Limit Entrys per day

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
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
 
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.
 
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...
 
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.
 
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
 
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?
 
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?
 
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
 
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
 
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...
 
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


"(e-mail address removed)" <[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...
 
Back
Top