By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,932 Members | 1,540 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,932 IT Pros & Developers. It's quick & easy.

Hotel booking (double bookings)

P: 14
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
Nov 28 '06 #1
Share this Question
Share on Google+
4 Replies


NeoPa
Expert Mod 15k+
P: 31,758
Try this :-
Expand|Select|Wrap|Line Numbers
  1. If Me.NewRecord = True Then
  2.     Dim strWhere As String, strMessage As String
  3.     Dim rsClone as RecordSet
  4.  
  5.     strWhere = "((Room_Number=" & Me.Room_Number & _
  6.                ") AND (Booking_End_Date>=#" & _
  7.                Format(Me.Booking_Start_Date,"m/d/yyyy") & _
  8.                "#) AND (Booking_Start_Date<=#" & _
  9.                Format(Me.Booking_End_Date,"m/d/yyyy") & _
  10.                "#))"
  11.  
  12.     set rsClone = Me.RecordsetClone
  13.     rsClone.MoveFirst
  14.     rsClone.FindFirst strWhere
  15.  
  16.     If rsClone.NoMatch then
  17.         MsgBox ("test")
  18.         Cancel = True
  19.         Exit Sub
  20.     End If
  21. End If
Nov 28 '06 #2

P: 14
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
Nov 28 '06 #3

NeoPa
Expert Mod 15k+
P: 31,758
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?
Nov 29 '06 #4

P: 14
yes thanks
was me being silly!
Dec 4 '06 #5

Post your reply

Sign in to post your reply or Sign up for a free account.