Hotel booking (double bookings)

[markymark34]

Im messing around trying to learn Access and have hit a problem.

I have a table called tblbookings and i want to seach though it when trying to make a new booking to make sure the room isnt alrady booked.

In tblbookings i have a room_number, booking_start_date and booking_end_date

Ive tried all sorts of code but it either brings up an error or doesnt do anything at all. Hoping someone can help.

Ive got as far as bringing up a message box if it finds the same room already in the booking table (which obviously isnt very useful but its as far as i can get!)
Im using the following code:

Can someone have a look and tell me what i should be doing. Cheers

If Me.NewRecord = True Then
Dim strWhere As String, strmessage As String

strWhere = "Room_Number = " & Me!Room_Number

Me.RecordsetClone.MoveFirst
Me.RecordsetClone.FindFirst strWhere

Do Until Me.RecordsetClone.nomatch
If Me.RecordsetClone!Room_Numnber = Me!Room_Number Then
MsgBox ("test")
Cancel = True
Exit Sub
End If
Me.RecordsetClone.FindNext strWhere
Loop
End If

[NeoPa]

Try this :-

If Me.NewRecord = True Then
    Dim strWhere As String, strMessage As String
    Dim rsClone as RecordSet
 
    strWhere = "((Room_Number=" & Me.Room_Number & _
               ") AND (Booking_End_Date>=#" & _
               Format(Me.Booking_Start_Date,"m/d/yyyy") & _
               "#) AND (Booking_Start_Date<=#" & _
               Format(Me.Booking_End_Date,"m/d/yyyy") & _
               "#))"
 
    set rsClone = Me.RecordsetClone
    rsClone.MoveFirst
    rsClone.FindFirst strWhere
 
    If rsClone.NoMatch then
        MsgBox ("test")
        Cancel = True
        Exit Sub
    End If
End If

[markymark34]

excellent cheers.

only probelm is this wont catch bookings made at different dates eg

room 101 - booked from 1st dec - 5th dec
room 101 - booked from 2nd dec - 4th dec.

ive found the function DateDiff() but im not sure if its going to help?

thanks

Mark

[NeoPa]

excellent cheers.

only probelm is this wont catch bookings made at different dates eg

room 101 - booked from 1st dec - 5th dec
room 101 - booked from 2nd dec - 4th dec.

ive found the function DateDiff() but im not sure if its going to help?

thanks

Mark

Why would it not?
That's what the complicated stuff in the WHERE clause is for.
Do you say that after testing or is it what you'd expect to happen?

[markymark34]

yes thanks
was me being silly!