Comparing a value from a table to a count value on a form (1 Viewer)

Local time
Today, 07:37
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!!
 

Isaac

Lifelong Learner
Local time
Today, 00:37
Joined
Mar 14, 2017
Messages
8,738
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?
 
Local time
Today, 07:37
Joined
Jun 26, 2020
Messages
4
Issac - thank you for reply - unforunately won't be able to post the database.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:37
Joined
Oct 29, 2018
Messages
21,358
Hi Steven. Welcome to AWF!

Check out the DCount() function.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 15:37
Joined
May 7, 2009
Messages
19,169
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

Top Bottom