Access - overlapping appointments

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

Guest

iIm not sure if this is possible, I want to know If there is a way for access
to check that if a new form is entered, that the Date and Time does not
overlap with a current record, that is scheduled.

I have the following fields
Date
Start Time
End Time
Scheduled - this being a Yes/No answer.

For example if I have a meeting
Date 15/1/2006 Start Time - 10:00 End Time - 11:00 Scheduled - Yes
and I try and enter another record for
Date 15/1/2006 Start Time - 10:30 End Time - 11:00 Scheduled - Yes
then an error appears to reschedule. If however the first meeting is not
scheduled then the second record will be ok.

I am only just starting out with access so if this is a very basic query my
appolgies, but I have searched, and not been able to find any solution.
 
Two events overlap if:
A starts before B ends, AND
B starts before A ends.

In the BeforeUpdate event procedure of the *form*, you can use that to
DLookup() any records in the table where this condition would be true. The
DLookup() will return Null if there are no matches.

The example below assumes your table is named MyTable, and the primary key
is a Number field named MyID. Adjust this to suit, but here's the idea:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
If IsNull(Me.[Date]) Or IsNull(Me.[StartTime]) Or _
IsNull(Me.[End Time]) Or (Me.[Date] = Me.[Date].OldValue) Or _
(Me.[Start Time] = Me.[Start Time].OldValue) Or _
(Me.[End Time] = Me.[End Time].OldValue) Then
'do nothing
Else
strWhere = "([Date] = " & _
Format(Me.[Date], "\#mm\/dd\/yyyy\#") & ") AND (" & _
Format(Me.[Start Time], "\#hh\:nn\:ss\#") & _
" < [End Time]) AND ([Start Time] < " & _
Format(Me.[End Time], "\#hh\:nn\:ss\#") & _
") AND ([MyID] <> " & Me.[MyID] & ")"
varResult = DLookup("MyID", "MyTable", strWhere)
If Not IsNull(varResult) Then
Cancel = True
MsgBox "Clashes with ID " & varResult
End If
End If
End Sub

Your examples indicate you live in a country with d/m/y dates. The
formatting is important so Access does not misunderstand your dates. More
info in:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html

BTW, Date is a particularly bad name for a field. It is a reserved word in
VBA, and so Access is likely to misunderstand what you intend. Consider
changing the name to EventDate or similar, in your table, and then in any
queries, forms, reports, macros, or code that refer to that field.
 
Back
Top