Seating availability depending on two fields....please assist...

[andrew v via AccessMonster.com]

two tables linked by CustomerID on a CustomerRegistration form as below...

tblCustomerInfo
*CustomerID

tblEvents
Event
EventDate
EventTime (am/pm session)

In an unbound text box on the CustomerRegistration form, I would like it to
show how many seats available for that session that day. There are two
sessions per day, an AM and PM session. There are only 16 seats available
per session per day. Anyone entering adding the customer to a particular
session will be able to find out if that session still has room or not.

If you need more info please ask?

thanks in advance...

 

[Allen Browne]

You can get the number of bookings for an event with this kind of thing:
DCount("*", "tblEvents", "(Event = 'someevent') AND (EventDate = #1/1/2005#)
AND (EventTime = 'PM')")

In practice, you will probably want to concatenate the 3 parts of the 3rd
argument into the string. If you need help, it is the same concept as
DLookup(), explained in this link:
http://allenbrowne.com/casu-07.html

 

[andrew v via AccessMonster.com]

The Event name wasn't important afterall, so I left it out of the formula.
The current formula shows:

DCount("*", "tblEvents", "(EventDate = #3/4/2005#)
AND (EventTime = 'AM')")

which works perfectly. However, I need the EventDate in the formula to
reflect the current date, for example today. As far as the EventTime
goes, I'll just have two text boxes that will reflect an AM and PM seating.

Please advise...thanks in advance...

 

[andrew v via AccessMonster.com]

one more thing, is there a way for me to lock the data entry form
automatically after a set time for the following day's scheduling?

for example, in the tblEvents of the CustomerRegistration form, a user
cannot add another customer to the following day's morning session after,
say 11 am.

And for the following day's afternoon session, say 4pm.

 

[Allen Browne]

This example uses Date() - meaning today's date - and the value in a text
box that is named ampm:
=DCount("*", "tblEvents", "(EventDate = Date())
AND (EventTime = '" & [ampm] & "')")

 

[Allen Browne]

Use the BeforeUpdate event procedure of the form to check that the date and
time in the entry are suitable.

 

[andrew v via AccessMonster.com]

Thanks for the DCount function. Works great. sorry, but you may need to
assist me in the BeforeUpdate situation. I just created an AM and PM
unbound text box with the same function but reflecting AM and PM, which
will work fine.

For example, a user cannot enter a client into the following day's AM
session after 11 am today and PM session after 4pm today. I'm thinking that
this may require some kind of IIF statement or something of the sort...

Thanks again...

 

[Allen Browne]

Andrew, we can't write your code for you--just point you to the event to use
and the idea to implement.