Count with VBA 2

[Rene1024]

Hi,

I'm developing a dinner registration form. One of the fields on the form is used for "Table number". I would like to write something that checks the number of guests seating at that table and if the number of guests has reached 10 return a message that says "This Table is Full". I'm also not sure which property of the field I should associate the event with, I was thinking of using "After Update".

Any help with this will be greatly appreciated.

Best regards,

Rene.

 

[randy700]

Perhaps dlookup?

If Dlookup("NbrGuests", "TableName", "TableNbr = '" & Me.TableNbr & "'") = 10 Then
    Msgbox...
End If

Lose the single quotes if the TableNbr field is numeric.
I'd think BeforeUpdate would be a better choice.



Randy

 

[Rene1024]

Thanks Randy,

I don't know anything about programming, this is what my code looks like:
Dim strMsg
strMsg = "This Table is full"

If DLookup("NbrGuests", "Participants", "TableNo = '" & Me.TableNo & "'") = 10 Then
MsgBox srrMsg, vbOKCancel

I get Run time error 2751 "The expression you entered as a query parameter produced this error: 'NbrGuests'

Best,

Rene

 

[PHV]

Do you really have a field named NbrGuests in the Participants table ?
If not, you may consider the DCount function instead.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rene1024]

Still not working, I changed my code to:
Dim strMsg
strMsg = "This Table is full"

If DCount("TableNo", "tblParticipants", "TableNo = '" & Me.TableNo & "'") = 10 Then
MsgBox srrMsg, vbOKCancel

End If

Now I get an error that says "Run-time error '3464' Data Type mismatch in criteria expression"

Thanks for any help.

 

[dhookom]

I would make the table number control into a combo box that would not display any tables that didn't have room. If you want further assistance, you should be providing the significant table and field names as well as the data types of the fields.

Your attempted code suggests TableNo is text.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

What about this ?
If DCount("*", "tblParticipants", "TableNo=" & Me!TableNo) >= 10 Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Rene1024]

Thanks to everyone for all the help.

PHV's suggestion worked great.

Best Regards,

Rene