Search code examples
ms-accesseventsreport

MS Access VBA: When opening a report, what event is triggered once the query that the report depends on has been completed?

I have a report that is dependent on a query. The report displays correctly but I'm trying to have a label's visibility dependent on a yes/no field in the query.

The following produces error 2424 that the field isn't found:

Private Sub Report_Open(Cancel As Integer)
lblUppersIncluded.Visible = ysnUppersIncluded
End Sub

Alternatively: 

Private Sub Report_Activate()
lblUppersIncluded.Visible = ysnUppersIncluded
End Sub

Gives error 2427 expression has no value. Similarly for Report_Load.

However, a command box created on the report that executes the same line of code executes correctly.

I suspect that all of the Report events are triggered before the query has been run so those fields have not been populated. Is there an event that I can use after the query has been completed?

Solution

  • Sometimes the best way around problems like this is to convert your label to a text box and use conditional formatting. If your formatting needs are not met by what can be accomplished by conditional formatting then you can look into code.

    Specifically for a label to be visible or not you don't even need conditional formatting. Convert to text box and use:

    =IIF([My condition for visible label],"My Label text:","")

    as its control source.