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

Question

So, in an Access Form or Report a good way to show something dynamic on the screen that is more complex than =[UnitPrice]*[Quantity] is to drop into VBA.

e.g. in this simplified example, the underlying table for this Report has two fields ShowTax and TaxRate. By making a TextBox's control source =GetTaxInfo I get to introduce some complexity in VBA:

Public Function GetTaxInfo() As String

    Dim result As String
    If Me!ShowTax = 0 Then
        result = "Tax included @ " & Me!TaxRate

    Else
        result = ""
    End If

    GetTaxInfo = result

End Function

OK, this works ... so long as I have a field somewhere else that refers to TaxRate. Otherwise it just prints #Error. It's as if it needs to preload 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.

So is it the case that you can't refer to a field in VBA code backing a report unless you have already referred to the field in the conventional way as a field baked into the report?

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)

Edit:

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.

Answer 1

You'll need a control on the form/report.

If this is too messy, you could put the function in a Module and use in the RecordSource (based on a query). No sense burying all this logic in a report when it could be used in other places as well.

Public Function GetTaxInfo(ShowTax as Boolean, TaxRate as Single) As String 

    Dim result As String 
    If ShowTax = 0 Then 
        result = "Tax included @ " & TaxRate 

    Else 
        result = "" 
    End If 

    GetTaxInfo = result 

End Function 

Then the control is set to this field in this report and others.