Only Allow 1 Enrollment

[padlocked17]

How would I go about checking on a continuous form when a new member is being added to a table if they already exist in that table with the criteria that they are not graduated (yes/no) column?

Also, in addition to the above, how would I limit the list of members from the drop-down to simply not include individuals who are already enrolled in that current class or any class that doesn't have them marked as "Yes" graduated?

Screen grabs of the queries are attached.

I essentially have a frmEnrollment which is the main form with the subfrmEnrollment which is a continuous form that allows members to be added to a class.

In addition, how could I get the Course type to actually be prefilled based on the member's rank. As you can see in the screenshot, there is a yes/no column for each rank associated with each person. Based off of this rank, and the yes or no associated with the rank, they are in one of the two categories for that drop-down in course.

The Form:

frmEnrollment Record Source

subfrmEnrollment Record Source

subfrmEnrollment Select Member Combo Box Record Source

Thanks!

 

[neileg]

Not at all easy to do in a continuous form. You could use a multifield index set to no duplicates but the 'not graduated' requirment makes that a nonstarter. Where you want to validate on multiple fields, it is easier to capture the data first , validate it and then add it to the table. You can capture the data in unbound controls, or bind the controls to a temporary table and append the data to your main table when validated.

 

[padlocked17]

Neil -

I don't have any idea how to set this up. Could you provide some pointers on how to go about this or an alternative solution that I might be able to use?

I'm open to just about anything.

Thanks.