Databases
StackOverflow2013
Postsdbo
POAccess Report won't allow me to refer to a field in VBA unless it's on the report in its own right
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAccess Report won't allow me to refer to a field in VBA unless it's on the report in its own right
    primarykey
    data
    text
    <p>So, in an Access Form or Report a good way to show something dynamic on the screen that is more complex than <code>=[UnitPrice]*[Quantity]</code> is to drop into VBA.</p> <p>e.g. in this simplified example, the underlying table for this Report has two fields <strong>ShowTax</strong> and <strong>TaxRate</strong>. By making a TextBox's control source <code>=GetTaxInfo</code> I get to introduce some complexity in VBA:</p> <pre><code>Public Function GetTaxInfo() As String Dim result As String If Me!ShowTax = 0 Then result = "Tax included @ " &amp; Me!TaxRate Else result = "" End If GetTaxInfo = result End Function </code></pre> <p>OK, this works ... so long as I have a field somewhere else that refers to TaxRate. Otherwise it just prints <code>#Error</code>. It's as if it needs to <em>preload</em> the field before it can be used in VBA code. It isn't the end of the world because I can have a bunch of fields in the report all set to not be visible, but it's untidy.</p> <p>So is it the case that you can't refer to a field <em>in VBA code backing a report</em> unless you have <em>already referred to the field in the conventional way as a field baked into the report?</em> </p> <p>I can't remember encountering this limitation before. Or is it that I have a corrupt report? (I have tried the usual compact/repair, export/reimport the Report etc)</p> <p><strong>Edit:</strong></p> <p>the weird thing is ... now it's working again. And - I'm pretty sure - there is no control in the report. which is why I was thinking it was a corruption in the report. </p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. UShawbsl
    1. UShawbsl
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAccess Report won't allow me to refer to a field in VBA unless it's on the report in its own right
      1. This table or related slice is empty.
      1. VTUpMod
    1. COYou're encountering what I consider a bug that was introduced into Access in the A2000 time frame. My surmise is that the introduction of support for ADO in Access forms/reports somehow decoupled the Access expression service from its connections to fields in the recordsources of forms/reports, with the results that you see, i.e., a field in the recordsource is not accessible in the form's/report's VBA code unless that field is the ControlSource of a control on the form/report. The problem was very narrow in A2000 but has since expanded. The only solution is a hidden control.
      singulars
      1. POAccess Report won't allow me to refer to a field in VBA unless it's on the report in its own right
      1. USDavid-W-Fenton
    2. COBy the way, I just checked and there's on really important difference between forms and reports: the default "property" of the form object is a union of the Controls and Fields collection (i.e., the controls on the form plust the fields in the recordsource). What I recently discovered was that there isn't really an independent Fields collection, but all the fields in the recordset do show up in the form's Controls collection. For reports, THIS IS NOT THE CASE. The only thing in the default collection of a report is the controls collection and that does not include fields from the recordsource.
      singulars
      1. POAccess Report won't allow me to refer to a field in VBA unless it's on the report in its own right
      1. USDavid-W-Fenton
    3. CODavid, your typo .... did you mean "there's no really important" or "there's a really important"?
      singulars
      1. POAccess Report won't allow me to refer to a field in VBA unless it's on the report in its own right
      1. UShawbsl
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload