Double Booking problem in room booking system

[xtremeboat]

I've put this in this forum because it doesn't strictly fit into any of the others. I' want to attatch a copy of my coursework with only the relevant tables and forms, no idea how though. Basically its a room booking system for a hotel but I can't figure out how to stop double bookings from taking place. A friend suggested to make some code so that each individual day would be booked rather than a specified time period making validation more straightforward. If you could help me to stop double bookings from taking place please help. Bear in mind my VB skills aren't great. Thanks for your help.

 

[WayneRyan]

xtreme,

I'm assuming that you have a form with a RoomNumber and
TheDate on it. Use the AfterUpdate event of the TheDate
field:

Dim Result As Long

If IsNull(Me.RoomNumber) Then
   MsgBox("Must enter RoomNumber.")
   Exit Sub
End If

If IsNull(Me.TheDate) Then
   MsgBox("Must enter Date booked.")
   Exit Sub
End If

Result = Nz(DLookup("[RoomNumber]", "YourTable", _
         "[RoomNumber] = " & Me.RoomNumber & " And " & _
         "[TheDate] = #" & Me.TheDate & "#"), 0)

If Result > 0 Then
   MsgBox("That room is booked for that day.")
End If

Wayne

 

[xtremeboat]

Nah won't work but thanks.

Basically there are 4 tables: tblGuest, tblBookings, tblRoom and tblRoomRate.

tblGuest - Guest Details
tblBookings - Booking Details
tblRoom - contains each room number and its room type (double or single)
tblRoomRate - contains the NightyRate for single and double rooms.

The whole thing works but the booking does not reserve the room for every day during the guest's stay. It just contains the ArrivalDate as data and NumberOfNights as an integer. LeavingDate is worked out on the form (btw its a subform on the Guest details form - I'll attatch a couple of screens) or using a query in the case of reports. The thing is I have no idea how to actually stop anyone else booking the room during the duration of another guest's stay because it doesn't save the actual dates that the room is booked for. Help would be appreciated, thanks.

On the screenshot you can see how the room is effectively double booked.

 

[WayneRyan]

xtreme,

You can use the DateAdd function to calculate
the ending date. Can you compact, ZIP and
post your database?

Wayne

 

[xtremeboat]

Here it is. Thanks for your help!

 

[WayneRyan]

xtreme,

I can't run your database, it keeps crashing my machine.

You have a lot of code in there, so this shouldn't scare you.

This should get you started ...

Dim Result As Long

If IsNull(Me.RoomNumber) Then
   MsgBox("Must enter RoomNumber.")
   Exit Sub
End If

If IsNull(Me.TheDate) Then
   MsgBox("Must enter Date booked.")
   Exit Sub
End If

Result = Nz(DLookup("[RoomNumber]", "tblBooking", _
         "[RoomNumber] = " & Me.RoomNumber & " And " & _
         "([ArrivalDate] >= #" & Me.ArrivalDate & "# And " & _
           [ArrivalDate] <= #" & DateAdd("d", Me.NumberOfNights, Me.ArrivalDate) & "#) Or " & _
         "(DateAdd("d", [NumberOfNights], [ArrivalDate]) >= #" & Me.ArrivalDate & "# And " & _
           DateAdd("d", [NumberOfNights], [ArrivalDate]) <= #" & DateAdd("d", Me.NumberOfNights, Me.ArrivalDate) & "#")), 0)

If Result > 0 Then
   MsgBox("That room is booked for that day.")
End If

Wayne

 

[xtremeboat]

Thanks for your help but Access highlights this code red:

Result = Nz(DLookup("[RoomNumber]", "tblBooking", _
"[RoomNumber] = " & Me.RoomNumber & " And " & _
"([ArrivalDate] >= #" & Me.ArrivalDate & "# And " & _
[ArrivalDate] <= #" & DateAdd("d", Me.NumberOfNights, Me.ArrivalDate) & "#) Or " & _
"(DateAdd("d", [NumberOfNights], [ArrivalDate]) >= #" & Me.ArrivalDate & "# And " & _
DateAdd("d", [NumberOfNights], [ArrivalDate]) <= #" & DateAdd("d", Me.NumberOfNights, Me.ArrivalDate) & "#")), 0)

and points to the # on the 4th line for some reason. What options should I have set in References (under Tools when in Visual Basic mode)? Thanks.

 

[The_Doc_Man]

I won't do all of this for you but I can at least point a bit for you. The problem with overlapping is because you have to do two tests. The BETWEEN ... AND ... operator works in SQL, so you can write a query. The way this would have to work is as follows...


For every accepted booking, include a flag for "locked in" or something like that. In other words, you have screened it and accepted it and it is OK. Every accepted booking must have a starting time/date and and ending time/date.

For every potential booking (not yet accepted), you ALSO have a starting time/date and ending time/date. A conflict occurs in any of the following cases:

1. [RoomBooking].[Starting] BETWEEN [Potential].[Starting] AND [Potential].[Ending] or

2. [RoomBooking].[Ending] BETWEEN [Potential].[Starting] AND [Potential].[Ending] or

3. [Potential].[Starting] BETWEEN [RoomBooking].[Starting] AND [RoomBooking].[Ending] or

4. [Potential].[Ending] BETWEEN [RoomBooking].[Starting] AND [RoomBooking].[Ending]

You need all four cases because of pathological overlaps in which one of the bookings is completely contained within the other booking. I.e. a booking is blocked if (1 or 2 or 3 or 4) is true.

Application of De Morgan's Theorem can simplify and collapse the above cases, though.

The only time it is REALLY safe to make the booking is if:

1. [RoomBooking].[Starting] < [RoomBooking].[Ending] and

2. [Potential].[Starting] < [Potential].[Ending] and (open parentheses for the next two ...)

3. [Potential].[Ending] < [RoomBooking].[Starting] or

4. [RoomBooking].[Ending] < [Potential].[Starting] (close the parentheses).

Now, a booking is safe if 1 and 2 and ( 3 or 4 ) is true.

The way I would approach it is to have a form with a potential booking having start and stop times. You don't have to test 1 and 2 every time if you just assure that 2 is always true at the time that you start your test and when you change a potential into an actual booking. So if you do this pre-screening, you don't have to repeat those tests in the query.

Next, I would build a table that lists all rooms and has a flag that says "available" as a yes/no. Build an action query that assumes that all rooms are available in this table. I.e. reset all room flags to True. Build a query that tests for 3 and 4 as two criteria, and make a yes/no result as the logical OR of the two cases. Update the "available" flag based on this test AND'd with the available flag. At the end of the query, a room is available if and only if NONE of the listed bookings has reset the flag to false.

The room booking table therefore would have to include the room number, starting time, ending time, and any other information you needed therein for other reporting.

The room table would have to include the room description stuff.

This TEMP BOOKING table would perhaps only include room number and this one flag. Then you could JOIN the temp table to the other tables for display purposes.

To make it easy, you might only need a single-entry potential entry table with a starting and ending time.

Remember, the idea is that if either end of a booking overlaps the opposite end of a potential booking, you cannot assign that room.

Of course, if all of this is driven from a form, you only need an action button to run a query to update the potential booking as an append to the RoomBooking table.

Now, bells and whistles...

You could write action queries to update the temp flags to FALSE based on room occupancy smaller than potential occupancy. Also if the potential occupant wants non-smoking and the room is not designated smoke-free. Also if the potential occupant needs a handicap-friendly room and the room isn't so friendly.

The idea is that when all filtration is done, the TEMP BOOKING list has TRUE flags for all rooms that could be used to accomodate the booking. And if no flags are left TRUE, the booking cannot be accomodated.

 

[WayneRyan]

extreme & doc,

I'm going to Vegas.

I'll be back.

hth
Wayne

 

[xtremeboat]

Thanks for your help but I have no idea how to implement any of that. Could you dumb it down a bit please.