Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Question Unanswered: Reservation Availability

    Hi thanks in advance for any help you may be able to provide.

    I have been working on a reservation database for a small Bed & Breakfast.

    I am almost done. However I need a way to check that a room has not been double booked.

    I have been reading a few of the other posts but that did not help me. Sorry this is still new to me.

    My database has a table called customer that contains

    customerName, arrivalDate, departureDate and contains the data from the room selection dropdown that has three rooms to select.

    This is all filled into a form that populates the table customer from the customer query that is usedby the forms.

    I want a way to validate that a room has not already been booked during the requested time.

    any suggestions.
    Attached Files Attached Files
    Last edited by deborahdw; 12-09-04 at 00:49. Reason: added attachment

  2. #2
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    My suggestion would be to do a bit of redesign and have a table for reservations that has an index of 2 fields; RoomNo and Date and then has the other relevant information such as Guest, Rate, etc. I would make the index the Primary Key, which would not allow a duplicate RoomNo/Date combination. This would require 3 lines for a 3-night stay by a Guest, but that would eliminate the chance of double-booking. If you want to stay with your current design, there will be some other folks on this forum in a little while that can give some good advice on coding BeforeUpdate statements.

    TD

  3. #3
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi deborahdw,

    What version Access are you using? I was going to take a look but was not recognized. If you can save it to A2K version I would be more than happy to take a look and see if I can help you out.

    thanks BUD

  4. #4
    Join Date
    Dec 2004
    Posts
    2

    Smile Reservation Availability - Thank you and here is the converted file

    Thanks very much. I have coverted the dB to A2K.
    Attached Files Attached Files

  5. #5
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hi again deborahdw,

    I viewed your database, and as suggested by buckeye_td your database needs a bit or redesign. I noticed right off that you didn't create ANY relationships between your Tables. THAT is a good thing to do from the start, hence Relational Database. There has to be a logical manner as to how your Tables are tied (Related) to one another.
    In your Customers and Purchases tables there is a field called JoinID. Just what is that? Reason is it doesn't seem to serve a purpose when I entered data, and not only that but it is set to Indexed - Duplicated / OK.

    PurchasesID form: It would be a good thing(my opinion) to have a ComboBox to select the ProductName that the customer purchased from a list of items you have a known cost for. This in turn will auto-fill in the Description of the product and Cost before tax. Good thing would be to have Qty. of items and then Cost, that way you can charge for 3 towels, 2 pillows etc.

    TabEmailUtilities form: None of the forms opened.
    TabCustomerReservationDetail form: ExistingCustomerRecordsSearch CommandButton / SearchForm....the TotalPrice field has a different Sum ($272.85) than on the TabCustomerServiceReporting PreviewAllReservationInformation CommandButton / DetailReport has another value. ($275.40) You set it with 2 different Tax amounts. Is that what is should be?

    SearchForm: I recreated a CommandButton for you to close that Form instead of the Macro. Reason, the CommandButton wasn't working.

    For aesthetics: I set InputMasks for your Telephone, ZipCode, Arrival/Departure Date and Expiry date.

    Also, I gave you my small tblStates then pulled that into the Customers table. What happens now is that when you input the Customers address with the City, you have a ComboBox for the State. There wasn't a State field on the Form and should be. There are some states that have identically named Cities. Not a biggie but it helps.

    Suggestion: A Table that lists all the Units you have, with Cost per night with it. As it stands from my input on the form for a new guest, you have to either KNOW what the rate is or be looking at some paper to find out. Once you do that when you select in on filling in the form, the Costs come over automatically and you only have to input NOTHING, since the number of nights input already calculate it for you.

    All in all I think you have a very nice looking database, however there are some things underneath that should be looked at. Try inputting data and see how it really works for you. I have done that and will Zip and rePost it here for you to see some of the things I have done. Don't have a lot of time to do it all but can do it little at a time if you don't get it all done sooner. Deb, all this is done in repect to helping you to achieve a very well put together database. After all it is being made to do a task for you and should be easy and flows about logically. When you do it very well you will be greatly recognized for it. It's YOUR signature. Just trying to assist you as best as I can and enjoy helping others. Sometimes I need help too. OH....one more thing....try to rid yourself of the plethora of Macros. VBA would be so much nicer and I see you did Convert one Macro...the way to learn. VBA code stays with the forms without you having to remember to include them if you ever Export part of your database. You have to remember which Macros go with which Form/Report or whatever. Just a tip.

    So enough space has been taken up and I need to get up in about 3 hours (4a.m. here now)
    Stay in touch and I will post my edited version sometime today (Saturday).

    have a nice one
    BUD yaaawwwwnnnnnnnn!!!!!!!!!! niters
    Last edited by Bud; 12-11-04 at 06:15. Reason: broke apart sections to ease reading

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Hello again deborahdw

    Finally got around to posting what I have done with your database so far. Oh, when your Startup Form opens, just wait a very few seconds, don't touch anything and see what happens. Presto chango........ Also, I got rid of that Maximize macro and did it in code for you. There are SO MANY macros and haven't had time to see what they are all associated with. Not sure how much time I will have today but will try to modify it some more for you. Tip: to see how the form automatically closes and the other one opens after a little while, simply check out your StartUp form in DesignView, go to the EventTab and scroll down to [u]OnLoad[/b] and review the short code behind that to Maximize the form. Then scroll down to the bottom to the OnTimer event and check out the code behind that. Lastly, the last item Timer that is where the time is set for the StartUp form to Close and open your entry form. Might get back to more today, but unsure.

    have a nice one,
    BUD
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •