Matt,
Paste this routine into the module for BookingQuery. The names all
match the mdb you sent me. It should run when you click the "Search
for a room..." button.
NOTE: No code lines should wrap. You may need to unwrap lines after
pasting.
NOTE: You cannot currently uncheck Ensuite required because you have
it bound as an option of Frame38. You may want to delete this check
box and put in a different check box that is not bound to an option
group. To do this, draw the check box outside of the option group,
then drag it into place. Then, change the code to test its value,
instead of Frame38's value.
' *********** Code Begin
Private Sub Command74_Click()
On Error GoTo Err_Handler
' Declare some variables
Dim cnn As ADODB.Connection
Dim rstRooms As ADODB.Recordset
Dim strSQL As String
Dim strWhere As String
' Get ADO variables ready to get data
Set cnn = CurrentProject.Connection
Set rstRooms = New ADODB.Recordset
' Start building the query
strSQL = "SELECT RoomNo FROM RoomType"
' See if EnSuite is required and adjust the WHERE clause to match
If Frame38.Value = True Then
strWhere = " WHERE Type Like '" & Combo71.Value & "*'"
Else
strWhere = " WHERE Type = '" & Combo71.Value & " ensuite'"
End If
' Only retrieve rooms where there are no booking collisions.
' The NOT EXISTS criteria will exclude rooms with colliding dates
strWhere = strWhere & " AND NOT EXISTS(SELECT RoomNo FROM Booking
WHERE RoomNo = RoomType.RoomNo And (((#" & DateFrom.Value & "# <=
DateTo) And (#" & DateTo.Value & "# >= DateFrom)) = True))"
strSQL = strSQL & strWhere
strSQL = strSQL & " ORDER BY Type, RoomNo;"
' Open a recordset based on the query
rstRooms.Open strSQL, cnn, adOpenForwardOnly
' If there are available rooms, use the first one.
If Not rstRooms.BOF And Not rstRooms.EOF Then
rstRooms.MoveFirst
Booking_RoomNo = rstRooms!RoomNo
Else
MsgBox "No rooms of the selected type available for the
requested dates."
End If
Exit_Me:
' Clean up so we don't leak memory.
If rstRooms.State = adStateOpen Then
rstRooms.Close
End If
Set rstRooms = Nothing
Set cnn = Nothing
strWhere = ""
strSQL = ""
Exit Sub
' *********** Code End
Good luck!
- Hammer |
Clarification of Answer by
hammer-ga
on
04 Apr 2003 16:40 PST
Matt,
I just realized that, if you replace your Ensuite Required checkbox as
I suggested, the condition will be reversed. If you create a new
checkbox named EnsuiteRequired, the condition should look like this:
' See if Ensuite is required and adjust the WHERE clause to match
If EnsuiteRequired.Value = True Then
strWhere = " WHERE Type = '" & Combo71.Value & " ensuite'"
Else
strWhere = " WHERE Type Like '" & Combo71.Value & "*'"
End If
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
05 Apr 2003 11:09 PST
Hi, thanks for that. It's almost working! just testing it still as
isnt completely working!
cheers matt
|
Clarification of Answer by
hammer-ga
on
05 Apr 2003 12:42 PST
Is there something you need from me in order to get it working?
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
06 Apr 2003 07:06 PDT
Its still now quite working - may have to put it on here again.
Am still working on slight problems with it but in the mean time could
you tell me how to check there is something in the fields (date from,
date to, ensuite, room type) before the rooms are looked up? as it
needs to display an error message such as "please ensure all the
fields are complete"
Thanks, matt
|
Clarification of Answer by
hammer-ga
on
06 Apr 2003 08:22 PDT
I tested it on your database, and the routine met the requirements
outlined in your question, with the exception of the EnsuiteRequired
issue, which I explained.
As to your additional issue regarding validation, it depends on the
kind of control. For a text box, you can check if the value is blank.
If Not DateFrom.Value = "" Then
' Do everything.
Else
MsgBox "Please enter a DateFrom."
End If
A full validation routine is outside the scope of your original
question, particularly in light of the question price. If you want me
to add full validation to this, please post another question, along
with a link to your database in its current state.
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 03:40 PDT
Hi, sorry I didn't get back to you for a while. I had a bit of trouble
implimenting the code you gave me as you didnt unclude the error
handler bit I don't think. Well it wouldnt work and I added that from
the other one and messed around with it. It now works fine checking
for ensuite and room type but unfortunately the date part doesn't
work.
I just booked in a person for a room with ensuite and single it gives
room 1 fine but then book someone else in exactly the same
requirements and it still gives them room 1.
Yet booking in another person with different room type gives them the
right room so I think it is the date part which is definently not
working:
strWhere = strWhere & " AND NOT EXISTS(SELECT RoomNo FROM Booking
WHERE RoomNo = RoomType.RoomNo And (((#" & DateFrom.Value & "# <=
DateTo) And (#" & DateTo.Value & "# >= DateFrom)) = True))"
Thanks, matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 04:48 PDT
Matt,
Can you repost your database? It was a bit difficult testing with the
Ensuite checkbox bound to the frame the way it was. I'd like to
troubleshoot this with your database in its current condition.
I apologize for the cut-and-paste error with the Error handler tag.
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 05:48 PDT
Hi, that's ok it did me good to figure out some of the coding!!
This is the link to the latest one. You might have to ignore a bit of
the code at the top I added to try and ensure that the appropriate
fields were filled before pressing button but it didnt really work!
just got around it by putting default dates it, but anyway here is the
link:
http://216.10.19.244/work/hotel030406w.mdb
thanks very much for your help and please let me know when you've got
it so i can take it off of server. cheers, matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 06:17 PDT
Got it.
- Hammer
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 06:29 PDT
Matt,
I think you may be seeing some tricky, yet logically correct,
behavior. If you select Single and check Ensuite, then click the
button, it assigns Room #1. If you immediately click again, it assigns
Room 1 again. This is because you have not yet SAVED the booking to
the Bookings table. Therefore, when you click the button again, there
is not yet a record in the Bookings table to cause a date collision.
However, if you advance the BookingQuery form so the record is saved,
then retreat and click the button again, it should now assign Room 2.
Check this out and let me know.
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 06:34 PDT
I dont think so because I open up customer form and fill in customer
details then proceed to booking form. I fill in details it assigns
room 1 then close that form. i check in the booking table and it has
saved it. I then repeat the process and that is when it reassigns room
1 to a different customer.
thanks matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 06:46 PDT
Matt,
I'm not getting this behavior. I'm getting a lot of error messages,
but when I get through them it seesm to be doing the right thing. I
created a Booking (got Room 1), clicked Confirm, entered the Customer
Info, clicked Continue. created another Booking with the same
parameters and got Room 2.
Could you write me out a detailed set of steps to cause your problem.
Tell me exactly what to click, what to type, etc.
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 07:55 PDT
hi, ok, i open the database. first of all open up booking TABLE and
delete all data so it doesnt get in the way.
goto customerQuery (form) and put in 2 click lookup then click
continue
leave the dates how they are and leave ensuite ticked, chose single
room, click searchj button it finds room one. now click confirm (this
just closes form)
the customer form is still open type in 1 this time, lookup and then
continue
Do exactly the same on the booking form. leave dates how they are
leave ensuite ticked, single room.
Now it should give this customer room 2 but click search - it gives
room 1!
Now close the windows and open booking TABLE. you can see they are
both in there fine but room 1 is on both of them which cannot be right
when the dates are the same.
cheers, matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 08:08 PDT
I followed your steps exactly, 4 times. Each time, the first customer
got Room 1, the second customer got Room 2. The records in the
Bookings table looked correct. They had the same dates, but different
Room numbers and different Customer ID's.
I'm not sure what to tell you, Matt. I can't find the problem if I
can't make it happen. Are you sure you didn't leave out a step? I'm
working with your downloaded database, so we should be seeing the same
results unless you made changes after uploading it.
- Hammer
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 08:16 PDT
I also just tried a few more experiments with assigning many rooms in
a row to the same customer, same dates, same room types, different
room types, etc. I can't get it to fail. It assigns all the rooms of
any available type, in order, then throws the error message when there
are no more of that type, as it should.
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 08:21 PDT
http://216.10.19.244/work/bookingtable.gif there a snapshot of my
booking table its not too clear but you can see it gives different
customers the same rooms on same dates! im really confused as to why
it should work for you and not me!
Thanks, matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 08:36 PDT
You're in Europe!!! Is that right? You're using a European date format?
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 08:53 PDT
hello, yep I am in UK. BUT.... I am putting in 2 dates that are the
same and so are you so why does that make any difference?
That will be good if it does solve the problem though
Thanks, matt
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 08:54 PDT
Just put in some American dates and it worked fine!!! how to I change it?? thanks
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 09:18 PDT
Try this. If it doesn't work, we'll have to get more complicated.
Remember to unwrap the line of code. Note that we are now running your
dates through DateValue before handing them to SQL. This is a problem
because Access assumes that dates in SQL are in American format. Your
tables, however, know what format they are in. Therefore, you are
comparing dates in Sept and Oct to dates in April. That's why you are
not getting collisions!
strWhere = strWhere & " AND NOT EXISTS(SELECT RoomNo FROM Booking
WHERE RoomNo = RoomType.RoomNo And (((DateValue('" & DateFrom.Value &
"') <= DateTo) And (DateValue('" & DateTo.Value & "') >= DateFrom)) =
True))"
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 09:29 PDT
Hi, i unwrap the code and it still says compile error: Expected
expression. And it highlight one of the "'" in the code
thanks, matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 09:36 PDT
Try it again. Make sure you don't accidentally delete a character when
pulling the line together.
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 10:16 PDT
Hi, still working on that code as its still giving syntax errors so
looking into it but...
surely if im booking someone in from the 09/04/2003 (uk date) till
10/04/2003
the computer is interpreting this as 4th september untill 4th october
but when i book another person in from the 09/04/2003 (uk date) till
10/04/2003
the computer is interpreting this also as 4th september untill 4th
october
so it shouldnt make any difference
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 10:36 PDT
Surely not. :>
The SQL statement thinks it has Sep 04, however, the value in the
database is actually a date and comes out as Apr 09. No match.
- Hammer
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 10:38 PDT
BTW, here's a fresh paste. I'm cutting right out of compiling/running
code.
strWhere = strWhere & " AND NOT EXISTS(SELECT RoomNo FROM Booking
WHERE RoomNo = RoomType.RoomNo And (((DateValue('" & DateFrom.Value &
"') <= DateTo) And (DateValue('" & DateTo.Value & "') >= DateFrom)) =
True))"
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 10:49 PDT
hi, it still creates a compile error. Would you be able to upload your
copy to the net please? I really dont understand why it isnt working.
I had to change the syntax on one of the other pieces of code you gave
me before it worked but where its such a long line its hard to look at
it. are u able to upload it or email it to me?
thanks, sorry about this - this project is turning into being the most
complex thing ive done - nothing seems to work properly!
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 11:17 PDT
http://www.hammerdata.com/Google/hotel030406w.mdb
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 11:35 PDT
thanks, got it. please remove from your server. will give it a
thorough test and let you know. thanks, matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 11:43 PDT
It's down.
- Hammer
|
Request for Answer Clarification by
mattie54-ga
on
09 Apr 2003 13:49 PDT
Hello, seems to be working. Will test it more tomorrow and get back to
you. hopefully this problem will be over!
thanks, matt
|
Clarification of Answer by
hammer-ga
on
09 Apr 2003 14:18 PDT
OK, Matt. Hopefully, we can get this question closed soon.
- Hammer
|
,
0 Comments
)
