Creating a quantity limit

T

TimE

I would like to prevent a field from containing more than 40 same dates.
For example, Schedule Date could only contain 40 1/1/2006. If someone
tried to enter another record with this same date, then it a message
popup would alert them. Is this possible?

Thanks in advance,

TimE
 
J

John Vinson

I would like to prevent a field from containing more than 40 same dates.
For example, Schedule Date could only contain 40 1/1/2006. If someone
tried to enter another record with this same date, then it a message
popup would alert them. Is this possible?

Thanks in advance,

TimE
Click to expand...

Only if you're entering data using a Form. There, you could use the
Form's BeforeUpdate event, with code like this: I'm assuming that you
have a textbox on the Form named txtScheduleDate bound to this field.

Private Sub Form_BeforeUpdate(Cancel as Integer)
If IsDate(Me!txtScheduleDate) Then ' Valid data?
If DCount("*", "[YourTableName]", "[ScheduleDate] = #" _
& CDate(Me!txtScheduleDate) & "#") >= 40 Then
MsgBox "Too many entries for this date", vbOKOnly
Cancel = True
End If
Else ' invalid or no date
MsgBox "Please enter a date"
Cancel = True
Me!txtScheduleDate.SetFocus
End If
End Sub

John W. Vinson[MVP]
 
Top