Problem adding "<16" into DCOUNT function to reflect a cap of 16???

[andrew v via AccessMonster.com]

My function down below works great. However, i've tried different ways so
that the limit will only be less than 16 and it's not working. Does anyone
have any suggestions where i can put the

<=16

into the function so that when someone tries to add somebody after a cap of
16, they won't be able to do it.

=DCount("*","tblEvents","(EventDate=Date()) and (EventTime='AM')")

thanks...

 

[Dirk Goldgar]

My function down below works great. However, i've tried different
ways so that the limit will only be less than 16 and it's not
working. Does anyone have any suggestions where i can put the

<=16

into the function so that when someone tries to add somebody after a
cap of 16, they won't be able to do it.

=DCount("*","tblEvents","(EventDate=Date()) and (EventTime='AM')")

thanks...

What limit? Is "Limit" the name of a field in the table? If so, have
you tried

=DCount("*","tblEvents",
"(EventDate=Date()) and (EventTime='AM') and (Limit<=16)")

?

 

[John Vinson]

My function down below works great. However, i've tried different ways so
that the limit will only be less than 16 and it's not working. Does anyone
have any suggestions where i can put the

<=16

What's the context? The DCount() function will simply return a count; are
you calling it from an event on a Form, or what?

To limit the number of entries into a table, you can use the Form's
BeforeInsert event and set its Cancel argument to True if there are already
too many entries.

John W. Vinson/MVP

 

[andrew v via AccessMonster.com]

it's an unbound text box in a form that is counting information from a
table. it's an events registration form that will only allow 16
participants at a time. if this text box has 16, then no one can add
anymore participants.

the form with this information has a subform that shows all the different
events that the partcipant has attended once on the program. anyone can go
and sign the participant up for any future date, once saved and refreshed,
will be accounted for in the unbound text box field above...ie.
(1,2,3,...16)

hope this helps...

 

[Dirk Goldgar]

it's an unbound text box in a form that is counting information from a
table. it's an events registration form that will only allow 16
participants at a time. if this text box has 16, then no one can add
anymore participants.

the form with this information has a subform that shows all the
different events that the partcipant has attended once on the
program. anyone can go and sign the participant up for any future
date, once saved and refreshed, will be accounted for in the unbound
text box field above...ie. (1,2,3,...16)

hope this helps...

Are you saying you want to limit the number of records on the subform to
a maximum of 16? I don't have a clear picture of how you have this set
up. Is the subform set up with Link Master/Child fields to show only
records that meet those criteria you gave in your DCount expression, or
what?

 

[andrew v via AccessMonster.com]

there are two sessions per day, morning and afternoon Mon thru Fri. the
subform will only reflect information regarding a customer with the
following fields that the user can enter...

Event, EventDate(setup as current date), EventTime, ScheduledBy

ie. Testing, 3/28/05, AM, John Doe

the dcount function is taking this information and calculating how many
customers have been reserved for either the morning or afternoon session
from tblEvents. i have a total of 10 unbound text boxes to reflect the
five days and sessions. there are only 16 available seats per session.

right now it will go over the 16 limit. i need help on putting a max limit
of 16 with the dcount unbound text box so that when a user is trying to add
another customer to the session they will be unable to do so. sorry for
the confusion.

 

[andrew v via AccessMonster.com]

yes, calling it from a form. explanation above...

 

[Dirk Goldgar]

there are two sessions per day, morning and afternoon Mon thru Fri.
the subform will only reflect information regarding a customer with
the following fields that the user can enter...

Event, EventDate(setup as current date), EventTime, ScheduledBy

ie. Testing, 3/28/05, AM, John Doe

the dcount function is taking this information and calculating how
many customers have been reserved for either the morning or afternoon
session from tblEvents. i have a total of 10 unbound text boxes to
reflect the five days and sessions. there are only 16 available
seats per session.

right now it will go over the 16 limit. i need help on putting a max
limit of 16 with the dcount unbound text box so that when a user is
trying to add another customer to the session they will be unable to
do so. sorry for the confusion.

This is still a little unclear to me, so let me talk through my
understanding. The purpose of the form and subform is to book a
customer for various sessions, where a session is defined by the
combination of the fields Event, EventDate, and EventTime. The form is
customer-centric, so you are working with a particular customer,
choosing various sessions for that customer. The subform enters the
Customer, Event, EventDate, and EventTime into a table to do the
booking. You are trying to enforce a constraint that no session can
have more than 16 customers booked into it.

Your questions up to this point imply that tblEvents is the table that
is storing all the booking information. That doesn't actually sound
like a proper design to me, as I'd expect you to have at least these
tables:

Events (one record per event)

Sessions (one record per Event+EventDate+EventTime)

Customers (one record per customer)

SessionsCustomers (or "Bookings" -- one record per
Customer+Session )

I'm not going to pursue this further for the moment, but rather go on
with what appears to be your current table design.

The simplest way to enforce your constraint would be to put code into
the subform's BeforeUpdate event that checks for overbooking. For
example:

'----- start of example code -----
Private Sub Form_BeforeUpdate(Cancel As Integer)

If DCount("*", "tblEvents", _
"EventID=" & Me!EventID & " AND " _
"EventDate=#" & Format(Me!EventDate, "mm/dd/yyyy") & _
"# AND " & _
"EventTime='" & Me!EventTime & "'") _

= 16 _

Then
MsgBox "Sorry, that session is fully booked."
Cancel = True
End If

End Sub
'----- end of example code -----

A somewhat better way might be to either set up the form so that booked
sessions can't be chosen, or set it so that booked sessions are
automatically flagged as closed. However, advice along those lines
would require me to know more about your table and form design than I
do.

 

[andrew v via AccessMonster.com]

tables are: tblCUSTOMER INFO and tblEVENTS with a one to many relationship
by CustID field.

forms are: frmCUSTOMER INFO and frmEVENTS
in the frmEVENTS, a user uses a combo box with custID to pull up the CustID
and CustName. the subform reflects tblEVENTS in datasheet view where the
user can schedule the customer for a testing date. the user would go into
frmEVENTS, pull up the customer throught combo box and schedule them for a
test session in the subform.

tblEVENTS
Event(a drop down menu with 8 different tests that customer can take)
EventDate(current date default)
EventTime(am or pm)
ScheduledBy

at the top of all this shows:

Date() Date()+1...
am-session (dcount)
pm-session (dcount)
where the dcount function calculated the number of customers who has been
scheduled for a am/pm session on a certain date. this shows that this many
customers has been reserved for this session on this date. this number
needs to be less than or equal to 16.

your suggestion about not being able to choose or flag closed suggestions
is great. hope this helps...

 

[Dirk Goldgar]

tables are: tblCUSTOMER INFO and tblEVENTS with a one to many
relationship by CustID field.

forms are: frmCUSTOMER INFO and frmEVENTS
in the frmEVENTS, a user uses a combo box with custID to pull up the
CustID and CustName. the subform reflects tblEVENTS in datasheet
view where the user can schedule the customer for a testing date.
the user would go into frmEVENTS, pull up the customer throught combo
box and schedule them for a test session in the subform.

tblEVENTS
Event(a drop down menu with 8 different tests that customer can take)
EventDate(current date default)
EventTime(am or pm)
ScheduledBy

at the top of all this shows:

Date() Date()+1...
am-session (dcount)
pm-session (dcount)
where the dcount function calculated the number of customers who has
been scheduled for a am/pm session on a certain date. this shows
that this many customers has been reserved for this session on this
date. this number needs to be less than or equal to 16.

your suggestion about not being able to choose or flag closed
suggestions is great. hope this helps...

But there's something missing here. You show no CustID field in
tblEvents. If that's really the way the table is set up, there's no way
to record in tblEvents that a particular customer is booked for a given
event.

 

[andrew v via AccessMonster.com]

tblEvents
Autonumber (PK)
CustomerID (link to tblCustomerInformation)
Event (drop down list with 8 different tests)
EventDate (current date or later)
EventTime (am/pm)
ScheduledBy
Notes
Attend (yes/no)

ran into another problem today as well. do you have any thoughts of
changing the design to include more tables then???

 

[Dirk Goldgar]

tblEvents
Autonumber (PK)
CustomerID (link to tblCustomerInformation)
Event (drop down list with 8 different tests)
EventDate (current date or later)
EventTime (am/pm)
ScheduledBy
Notes
Attend (yes/no)

ran into another problem today as well. do you have any thoughts of
changing the design to include more tables then???

Well, as I said before, it does seem to me that your current two-table
structure is not really an accurate model of the entities that are
really involved. I would expect that you would have tables like these:

Events (one record per event)
As I understand it, there would be 8 records in this
table, corresponding to the "drop-down list with
8 different tests" that you refer to above. I don't
know what these tests are.

Sessions (one record per Event+EventDate+EventTime)
Each record in this table would represent a date and
time on which a particular event will occur. If these
events are tests, I guess they are the dates/times on
which those tests are given.

Customers (one record per customer)
You have this already, apparently.

SessionsCustomers (or "Bookings")
Each record here would be keyed by CustomerID
and SessionID (or EventID, SessionDate, SessionTime).
The presence of a record in this table would represent
the fact that a particular customer is signed up for a
particular session of a particular event.

Given these tables, the process for booking would involve choosing (or
entering) a customer, then choosing an event, then choosing from the
available sessions for that event. "Available sessions" would be those
that are not already filled.