Double booking Validation

[Ben Allen]

Hi,
My database allows the user to book a lesson and assign a passenger and
instructor to that lesson. The bookings table has the following structure

Booking (booking id, activity_id, time, length, instructor id, passenger id)

On my form, I would like to do some validation when entering a
time/length/changing instuctor or passenger to check that that
passenger/instructor is not already booked. Can anyone suggest a way of
doing this? I thought of using a query with a calculation such as

overlap:
IIf((tbl_booking!time)<(Forms!frm_passenger!subfrm_booking.Form!txt_booking_time)<(tbl_booking!time+tbl_booking!length),1,0)

And checking to see if any '1's are displayed, not sure if that would really
work though, thanks.

Ben

 

[Allen Browne]

You have a clash if:
- another booking begins before this one ends, AND
- this one begins before that one ends, AND
- it is the same instructor or passenger

The untested example below shows how to use the BeforeUpdate event of the
*form* where entries are made to test for a clash. It is untested. You may
need to adjust the where clause, and set up the brackets correctly. You will
have to fix up the line breaks. I have assumed:
- the field named Time is a Date/Time field, and contains both the date and
time;
- the other fields are all of type Number;
- the Length is in minutes;
- all fields have a value (none are null.)
Additionally, Time is a reserved word in VBA, so Access may understand it to
be the system time and the whole thing may not work.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant

strWhere = "([Booking ID] <> " & Nz([Booking ID],0) & ") AND ([Time] < "
& Format(DateAdd("n", [Length], [Time]), "\#mm\/dd\/yyyy hh\:nn\:ss\#") & ")
AND (" & Format([Time], strcJetDateTime) & " < DateAdd(""n"", [Length],
[Time], ""\#mm\/dd\/yyyy hh\:nn\:ss\#"")) AND (([instructor id] = " &
Nz([instructor id],0) & ") OR ([passenger id] = " & Nz([passenger id],0) &
"))"

varResult = DLookup("[Booking ID]", "Booking", strWhere)
If Not IsNull(varResult) Then
MsgBox "Clash with booking " & varResult
Cancel = True
End If
End Sub

If you were wanting to compare every record against every other record to
find any clashes, see:
Clashing Events/Appointments
at:
http://allenbrowne.com/appevent.html