Bookings Integrity.

[Guest]

Hi there.

In my database I have a booking system which books people into one of three
'beds'. Now the problem with this is that when the bookings are entered I
have to avoid double bookings. I have looked at allenbrownes query
http://www.allenbrowne.com/appevent.html
It is most useful except that I am not doing it in SQL, Or even if I was I
have to do it programmatically and then from those results decide whether the
person can be booked in and if so which bed they must be in, all in code.
Currently I am using lots of If...Then....Else statements to figure it out.

Thanks
Simeon

 

[Allen Browne]

When you are entering a new booking, you don't need to compare every booking
against every other one - just the proposed booking against the others.

You can do that with a DLookup() in the BeforeUpdate event of the form.
Something like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

If IsNull(Me.BookingStart) Or IsNull(Me.BookingEnd) Or IsNull(Me.RoomID)
Or _
((Me.BookingStart = Me.BookingStart.OldValue) And _
(Me.BookingEnd = Me.BookingEnd.OldValue) And _
(Me.RoomID = Me.RoomID.OldValue)) Then
'Ignore cases where there was no change, or values are missing.
Else
strWhere = "([BookingEnd] < " Format(Me.BookingStart,
strcJetDateTime) & _
") AND " & Format(Me.BookingEnd, strcJetDateTime) & _
" < [BookingEnd]) AND ([RoomID] = " & Me.RoomID & _
") AND (BookingID <> " & Me.BookingID & ")"
varResult = DLookup("BookingID", "BookingsTable", strWhere)
If Not IsNull(varResult) Then
If MsgBox("Clashes with booking number " & varResult & vbCrLf &
_
"Continue anyway?", vbYesNo + vbDefaultButton2) = vbNo Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...