Microsoft Access attempting to find table in macro as field-CodePudding

I am creating a database for my school which contains data for an event happening later on in the year. They need a query that displays the events and how many people are going. At the same time they want me to display an error if the venue is overbooked.

I attempted to do this via a macro. The macro would open the attendence_rpt report, then run the macro to see whether the venue is overbooked, like so:

'------------------------------------------------------------
' Macro1
'
'------------------------------------------------------------
Function Macro1()
On Error GoTo Macro1_Err

    DoCmd.OpenReport "attendence_rpt", acViewReport, "", "", acNormal
    If (Reports!attendence_rpt!CountOfAttendeeID > Reports!attendence_rpt!venue_tbl!Capacity) Then
        Beep
        MsgBox "test", vbOKOnly, "test"
    End If


Macro1_Exit:
    Exit Function

Macro1_Err:
    MsgBox Error$
    Resume Macro1_Exit

End Function

When I run the macro however, it comes up with this error:

Microsoft Access can't find the field 'venue_tbl' referred to in your expression

This means that the macro is trying to find the linked table 'venue_tbl' as a field.

Is there any way to fix this? Or is there an easier method to achieve the same goal?

Below are some photos of the database in Access:

CodePudding user response：

Add a new field to the report that contains venue_tbl!Capacity and make it not visible (unless you want to show the new field to the user). Then change the line in your macro:

If (Reports!attendence_rpt!CountOfAttendeeID > Reports!attendence_rpt!venue_tbl!Capacity) Then

to:

If Reports!attendence_rpt!CountOfAttendeeID > Reports!attendence_rpt!NameOfNewField Then

where NameOfNewField is the name of the field you added.

CodePudding user response：

You must not reference the table in the report.

So this is WRONG:

Reports!attendence_rpt!venue_tbl!Capacity

And this is CORRECT:

Reports!attendence_rpt!Capacity