Limit the number of times an entry in a Lookup Table can be select

[Guest]

I have created a sign-up table and corresponding form. The table consists of
Student ID (to eliminate duplicates), First Name, Last Name and a Lookup
Table of dates to choose from. Is there a way in the table or on the form to
limit the number of times a particular date can be chosen? For example, if
March 1st has been chosen 5 times can a message display that that date is
full and the student will have to choose another one- either a setting in the
table or on the form? Or should I design my table in a different way to
display available dates other than a Lookup Table?

Thanks in advance!
Jan

 

[Guest]

Jan:

You can do this in the BeforeUpdate event procedure of the form's control (a
combo box I'd imagine) bound to the foreign key column in the sign-up table
which references the table of dates. This event procedure has a Cancel
argument so you can set the return value of this to True if there are already
5 rows in the sign-up table with the date selected:

Dim criteria as String

strCriteria = "SignUpDate = #" & Format(Me.cboSignUpDate ,"mm/dd/yyyy") & "#"

If DCount("**, "tblSignUp",strcriteria) >= 5 Then
MsgBox strMessage, vbInformation, "Warning"
Cancel = True
End If

where tblSignUp is the table name, SignUpDate is the foreign key column in
tblSignUp of date/time data type to which the combo box is bound, and
cboSignUpDate is the name of the combo box.

This assumes each date can only be selected 5 times. If, however, you had a
situation where one date could be selected 5, another 4 and another 6 times
for instance, then you'd need another column, Quota say, in the table of
dates with a number in each row representing how many times that date could
be selected. The above code would then have to be amended slightly to use
this value rather than the constant 5, e.g.

Dim criteria as String
Dim intQuota As Integer

strCriteria = "SignUpDate = #" & Format(Me.cboSignUpDate ,"mm/dd/yyyy") & "#"
intQuota = DLookup("Quota", "tblDates", strCriteria)

If DCount("**, "tblSignUp",strcriteria) >= intQuota Then
MsgBox strMessage, vbInformation, "Warning"
Cancel = True
End If

where tblDates is the table of dates, and SignUpDate and Quota are the
columns in it, the former in this case being the primary key of the table
which is referenced by the foreign key column of the same name in tblSignUp.

You might have noticed that I've used >= rather than simply = when checking
to see if the maximum number has already been selected. This is just in case
you might ever want to override the quota and allow one or two more sign-ups
for a date than the quota, which would be done by an administrator outside of
this form. If you tested only for = rather than >=, then once an
administrator had gone over the quota students would be able to select that
data ad infinitum in the form.

Ken Sheridan
Stafford, England