Google Answers Logo
View Question
 
Q: Access database - help needed ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: Access database - help needed
Category: Computers > Programming
Asked by: mattie54-ga
List Price: $3.00
Posted: 03 Apr 2003 07:56 PST
Expires: 03 May 2003 08:56 PDT
Question ID: 185415
Hi, I have constructed a hotel database which allows customers to be
booked into rooms. I am stuck as the customer specifies all their
requirements and their booking is saved in the "booking" table but
even if the room is taken for the time scale specified the person is
still booked in!! The person must instead specify what type of room
they want and the timescale then rooms matching the type (stored in
another table) should be checked for the dates!

Due to its rather comple nature I may need to make the database
available online for you to put code in so you can see all the
tables/fields used. And also my lack of knowledge/unable to use VBA.

Thanks, matt

Request for Question Clarification by hammer-ga on 03 Apr 2003 08:36 PST
Matt,

I need more info to work on this. Please post your database where I
can download it, so I know what questions to ask you. Then I can make
the adjustments and give you back the altered database so you can see
what I did.

- Hammer

Clarification of Question by mattie54-ga on 03 Apr 2003 09:26 PST
hi, I uploaded it to http://216.10.19.244/work/hotelforanswers.mdb

basiclly its on "bookingquery" which is confusingly a form! where the
person specifies the details and when the button is pressed it needs
to check the room availability. All the calculations at the bottom
work but only when the room number is specified by the used.

The table Roomtype is used to check what type the room is e.g. "single
ensuite"

The table booking contains the dates when rooms are already booked for
a room should not be suggested if it has already been booked.

Hope thats all clear!

Basically when the user clicks button "search for room matching
requirements" text needs to come up saying room 7 is available and
matches or whatever like "sorry no rooms match your requirements
please try again". Then the room number found is assigned to
Booking_RoomNo and all the calculations bellow then work fine for that
room and the booking is added to the database

Thanks! matt

Clarification of Question by mattie54-ga on 03 Apr 2003 09:27 PST
please post on here when you have downloaded it so that I can remove
it from server. thanks matt

Request for Question Clarification by hammer-ga on 03 Apr 2003 09:29 PST
I've got it, Matt.

- Hammer

Request for Question Clarification by hammer-ga on 03 Apr 2003 09:31 PST
Do you want to offer a list of the available matching rooms or do you
just want to assign the first available match?

- Hammer

Clarification of Question by mattie54-ga on 03 Apr 2003 14:08 PST
just first available match, nothing too complex :-) thanks
Answer  
Subject: Re: Access database - help needed
Answered By: hammer-ga on 04 Apr 2003 06:55 PST
Rated:5 out of 5 stars
 
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
mattie54-ga rated this answer:5 out of 5 stars and gave an additional tip of: $1.00
Thanks very much. sorry it took me a while to reply been busy with
rest of project! great answer A+++++

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy