Comparing a value from a table to a count value on a form

[steven patrick arrow]

New to this so bear with me. I have a table of training courses available for people to book and a field on it is the max number of people that can attend an individual training course. I also have a form to record the names of people who want to attend each training course and as i enter their details i want the form to check that i do not exceed the max number of attendees allowed with some sort of message to appear on the screen. Looking for some help thanks!!

 

[Isaac]

You might be able to use the Dcount() function and write some code to check that in the form's BeforeUpdate event. Can you post your database?

 

[steven patrick arrow]

Issac - thank you for reply - unforunately won't be able to post the database.

 

[theDBguy]

Hi Steven. Welcome to AWF!

Check out the DCount() function.

 

[arnelgp]

you can use the Two Events of your Form (BeforeInsert and BeforeUpdate).

BeforeInsert will do.
this will validate before you can Add a record to a specific training:

Private Sub Form_BeforeInsert(Cancel As Integer)
Dim intAttendees As Integer
'count how many already registered to this training
intAttendees = DCount("1", "yourTrainingRegisterTable", "TrainingID = " & Me!TrainingID)
'compare this value against the max allowed attendees
If intAttendees >= DLookUp("maxAttendeeField", "yourTraningMaster", "TrainingID = " & Me!TrainingID)
    'already full
    'Cancel it
    Cancel = True
    Msgbox "Registry is full"
End If
End Sub

 

[steven patrick arrow]

Wow - Thanks a lot - that did it!!!