On Tue, 2 Mar 2010 19:28:01 -0800, Dennis
wrote:

You could use your new Count query before you save the data. Perhaps
in your Form_BeforeUpdate you could write:
if DLookup("CountOfCustAcctNo", "tblClass_Count", "ClassNo=" &
Me.ClassNo) MAX_VALUE then
Msgbox "Yo, no more!"
Cancel = True 'This stops the record from being saved
end if

Btw, from a naming convention standpoint it's a bad idea to name a
query "tbl...".

-Tom.
Microsoft Access MVP


All,

I think I figured it out. I created a query call tblClass_Count that gives
me the current number of students in each class.

SELECT qrytblClassD.ClassNo, qrytblClassD.ClassDesc,
Count(qrytblClassReg.CustAcctNo) AS CountOfCustAcctNo
FROM qrytblClassD LEFT JOIN qrytblClassReg ON qrytblClassD.ClassNo =
qrytblClassReg.ClassNo
GROUP BY qrytblClassD.ClassNo, qrytblClassD.ClassDesc
ORDER BY qrytblClassD.ClassNo, Count(qrytblClassReg.CustAcctNo);


On the continuous form sub-form, I have a combo box where I select the class
that the student wants to take. The current row source for it simply selects
the tblClass table. I will change that combo box row source to the above
query and use the ".Column()" value to retrieve the current student count.

I also realize that I will have to refresh this combo box after I add a
student.

At this time I only have one, maybe two people registering student and it
only happens a couple of times a day. So the timing issues of overbooking is
not an issue.

However, let's say it was an issues. Let's say that two different people
wanted to book someone different into the last seat in the class. Given the
above structure, how would you prevent that from happening?

Thanks,


Dennis