Preventing Double Bookings - CODE NEEDED! (1 Viewer)

[RandomGuy123]

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

After trying the following code:

Public Function CheckDoubleBook(lngRoomID As Long, lngCustomerID As Long, datBookingStartDate As Date, datBookingEndDate 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 Bookings 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

With the call in the command button:

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

I get the error in the JPG file named Capture 1.

When I use the other code:

Public Function CheckDoubleBook(lngRoomID As Long, datBookingStartDate As Date, datBookingEndDate 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

and use the same call from the command button I get the error:

'Compile error: wrong number of arguments or invalid property assignment'

Please note that the logic that you gave me for the call for the command button:

[CODE]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

[/CODE]

I have changed it to the following - I hope I did the right thing:

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

 

[TJPoorman]

Place this in your Module (you need to adjust the names in RED to match your tables. Don't change anything else)
EDIT: Also, if your field names or table names contain a space, make sure to enclose the name in brackets ie: [My Field With Space]

Public Function CheckDoubleBook(lngRoomID As Long, lngCustomerID As Long, datStartDate As Date, datEndDate 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 [COLOR="red"]Bookings [/COLOR]WHERE [COLOR="red"]RoomID[/COLOR]=" & lngRoomID & " AND [COLOR="red"]CustomerID[/COLOR]<>" & lngCustomerID & _
        " AND ([COLOR="red"]BookingStartDate[/COLOR]<#" & datEndDate & "# AND [COLOR="red"]BookingEndDate[/COLOR]>=#" & datStartDate & "#)")

CheckDoubleBook = Not (rs.EOF)
End Function

Then put this in your command button:

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

 

[jdraw]

RandomGuy,

Do you have any answers for my questions in post #17?

 

[RandomGuy123]

To TJPoorman

Place this in your Module (you need to adjust the names in RED to match your tables. Don't change anything else)
EDIT: Also, if your field names or table names contain a space, make sure to enclose the name in brackets ie: [My Field With Space]

Public Function CheckDoubleBook(lngRoomID As Long, lngCustomerID As Long, datStartDate As Date, datEndDate 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 [COLOR="red"]Bookings [/COLOR]WHERE [COLOR="red"]RoomID[/COLOR]=" & lngRoomID & " AND [COLOR="red"]CustomerID[/COLOR]<>" & lngCustomerID & _
        " AND ([COLOR="red"]BookingStartDate[/COLOR]<#" & datEndDate & "# AND [COLOR="red"]BookingEndDate[/COLOR]>=#" & datStartDate & "#)")

CheckDoubleBook = Not (rs.EOF)
End Function

Then put this in your command button:

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

Hi there, I entered the codes exactly the way you posted them and placed them in the correct locations as instructed. Furthermore the bits that you highlighted in red were already matched to my database so apart from double-checking, I didn't do anything to them.

However now when I create a booking (see Capture 2.jpg file) and then try to create an identical booking for the same room, start date and end date again (signalled by the changed Booking ID in Capture 3.jpg), and then click the button that would run the event procedure and call the public function, it simply does nothing. I don't understand why it does nothing but it just doesn't for some reason - no message, no error, no nothing.

To jdraw

RandomGuy,

Do you have any answers for my questions in post #17?

Apologies, I missed your post!

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.

I don't really know what a unique composite index is but if you're referring to a composite key (like a primary/foreign key), I currently do not have one for the Bookings table.

Billing isn't currently in the picture - its a rather rudimentary database that has little room for change (in terms of table items/table structures) - billing is done manually.

Checkboxes would simply be used to indicate whether a customer requires airport transportation e.g. when the receptionist is talking to the customer on the phone.

As for test data, see the attached screenshots.

The ViewingPropertyID field is a field that has been taken from an extremely simple real estate system which makes bookings/viewings for buyers at the required property. Why I mentioned that code is because the buyers would simply have one date and one timeslot (out of four in a drop down list) and due to the simplistic nature of such a booking, the code I included had been used to prevent double bookings however with this hotel booking system (which by the way is still simple), it has variable length bookings with two dates; a start date and an end date which is proving to be problematic!

 

[TJPoorman]

In looking at your previous post with your relationships, I can see that the fields are not the same (your table has spaces). This should be what the module function looks like:

Public Function CheckDoubleBook(lngRoomID As Long, lngCustomerID As Long, datStartDate As Date, datEndDate 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 Bookings WHERE [Room ID]=" & lngRoomID & " AND [Customer ID]<>" & lngCustomerID & _
        " AND ([Booking Start Date]<#" & datEndDate & "# AND [Booking End Date]>=#" & datStartDate & "#)")

CheckDoubleBook = Not (rs.EOF)
End Function

Furthermore, the if statement won't return anything if there is no double book. So to check if the code is working you will need to put something in the else, so your if statement would look like this:

Private Sub Command24_Click()
If CheckDoubleBook(Me.RoomID, Me.CustomerID, Me.BookingStartDate, Me.BookingEndDate) Then
    MsgBox "There is a conflict with this reservation"
Else
    MsgBox "There are no conflicts with this reservation"
End If
End Sub

 

[RandomGuy123]

abcdefghijkl