Preventing Double Bookings - CODE NEEDED!

[RandomGuy123]

Hello everyone,

I'm having some significant difficulties with a simplistic database I have designed for a hotel scenario. I want my database to automatically prevent double bookings from occurring (and also inform the user that the double booking has been prevented) and due to my lack of expertise/knowledge of VBA and database, I'm struggling - this is for a client of mine.

The Database consists of four tables:

CUSTOMERS (Customer ID, Title, Forename, Surname, Date of Birth, Gender, Address Line 1, Address Line 2, Town, County, Postcode, Telephone Number, E-Mail Address)

EMPLOYEES (Employee ID, Title, Forename, Surname, Date of Birth, Gender, Address Line 1, Address Line 2, Town, County, Postcode, Telephone Number, E-Mail Address, Job Title)

ROOMS (Room ID, Room Type, Room Rate)

BOOKINGS (Booking ID, Customer ID, Room ID, Employee ID, Booking Start Date, Booking End Date, Wake-Up Call, Airport Transportation, Car Rental, Laundry & Dry Cleaning, Newspapers & Magazines, Flowers)

All four of these tables also have forms.

The following fields in the Bookings table are check boxes:

Wake-Up Call, Airport Transportation, Car Rental, Laundry & Dry Cleaning, Newspapers & Magazines, Flowers

The following is what I want to happen with the Bookings form:

I want the employee to select the Customer ID for the customer who is making the booking, the Room ID (the room which the customer will stay in - the hotel has 24 rooms and therefore in the Rooms table there are 24 Records/Primary Keys), the Employee ID (the employee who is making the booking) and the Booking Start Date and Booking End Date.

When I click the 'Save Booking Details' button which is on the Bookings form, I want the system to check whether another booking for the same room lies within the Booking Start Date and the Booking End Date.

The following is an example:

I have made a booking which starts on the 25/12/2014 and ends on 31/12/2014 for room number 6. If I try to make another booking for room number 6 for the start date of 20/12/2014 and the end date of 27/12/2014 or the start date of 23/12/2014 and the end date of 03/01/2015, the double booking should automatically be prevented as it falls in the time frame for when room number 6 has already been booked.

Simply, when I click the 'Save Booking Details' button, it should check whether the booking requested is possible; if it is possible then the booking should be saved as a record in the Bookings table and a message should appear confirming the booking. If not, a message should inform the user that the room is already booked for that time-frame/a double booking was attempted.

I think the solution lies with a VB Code which would be put as the Event Procedure of the button however I kindly request that you provide it. I have looked significantly on the Internet for a solution and haven't found one and I sincerely request your help. Please do not hesitate to request any images of the database.

Kind Regards,
RandomGuy123

 

[jdraw]

See this free data model to help with table structures. This is a generic model parts of which may apply to your project.

Also, research unique composite index, to help with duplicate prevention.
Here're 2 link and this link but there are many.

Good luck with your project.

 

[RandomGuy123]

See this [This is a generic model parts of which may apply to your project.

Also, research unique composite index, to help with duplicate prevention.
Here're 2 but there are many.

Good luck with your project.

Hi there, thanks for your response. I was however hoping if anyone was able to provide me with a custom code that I can simply pop into my database since I have received similar and extensive help from people here on Access World Forums in the past. It's just that I'm a real novice! I looked at your links and they were helpful but still far too advanced & extensive for my needs. Something that is ready made for my needs is quite a demand but I would be immensely grateful for such support.

 

[MarkK]

I would be immensely grateful

Free room at your hotel? For free code? Sounds fair to me.

 

[smig]

a picture of the relationships can help.

First thing to do is write for yourself "What a double booking is ?" Once you clearly know what you are looking for it will be easier to know what to do.

You must remember that hotel's booking is by far more complex then flight booking. Most visitors won't book for a single night and you can't change room in the middle of the visit. They also don't book for a specific room.
You must have a lot of complexity to rearrange the rooms for maximum use.

 

[TJPoorman]

In a nutshell what you need to do is check if there are any records with that room that are not for the current customer within the date range. You need to check the date in four different ways (any records outside the input, inside the input and one each with one in and one out)

Take the following diagram where () represents your bookingTableDates, x equals your start query, and y equals your end query:
X()Y
(XY)
X(Y)
(X)Y

So the following code should get you on the right track (you may need to adjust the greater than and less thans):

Public Function CheckDoubleBook(lngRoomID As Long, lngCustomerID As Long, datStart As Date, datEnd As Date) As Boolean
'This function will return True if the room is double booked
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblBooking WHERE RoomID=" & lngRoomID & " AND CustomerID<>" & lngCustomerID & _
        " AND ((BookingStartDate>=#" & datStart & "# AND BookingEndDate<#" & datEnd & "#)" & _
            " OR (BookingStartDate<=#" & datStart & "# AND BookingEndDate>#" & datEnd & "#)" & _
            " OR (BookingStartDate<=#" & datStart & "# AND BookingEndDate>#" & datEnd & "# AND BookingEndDate<#" & datEnd & "#)" & _
            " OR (BookingStartDate>=#" & datStart & "# AND BookingStartDate<#" & datEnd & "#  AND BookingEndDate>#" & datEnd & "#))")

CheckDoubleBook = Not (rs.EOF)
End Function

 

[stopher]

In a nutshell what you need to do is check if there are any records with that room that are not for the current customer within the date range. You need to check the date in four different ways (any records outside the input, inside the input and one each with one in and one out)

Actually it's a bit simpler than that. Only need to check for one case. Take a look here:
http://www.baldyweb.com/OverLap.htm

Also, the function in not interested in customer. It simply checks if a room is already booked for any par of given dates:

So you could do something like:

Public Function CheckDoubleBook(lngRoomID As Long, datStart As Date, datEnd As Date)
'This function will return True if the room is double booked

If DCount("[BookingID]", "Bookings", "RoomID=" & lngRoomID & _
        " AND BookingStartDate<=#" & datEnd & "# AND BookingEndDate>=#" & datStart & "#") > 0 Then
    CheckDoubleBook = True
Else
    CheckDoubleBook = False
End If
        
End Function

 

[smig]

No one is booking for a specific room !!!
the hotel need to check if they have A room available at the requested dates.

And no, it's not simple at all.
if you take this Overlaping records from Baldy example you can see it's not simple.
A good reservation system will put 1 and 6 on the same room, 2 and 4 on the same room and 3 and 5 on the same room.
When a new reservation come in all can be changed again......

A good reservation system will not only take care for double booking but will take care for best efficiency.

 

[stopher]

No one is booking for a specific room !!!
the hotel need to check if they have A room available at the requested dates.

How do you know that? If a hotel has 6 individually styled rooms then it is quite reasonable for a guest to want to book the Georgian room. How do you the user hasn't already selected a room via some method?

And no, it's not simple at all.
if you take this Overlaping records from Baldy example you can see it's not simple.
A good reservation system will put 1 and 6 on the same room, 2 and 4 on the same room and 3 and 5 on the same room.
When a new reservation come in all can be changed again......

The OP requirements were quite clear. He wants to check that a double booking is not being made. For all we know he as already got some fancy stuff for showing availability and selecting rooms but in his question he just wants to prevent double bookings. This requirement is a database structure constraint (albeit implemented in the form) whereas the problem you are suggesting is an interface/user requirement as far as I can see.[/QUOTE]

 

[smig]

How do you know that? If a hotel has 6 individually styled rooms then it is quite reasonable for a guest to want to book the Georgian room. How do you the user hasn't already selected a room via some method?

The OP requirements were quite clear. He wants to check that a double booking is not being made. For all we know he as already got some fancy stuff for showing availability and selecting rooms but in his question he just wants to prevent double bookings. This requirement is a database structure constraint (albeit implemented in the form) whereas the problem you are suggesting is an interface/user requirement as far as I can see.

[/QUOTE]
honesty I don't really know what the OP requirements are.
If all he need is the Double Booking issue, so yes, the link you gave him will put him on the right truck.

 

[RandomGuy123]

Bare with me guys.

So I've made two separate copies of the database in order to prevent too many errors/changes. I've used both your provided codes - TJPoorman's code in one copy and Stopher's in the other copy.
I've put each code as the event procedure for a blank command button however every time I test it I get the following error:

'Compile Error: Expected End Sub'

I've tried adding an End Sub at the end of the code but its not working.

Here is Stopher's:

Private Sub Command24_Click()
Public Function CheckDoubleBook(lngRoomID As Long, datStart As Date, datEnd As Date)
'This function will return True if the room is double booked

If DCount("[BookingID]", "Bookings", "RoomID=" & lngRoomID & _
        " AND BookingStartDate<=#" & datEnd & "# AND BookingEndDate>=#" & datStart & "#") > 0 Then
    CheckDoubleBook = True
Else
    CheckDoubleBook = False
End If
        
End Function
End Sub

Here is TJPoorman's:

Private Sub Command24_Click()
Public Function CheckDoubleBook(lngRoomID As Long, lngCustomerID As Long, datStart As Date, datEnd As Date) As Boolean
'This function will return True if the room is double booked
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblBooking WHERE RoomID=" & lngRoomID & " AND CustomerID<>" & lngCustomerID & _
        " AND ((BookingStartDate>=#" & datStart & "# AND BookingEndDate<#" & datEnd & "#)" & _
            " OR (BookingStartDate<=#" & datStart & "# AND BookingEndDate>#" & datEnd & "#)" & _
            " OR (BookingStartDate<=#" & datStart & "# AND BookingEndDate>#" & datEnd & "# AND BookingEndDate<#" & datEnd & "#)" & _
            " OR (BookingStartDate>=#" & datStart & "# AND BookingStartDate<#" & datEnd & "#  AND BookingEndDate>#" & datEnd & "#))")

CheckDoubleBook = Not (rs.EOF)
End Function
End Sub

Also, just for some additional information, the relationships in my database are:

Customers to Bookings - 1 to Many - Customer ID to Customer ID
Rooms to Bookings - 1 to Many - Room ID to Room ID
Employees to Bookings - 1 to Many - Employee ID to Employee ID

How can I resolve the error and what I am I doing wrong?
Also does it matter if my field names in the tables have spaces e.g. Booking Start Date instead of BookingStartDate?

 

[burrina]

Cartesian query to get all bookings.

SELECT tblDate.TheDate, tblBookingTimes.TheTime, [tblDate].[TheDate]+[tblBookingTimes].[TheTime] AS SlotStart, [SlotStart]+TimeSerial(0,14,59) AS SlotEnd
FROM tblDate, tblBookingTimes
WHERE (((tblBookingTimes.TheTime) Between #12/30/1899 8:0:0# And #12/30/1899 15:59:59#));

qryFreeSlots

SELECT qryCartesianAllBookingDateTimes.*
FROM qryCartesianAllBookingDateTimes LEFT JOIN qryOccupiedSlots ON (qryCartesianAllBookingDateTimes.SlotEnd = qryOccupiedSlots.SlotEnd) AND (qryCartesianAllBookingDateTimes.SlotStart = qryOccupiedSlots.SlotStart) AND (qryCartesianAllBookingDateTimes.TheTime = qryOccupiedSlots.TheTime) AND (qryCartesianAllBookingDateTimes.TheDate = qryOccupiedSlots.TheDate)
WHERE (((qryOccupiedSlots.TheDate) Is Null));

tblBookings
tblBookingTimes
tblCustomers
tblDate
tblLocation

HTH

 

[jdraw]

Hers is another free data model to help in establishing the tables and relationships to match your needs.

Also does it matter if my field names in the tables have spaces e.g. Booking Start Date instead of BookingStartDate?

It is considered good practice to not have embedded spaces or special characters in field and object names. The presence of spaces within names requires that such names be enclosed in square brackets [field with space in name] wherever used.

Before getting into too much code, I recommend you get your tables set up to match your needs. Get some test data typical of your expectations and test that data against your evolving model. When the data and model are working without issue then move to specific code. If in your testing scenarios there is a glitch, reconcile that issue before moving on -- is it the data, is it the model??? resolve and test again.
Good luck.

 

[RandomGuy123]

Before getting into too much code, I recommend you get your tables set up to match your needs. Get some test data typical of your expectations and test that data against your evolving model. When the data and model are working without issue then move to specific code. If in your testing scenarios there is a glitch, reconcile that issue before moving on -- is it the data, is it the model??? resolve and test again.
Good luck.

That's the thing. Its compulsory for me to remain with the current tables and table structures - I need to be able to prevent double bookings with the current tables.

 

[jdraw]

Take a screen capture of your relationships window with tables expanded to show all fields. Then save as a jpg file. Then create a zip and post that zip so readers can see your table structures and relationships.

Is your database an operational database? That is, do you use it in support of a business?
Do you have double bookings now?

You still need some test data. Create soe scenarios with your "expected" outcomes. Test the model (even your current set up) with the data and see what are the "observed" outcomes. Be prepared to rationalize/reconcile any difference between expected and observed outcomes.

 

[RandomGuy123]

Take a screen capture of your relationships window with tables expanded to show all fields. Then save as a jpg file. Then create a zip and post that zip so readers can see your table structures and relationships.

Attached is a zip file containing an image of the relationship.

My current client requires that the current tables and table structures remain the same/as similar as possible to the existing format. However he also wants to prevent double bookings automatically without changing essential existing aspects of the system.

In the past, for a different database booking system, we had prevented double bookings from occurring with this extremely helpful code:

Private Sub PBButton_Click()
Dim rst As dao.Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT ViewingPropertyID, BuyerID, StaffID FROM Viewings WHERE ((ViewingDate=#" & ViewingDateForm & "# AND ViewingPeriod='" & ViewingPeriodForm & "') AND (ViewingPropertyID=" & ViewingPropertyIDForm & " OR StaffID=" & StaffIDForm & ")) OR (ViewingPropertyID=" & ViewingPropertyIDForm & " AND BuyerID=" & BuyerIDForm & ")")
If Not rst.EOF Then 'there is a clash
    If rst!ViewingPropertyID = ViewingPropertyIDForm Then MsgBox "Property Double Booked!"
    If rst!StaffID = StaffIDForm Then MsgBox "Agent Double Booked!"
    If rst!BuyerID = BuyerIDForm Then MsgBox "Buyer Double Booked!"
    Me.Undo
Else ' there is not a clash
    DoCmd.Save
    MsgBox "Congratulations! The Booking has been made."
End If
Set rst = Nothing
End Sub

This code was fantastic, simple and worked beautifully for bookings that were being made for a single date and timeslot. Once the user had selected their date and timeslot, they simply clicked the 'Process Booking' button and the event procedure would run - you guys being the experts don't need an explanation of how the code works.
We were hoping for a similar code to be applied to this situation which has a booking start date and a booking end date for bookings being made however this is proving rather troublesome.

 

[jdraw]

Do you have a unique composite index on:

RoomId
BookingStartDate and
BookingEndDate?

That will prevent duplicates via the database.

Is billing a separate process and database?
How will you record any info related to Car Rental or Airport Transportation etc.?

Do you have some test data and some scenarios for testing?

Where do your ViewingPropertyID and BuyerID= fit? I don't see such fields in your tables.

It looks as if those fields and perhaps the SQL was lifted from a real estate/property data base.

 

[TJPoorman]

Bare with me guys.

So I've made two separate copies of the database in order to prevent too many errors/changes. I've used both your provided codes - TJPoorman's code in one copy and Stopher's in the other copy.
I've put each code as the event procedure for a blank command button however every time I test it I get the following error:

'Compile Error: Expected End Sub'

I've tried adding an End Sub at the end of the code but its not working.

Here is Stopher's:

Private Sub Command24_Click()
Public Function CheckDoubleBook(lngRoomID As Long, datStart As Date, datEnd As Date)
'This function will return True if the room is double booked

If DCount("[BookingID]", "Bookings", "RoomID=" & lngRoomID & _
        " AND BookingStartDate<=#" & datEnd & "# AND BookingEndDate>=#" & datStart & "#") > 0 Then
    CheckDoubleBook = True
Else
    CheckDoubleBook = False
End If
        
End Function
End Sub

Here is TJPoorman's:

Private Sub Command24_Click()
Public Function CheckDoubleBook(lngRoomID As Long, lngCustomerID As Long, datStart As Date, datEnd As Date) As Boolean
'This function will return True if the room is double booked
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tblBooking WHERE RoomID=" & lngRoomID & " AND CustomerID<>" & lngCustomerID & _
        " AND ((BookingStartDate>=#" & datStart & "# AND BookingEndDate<#" & datEnd & "#)" & _
            " OR (BookingStartDate<=#" & datStart & "# AND BookingEndDate>#" & datEnd & "#)" & _
            " OR (BookingStartDate<=#" & datStart & "# AND BookingEndDate>#" & datEnd & "# AND BookingEndDate<#" & datEnd & "#)" & _
            " OR (BookingStartDate>=#" & datStart & "# AND BookingStartDate<#" & datEnd & "#  AND BookingEndDate>#" & datEnd & "#))")

CheckDoubleBook = Not (rs.EOF)
End Function
End Sub

Also, just for some additional information, the relationships in my database are:

Customers to Bookings - 1 to Many - Customer ID to Customer ID
Rooms to Bookings - 1 to Many - Room ID to Room ID
Employees to Bookings - 1 to Many - Employee ID to Employee ID

How can I resolve the error and what I am I doing wrong?
Also does it matter if my field names in the tables have spaces e.g. Booking Start Date instead of BookingStartDate?

You need to put the code that we supplied in a general module, then call it from your command button. The call should look like this:

Private Sub Command24_Click()
    CheckDoubleBook(Me.RoomID, Me.CustomerID, Me.DateStart, Me.DateEnd)
End Sub

 

[RandomGuy123]

You need to put the code that we supplied in a general module, then call it from your command button. The call should look like this:

Private Sub Command24_Click()
    CheckDoubleBook(Me.RoomID, Me.CustomerID, Me.DateStart, Me.DateEnd)
End Sub

Okay, so I've put the code in a general module and I'm trying to call it from the command button using the above code however I keep getting the error:

Expected =

 

[TJPoorman]

Sorry, I thought you had the logic already on your button. Try this:

Private Sub Command24_Click()
If CheckDoubleBook(Me.RoomID, Me.CustomerID, Me.DateStart, Me.DateEnd) Then
    MsgBox "There is a conflict with this reservation"
Else
    'Code here for not double booked
End If
End Sub