Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms / March 2005

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
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...
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.