Limited number of records

[John Watson]

This is for a conference registration. There are 5
courses on offer, and each course is restricted to a
different number of participants.

I have a table for participant registration information,
and one set up with course information. A relationship is
set up between the two (the specific course selections).

I want to limit the number of records that can be entered
into a table that are referencing a specific course (for
example, maximum 18 records that specify Course 1, maximum
16 records that specify Course 2).

 

[John Vinson]

I want to limit the number of records that can be entered
into a table that are referencing a specific course (for
example, maximum 18 records that specify Course 1, maximum
16 records that specify Course 2).

The safest way to handle this is to force all input to be done through
a Form - table datasheets don't have any useful events.

Typically one would use a Form based on the course table, with a
Subform based on the enrollment table. In the BeforeInsert event of
the subform you can use DCount() to count how many have already
enrolled, and pop up a message and cancel the insertion if the class
is full:

Private Sub Form_BeforeInsert(Cancel as Integer)
If DCount("*", "[Enrollment]", "[ClassNo] = " & [ClassNo]) _

Parent!txtMaxEnrollment Then

MsgBox "Sorry, this class is at capacity", vbOKOnly
Cancel = True
End If
End Sub