Allowing a maximum number of records in a subform

[Guest]

With Access you never stop learning and have been using it for about 6 years
and never really had any problems that I can't solve on my own, until now.
This is something I have been stuck on for a few day and just can't my head
around it no matter how hard I try, really hope someone out there can help me.

I have a Database that enrols students on short courses, I have a form that
the user can use to create a new scheduled course (select a short course and
enter when and where it will take place along with the maximum number of
students the course can hold). I also have another form with two sub-forms,
first the user selects they type of short course (for example "Immediate Life
Support") from a combo box, then in the first sub-form shows which dates this
course is running, on selecting the date, the second sub-form shows which
students are on this course for that date, and its in this second sub-form
the user can add new students onto this course for the specific date in the
first sub-form.

But the problem I am having is, say for instance the maximum number of
students for Immediate Life Support is 20, I don't know how to prevent the
user adding another student to the course for that date once the maximum
number of students allowed for the course has been reached.

Would really appreciate it if someone can help.

Kind Regards,

Matty

 

[Allen Browne]

Cancel the BeforeInsert event of the form if the maximum number has already
been reached.

I suggest that you use DCount() on the subform's table to get the matching
number of records, rather than use [Sub1].Form.RecordsetClone.Count, in case
the subform is filtered.