Validation code rule

[DCW]

I am using access 2000 format and want to validate that the time and date
fields are compared to not allow input of duplicate dates or times. The form
is an event calendar form using a continuous data entry subform. Since the
data can be captured for the same date many times at different times ie
05-08-09 at 08:00am,
05-08-09 at 09:00am etc and will be possible for the subform to scroll out
of view a validation will be necessary.

My table has these fields:
CalendarID -- Auto Number
Date -- Date/Time
Time -- Date/Time
Contact -- Text
Event Type -- Text
Place -- Text
Date Alert -- Date/Time
Event Due Past Due -- Yes/No

There is no primary key since the table and forms stand alone.
I want to set a before update vb code.

 

[Arvin Meyer MVP]

The easiest way to avoid duplication is to use a single field for both Date
and Time and set a unique index upon it.

 

[Allen Browne]

So you want to block the possibility of 2 events at exactly the same date
and time.

There's a few things to consider here:
a) Date and Time are reserved words, and they will cause you grief as field
names. Consider using other names, e.g. EventDate and EventTime.

b) It would be easier of the date and time were in the one field, say
EventDateTime. You can then prevent duplicates just by setting this field's
Indexed property (in table design) to:
Yes (No Duplicates.)

c) You don't have a field indicating how long an event lasts. If you only
prevent exact duplicates, you could end up with a 30 minute event that
starts and 8am, and another event starting at 8:10 which actually clashes.
Is this something you need to prevent?

d) I didn't understand why the table cannot have a primary key, such as an
AutoNumber. The form doesn't have to show it, but the table *really* needs
it. Even if your table is not bound to the table, every table needs a p.k.

It is possible to use Form_BeforeUpdate to check for a duplicate. Something
like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg as String
If (Me.EventDate = Me.EventDate.OldValue) And _
(Me.EventTime = Me.EventTime.OldValue) Then
'don't bother checking.
Else
strWhere = "(EventDate = " & Format(Me.EventDate,
"\#mm\/dd\/yyyy\#") & _
") AND (EventTime = " & Format(Me.EventDate, "\#hh\:nn\:ss\#") &
")"
varResult = DLookup("EventID", "tblEvent", strWhere)
If Not IsNull(varResult) Then
strMsg = "Clashes with event " & varResult & "." & vbCrLf &
"CONTINUE ANYWAY?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Duplicate time") <>
vbYes Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub