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.
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 05:15.
Reason: broke apart sections to ease reading
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.