Limit Entrys per day

L

lmossolle

Is there a way to limit the entrys to 40 per calander day? We can only
schedule 40 students per date and would like a message pop up that says you
reached the maximum of 40 entries for a certain date.
 
W

Wayne Morgan

Either in the BeforeUpdate of the date field or the BeforeUpdate of the
form, do a DCount() of the entries for that day. If the result is >= 40, pop
up the error message and disallow the entry.

The date field would probably be the best place for this. This would allow
the user to select another date and continue or, if the student couldn't
accept another date, the user could cancel without taking the time to make
the entries for the rest of the record. The form's BeforeUpdate event
wouldn't check for the problem until the user tried to save the record.

Example:
If DCount("*", "TableName", "DateField = #" & Me.txtDateField & "#") >= 40
Then
Msgbox "Class is full. Please pick another date or cancel the entry."
Cancel = True
End If
 
L

lmossolle

Now what kind of fields would be a good Idea to tie to this? I am still in
the developing phase on this. On the table these are the ones I was
considering;

tblappt
Date - Todays date
AppDate - Appointment date " this will be using the formula you mentioned.
Last Name -
First Name -
Social Security Number

Should I tie a number after the Appdate? So the database will see the number?

Thanks,
Lee
 
L

lmossolle

I used the same table name you specified and the same column, i am getting a
compile error, here is the formula used in the before update. I put it in the
datefield on the form instead of the form. Please assist....


Private Sub AppDate_BeforeUpdate(Cancel As Integer)

If DCount("*", "TableName", "DateField = #" & Me.txtDateField & "#") >= 40
Then
MsgBox "Class is full. Please pick another date or cancel the entry."
Cancel = True
End If

End Sub
 
W

Wayne Morgan

You don't need to tie it to a field, the * will be sufficient for this. As
far as the table name and the two names for the date field and date control,
you'll need to adjust them to match the names in your database.
 
L

lmossolle

Wayne,

I inserted the commands you mentioned set the control source at the
corrcct table and I still get the same error. Can you shed some light on this?

Thanks...
 
W

Wayne Morgan

I noticed that the newsreader wrapped the "Then" to a second line. It should
be on the same line as the "If". This is a limitation of the newsreader and
using the newsgroups.
 
L

lmossolle

I got rid of the compile errors, now when i get to 41 records it still allows
me to keep adding on a specific date. I s there a way to get this formula to
work? Table Name "Front Desk" Column Name "AppointmentDate" Please help!!!!!!

Thanks
 
W

Wayne Morgan

Is the AppointmentDate field in the table defined as a Date/Time data type?
Will you post the code as you currently have it? Does it allow you to make
the entries for the 41st record, but not save them or does it save them?
 
L

lmossolle

The AppointmentDate field is a date/time. Post as you sent information. It
allows to add record and it will also save. Is there something i am missing?
 
L

lmossolle

Here is the formula used.

Private Sub AppointmentDate_BeforeUpdate(Cancel As Integer)
If DCount("*", "Front Desk", "AppointmentDate = #" & Me.txtAppointmentDate &
"#") >= 40 Then
MsgBox "Class is full. Please pick another date or cancel the entry."
Cancel = True
End If

End Sub
 
W

Wayne Morgan

It appears that the table name "Front Desk" has a space in it. Access
requires that names with spaces be enclosed in brackets.

If DCount("*", "[Front Desk]", "AppointmentDate = #" & Me.txtAppointmentDate
& "#") >= 40 Then
 
L

lmossolle

I am getting a compile error at the Me.txtAppointment area do you have any
suggestions?

Sorry for being a pain!!!

Wayne Morgan said:
It appears that the table name "Front Desk" has a space in it. Access
requires that names with spaces be enclosed in brackets.

If DCount("*", "[Front Desk]", "AppointmentDate = #" & Me.txtAppointmentDate
& "#") >= 40 Then

--
Wayne Morgan
MS Access MVP


lmossolle said:
Here is the formula used.

Private Sub AppointmentDate_BeforeUpdate(Cancel As Integer)
If DCount("*", "Front Desk", "AppointmentDate = #" & Me.txtAppointmentDate
&
"#") >= 40 Then
MsgBox "Class is full. Please pick another date or cancel the entry."
Cancel = True
End If

End Sub
Click to expand...
Click to expand...
 
W

Wayne Morgan

Make sure that the name of the textbox txtAppointmentDate is changed to the
name of YOUR textbox. This was just an example. The same will go for any
field or table names.

--
Wayne Morgan
MS Access MVP


lmossolle said:
I am getting a compile error at the Me.txtAppointment area do you have any
suggestions?

Sorry for being a pain!!!

Wayne Morgan said:
It appears that the table name "Front Desk" has a space in it. Access
requires that names with spaces be enclosed in brackets.

If DCount("*", "[Front Desk]", "AppointmentDate = #" &
Me.txtAppointmentDate
& "#") >= 40 Then

--
Wayne Morgan
MS Access MVP


lmossolle said:
Here is the formula used.

Private Sub AppointmentDate_BeforeUpdate(Cancel As Integer)
If DCount("*", "Front Desk", "AppointmentDate = #" &
Me.txtAppointmentDate
&
"#") >= 40 Then
MsgBox "Class is full. Please pick another date or cancel the entry."
Cancel = True
End If

End Sub

:

Is the AppointmentDate field in the table defined as a Date/Time data
type?
Will you post the code as you currently have it? Does it allow you to
make
the entries for the 41st record, but not save them or does it save
them?

--
Wayne Morgan
MS Access MVP


"[email protected]" <[email protected]>
wrote
in
message I got rid of the compile errors, now when i get to 41 records it
still
allows
me to keep adding on a specific date. I s there a way to get this
formula
to
work? Table Name "Front Desk" Column Name "AppointmentDate" Please
help!!!!!!
Click to expand...
Click to expand...
Click to expand...
 
Top