Dlookup and coding

[Guest]

Hi

I posted this question a while ago and got some answers, but it didn't quiet
work. So, here is what I have now.

I have Schedule Table which shows all the classes offered in the clinic.
There is also a Rehab Schedule Table, which has the class name, start date
and maximum number of participants (MaxClassSize). My schedule table pulls
this information into it through combo boxes.

I need to set up a way that when in the Schedule form, when the user enters
the RehabID number (I have been trying this based on the date) that is
<=MaxClass Size, then the person is "Registered". If it is over the
MaxClassSize then should say "Class Full".

So.. I created a crosstab query where ClassName is set to Groupby and
StartDate is set to Groupby and RehabID is set to Count. The results are
coming up correctly showing how many instances RehabID has been selected.
Back in the Schedule form, I have tried doing a dlookup to pull the Total of
RehabID field into the form. I have tried this 2 ways and neither of them are
working
Dlookup("[TotalofRehabID]","[qryCountRehabID]","[StartDate]=#"&[StartDate]&"#")

The other way I have tried is:
=DLookUp("TotalofRehabID","qryCountRehabID","[RehabID]=" & [RehabID])

Can anyone see where I am going wrong with this?/
Thanks

 

[Duane Hookom]

Since your "schedule table pulls this information into it through combo
boxes" I would base the combo boxes on your queries that contain the columns
TotalOfRehabID (isn't this a count) and MaxClassSize. You can either filter
out classes that are already full or compare the two columns in the after
update event of the combo box and display a message to the user.