MS Access Forum / Forms / March 2005
Problem adding "<16" into DCOUNT function to reflect a cap of 16???
|
|
Thread rating:  |
andrew v - 28 Mar 2005 17:30 GMT 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 - 28 Mar 2005 17:33 GMT > 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 [quoted text clipped - 8 lines] > > 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)")
?
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
andrew v - 28 Mar 2005 18:37 GMT 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 - 28 Mar 2005 18:52 GMT > 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 [quoted text clipped - 8 lines] > > 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?
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
andrew v - 28 Mar 2005 19:16 GMT 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.
Dirk Goldgar - 29 Mar 2005 20:27 GMT > there are two sessions per day, morning and afternoon Mon thru Fri. > the subform will only reflect information regarding a customer with [quoted text clipped - 14 lines] > 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.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
andrew v - 30 Mar 2005 23:40 GMT 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 - 31 Mar 2005 22:53 GMT > tables are: tblCUSTOMER INFO and tblEVENTS with a one to many > relationship by CustID field. [quoted text clipped - 24 lines] > 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.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
andrew v - 31 Mar 2005 23:14 GMT 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 - 01 Apr 2005 00:05 GMT > tblEvents > Autonumber (PK) [quoted text clipped - 8 lines] > 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.
 Signature Dirk Goldgar, MS Access MVP www.datagnostics.com
(please reply to the newsgroup)
John Vinson - 28 Mar 2005 17:37 GMT > 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 - 28 Mar 2005 23:30 GMT yes, calling it from a form. explanation above...
|
|
|