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

Local time
Today, 18:33
Joined
Jun 26, 2020
Messages
4
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!!
 
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?
 
Issac - thank you for reply - unforunately won't be able to post the database.
 
Hi Steven. Welcome to AWF!

Check out the DCount() function.
 
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:
Code:
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
 

Users who are viewing this thread

Back
Top Bottom