Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    What's happening? (2000)

    I have a table called tblClients, which includes information about people booking at our B&B.
    The second related table (tbleBookings) with referential integrity includes fields indicating the arrival date, departure date and the room being booked.
    I have put a primary key on the arrival date and room fields, so I can't double-book a room for an arrival date.
    I created a form for tblClients with tblBookings as a subform.
    On the subform, I am using the Microsoft Date and Time Picker Control V6.0 for the date fields and making the control source the arrival date or the departure date fields.
    When I try to add a new record, it gives me an error message that says "Can't set Value to Null when Checkbox property = False."
    I haven't any checkboxes on the form or subform, so I don't really know what this means and how to make the form with the subform work properly.
    Any ideas?

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: What's happening? (2000)

    A couple of things to look for:
    Do you have any error trapping in code somewhere? What you are getting could be a bogus error message being created somewhere else and passed to the user interface.
    Are you using any lookup fields at the table level - in other words have you set up a field to display as a combo box, or do you have the subdatasheets option turned on? That could cause some funny business if you have a data validation problem. If you can't turn anything up, post a sample of what you are having trouble with and one of the members may be able to figure something out.

    In a different vein, I presume you have something setup so that dates between the arrival and departure date are blocked off to prevent booking two parties into the same room on a given night. Your description sounded as though you were only locking the arrival and departure dates. Your application is a fairly common one - we've worked on a number of hotel systems with similar arrangements. Best of luck with your application - BTW, I seem to remember a sample database in one of the Access books that solved this problem.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's happening? (2000)

    Thanks for the nice reply. I don't know what an error trapping code is, so I can't answer your first question.
    I was using a combo box. I got rid of it and the same problem persisted.
    Unfortunately, the size of the database exceeds what I can post by about 300k. Do you mind if I send it to you privately?
    And finally, on the different vein, what you described is what I would love to do. The problem is I don't know how to do it. All I have done is to make sure that on the first booking night, the same room won't be double booked, however, the way I have it, I run the risk that on subsequent nights it might be. If you can help me on this, it would be appreciated.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: What's happening? (2000)

    If you don't know about error trapping code then rest assured you don't have any running.

    If your problem persists without the combo box, then either you have some validation rules set up for tables, or the subdatasheets option is causing you grief. To turn off subdatasheets, go to each table you are working with, view it in design mode, and then show the properties window. You will see a property called Subdatasheet Names, typically with an entry of Auto, as that is the default. Replace that with the None option and see if that helps resolve the error message. To look at validation rules, see that property on the table design properties window. Also note that individual fields in a table can also have validation rules.

    As to the design to block double booking, you would typically do that by creating an occupancy record for each night of the stay for a given booking. Doing that is not trivial however. The most straightforward way is writing some code that creates a record for each of the days involved. Since presumably you are at the beginning of the learning curve for VBA, that may not be an option. I will attempt to find the source of the sample database when I return to work tomorrow, and post a message sometime tomorrow.
    Wendell

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What's happening? (2000)

    Do you have the CheckBox property of the date/time picker set to yes? I've found that it doesn't work as you would expect and in fact locks records somehow, so I never use the checkbox, no matter how handy it would be. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's happening? (2000)

    Thank you for your suggestions. I tried them and it didn't solve the problem. Hmmmm.

    And you are dead right on my position on the VBA learning curve.

  7. #7
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's happening? (2000)

    Both date/time pickers have the checkbox property set to no. Why would a date/time picker have a checkbox anyway?

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: What's happening? (2000)

    Actually I think Charlotte has found the issue - the error message says that it can't save a record with a null (probably in the date field) if the checkbox property is set to false - at least that's the way I would interpret it. I've not used the Date/Time control in a long time, so I may be way off base here, but it sure sounds like that's the source of the problem.
    Wendell

  9. #9
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What's happening? (2000)

    No, the checkbox property doesn't control what's in the field, but it does seem to turn access to the field off, which is why I asked about it. I've never figure out exactly what it's supposed to do, but whatever it is, it doesn't work very well. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

    It does sound like the code behind the date/time picker isn't functioning properly though.
    Charlotte

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's happening? (2000)

    I tried setting the check boxes to Yes and it actually worked. True, I have check boxes with my dates, but I can live with that. I wonder why it causes problems when my check boxes are set to No?

    Many thanks to both of you.

  11. #11
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's happening? (2000)

    I'm confused. I have a form with the date time picker, which works OK.

    I saw your last post and out of curiosity went looking for the checkbox property, however there doesn't seem to be one (or at least I can't find it)

    Any clues?

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What's happening? (2000)

    Are you setting the checkboxes to yes/checked or are you setting the checkbox *property* to yes? I don't use checkboxes on the date/time picker because they cause problems like you experienced.
    Charlotte

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: What's happening? (2000)

    Look on the Other tab of the properties dialog. You'll see properties like CalendarTrailingForeCo. CheckBox is right after that.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    St. Marys, Ontario, Canada
    Posts
    270
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: What's happening? (2000)

    I set the checkbox property in the Date/Time pickers to "Yes". It is when I leave them on "No" that I have the problems, which, I believe, is opposite to what you describe. How bizarre!

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: What's happening? (2000)

    I finally had to resort to browsing in a book store, but did manage to find a sample database which is targeted directly at a B&B. The book is "Instant Access Databases" by Greg Buczek, and is published by McGraw-Hill. The ISBN number is 0072130768 and it was published December 29, 2000. Amazon has it for $49.99. I don't think this is the original one I saw, but it looks to have been well received by at least a couple of people who wrote reviews. Hope this helps.
    Wendell

Page 1 of 2 12 LastLast

Posting Permissions

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