Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Room assignment help (Access 2000/SP2)

    I need some guidance on how I should set up tables and form to do what I want...

    I have to set up a form that has a combo box that lists 10 different cabins and when I choose a cabin (Inn for example), it would then automatically bring up a subform with fixed rooms with available beds in each room.. For example, Inn has 10 rooms, and each room can hold 2 people. I want to see a form that shows 10 rooms with two slots each for name entry purposes. If I change it to Inn Annex which has only 5 rooms, but has 4 beds in each room, then when I select Inn Annex, then form would show only 5 rooms with 4 slots each.

    This leads me to yet another question. How in the heck do I set up the tables? Hope that this is not too complicated to discuss on this forum.

    Thanks in advance.

  2. #2
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    Here's a brief outline of one approach to your problem:

    Create a table that describes each of the cabins (one cabin per record). Create a second "room" table that describes each of the rooms (one room per record) that includes a reference to the inn that the room belongs to. (Note that all rooms for all cabins are contained in one table.) Create a third "bed" table that describes each bed (one bed per record) that includes a reference to the room that the bed belongs to. (Likewise, not that all beds for all rooms and cabins are contained in one table). (You could take this one step further -- ad nauseum perhaps -- to capture "berths" per bed to handle double vs. single beds, etc.). Only at the "lowest" level do you need a field for the name of the person residing in that bed/berth. Set up a one-to-many relationship between the cabin table and the room table and then between the room table and the bed table.

    For the forms, set up a room form that has a bed subform. Then use the room form as a subform on the cabin form. Set up the Link Child Fields and Link Master fields for your subforms to match the relationships above, and you should be ready to rock and roll.

    I've left out the details, but hopefully this will get you started. If you need more details, holler.

  3. #3
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Room assignment help (Access 2000/SP2)

    When you allocate a person to a bed in a room, is it an indefinite allocation, or is it for a specific period of time.

    To put it another way, do you want the form to tell you whether the bed is vacant, or do you want to know if it is vacant on a specific date?

    If you need the dates, it is more complicated because each bed can have multiple bookings, and so you need a bookings table with dates attached to each.
    Regards
    John



  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    The allocation will be indefinite. The previous poster (I'm sorry, I forgot your name. <img src=/S/confused.gif border=0 alt=confused width=15 height=20> ) gave me the solution I needed. I actually was halfway through but had problems with the form, so I gave up. Now, I know I should have used subforms instead.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    Tom: Now, I'm hollering. <img src=/S/bwaaah.gif border=0 alt=bwaaah width=123 height=15>

    I decided to start over and use your suggestion because I had tried a different approach that apparently doesn't work. So far, I've created tlkpCabin which contains names of all cabins on the campground; tlkpRoom which contains CabinID FK and room number. However, this is where I really ran into a roadblock! In your message, you said:

    "Create a third "bed" table that describes each bed (one bed per record) that includes a reference to the room that the bed belongs to. (Likewise, not that all beds for all rooms and cabins are contained in one table)." and later on: "Only at the "lowest" level do you need a field for the name of the person residing in that bed/berth. Set up a one-to-many relationship between the cabin table and the room table and then between the room table and the bed table."

    So far, I have done with the Bed table is: BedID (AutoNumber, Primary Key), RoomID (Number Format), Bed (Text format, each "bed" is labeled as A or B because each room can have one or more beds in a room) and Name (Text format, for names of person who is assigned to that specific bed). However, I don't SEE how the database would know that that room belongs to a specific cabin?

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Room assignment help (Access 2000/SP2)

    Each room record contains a CabinID FK which identifies that a room belongs to a cabin.

    Each bed record contains a roomID FK which identifies which room it belongs to.

    To idenitfy which cabin a bed belongs to follow the chain from bed to room to cabin.

    If you build a query linking all three tables, you will see that the database can follow this chain to identify that a bed is in a specific cabin.
    Regards
    John



  7. #7
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    Once you have your tables linked as John describes, then set up your cabin form, room subform, and bed sub-subform with similar links using the Link Master and Link Child fields (one pair of these links between the cabin form and room subform and another pair between the room subform and bed sub-subform). Once you have these set up, it really works quite nicely. You'll have to play around with the cosmetics (continuous forms versus single forms, whether you want to be able to add records (cabins, rooms, and or beds) or just change existing ones (who's in which bed) or disable editing (e.g., cabins and beds are fixed) for the cabin form as well as the room sub form and bed sub-subform, etc., etc. -- whatever works best for your needs).

    One really nice advantage of setting things up this way is that if you add rooms or beds via these forms, the links will be established automatically based on the current record in the parent form (e.g., select the cabin you want to add a room to, then add a record in the subform and the foreign key reference will be assigned automatically -- it doesn't have to be (and shouldn't be!) a field on your subform). You don't have to mess around with that stuff yourself (which would be the case if you were adding cabins, rooms, and/or beds directly via the tables themselves).

    Hope this helps. I'd like to hear how things work out.

  8. #8
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> THANK YOU, THANK YOU, THANK YOU! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

    John and Tom, the suggestions you both gave me worked! I totally misled you yesterday on the relationships. I looked at the relationships today and it actually made sense, right up to the point where I started to populate the tblBed (I changed the name of the table), that was where I didn't understand the relationships until I read John's explaination, D'OH! I didn't "see" the relationship inside the tblBed with the RoomID and I realized, that was EXACTLY what John meant! Grin.

    Anyway, Tom, your suggestion worked perfectly. I am using Continous forms to show all "beds" for a specific room but I have two minor problems that I'm hoping you could help me out:

    1) Access 2000 doesn't allow me to create a continous form if there is a subform embedded in it. So, my fsubRoom has fsubBed (continous form), therefore, fsubRoom can't be continous form either. That's fine. I decided to use a Combobox to look up the room number I want which will then show me the beds in that specific room. Is there a better workaround than this?

    2) I haven't figured it out yet, hoping you could. When I set up the combobox in Rooms subform to "look up" rooms, it shows me ALL rooms in the table! I would like to tell the combobox to show me rooms ONLY for that specific cabin I choose. Because, right now, I have 5 different cabins with identical room numbers (1, 2, 3...) and it "repeats" itself (1, 2, 3, 1, 2, 3) and it is extremely difficult for me or user to figure out which room that belongs to. I'd like to filter it out so that it would only show rooms based on which cabin I choose.

    Thanks so much to you both, John and Tom! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  9. #9
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    Brent,

    1. Good point. You're right -- only the "innermost" form can be the continuous form type.

    2. As with most things in Access there are seventy-eleven ways this could be done. Here's one approach you could take: If you want to have a "Select Room" combo control (sounds like a good idea once you have all your rooms created), then I'd suggest putting the combo control and the bed subform directly on the Cabin form (do away with the room subform). Then, in the Cabin's OnCurrent event do several things:

    (1) Redefine the RowSource property for the combo control to select only those rooms with a CabinID matching the current Cabin Record (changing the WHERE clause); something like:

    <font face="Georgia">Me!cboSelectRoom.RowSource = "SELECT [Rooms].[RoomID], [Rooms].[RoomNumber] " & _
    "FROM [Rooms] WHERE [CabinID] = " & Me!ID</font face=georgia>

    (2) Requery the combo control:

    <font face="Georgia">Me!cboSelectRoom.Requery</font face=georgia>)

    (3) Initialize the combo control to first item in the list; something like:

    <font face="Georgia">Me!cboSelectRoom = CurrentDb.CreateQueryDef("", _
    Me!cboSelectRoom.RowSource).OpenRecordset!RoomID</font face=georgia>

    (Is there an easier way to do this? Anyone?)

    (4) Requery the Bed subform:

    <font face="Georgia">Me!subformBeds.Requery</font face=georgia>

    (Again, put all the above code in the OnCurrent event for the Cabin form.)

    Finally, make sure the Bed subform's Link Master Fields property references the "Select Room" combo control (type that control's name, e.g. <font face="Georgia">cboSelectRoom</font face=georgia>, as the Link Master Fields property's value). This way, when you make a selection from the combo control, the subform will requery and display that room's beds (you don't have to code anything into the combo control's "AfterUpdate" event).

    Hope this helps. Should be close to what you're after...

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Room assignment help (Access 2000/SP2)

    As you found out, you can't have a continuous subform inside a continuous subform, but inside a form that showed a cabin, you could have a single continuous subform showing both rooms and beds. And it is easy to make as well.

    Create a query pulling in all fields from all three tables, and set it to sort by roomID then by bedID. Next build a form from that query using the form wizard. Access will ask how you want to view the data, so ask to view it by cabin, and select the subform option. You will then see all the rooms and beds for each cabin.
    Regards
    John



  11. #11
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Room assignment help (Access 2000/SP2)

    If theres no sensitive data, why don't you post the database and we can fiddle around with it as I'm sure we can come up with something quite snazzy !!
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  12. #12
    4 Star Lounger
    Join Date
    Feb 2001
    Location
    Richland, Washington, USA
    Posts
    407
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    My opinion only, Pat, but I think it's more educational (and gratifying in the end) for Brent to wrestle with this himself. Of course, I'm as guilty as the next guy in providing actual code as hints, although he's going to have to do some converting to get it to fit his tables, forms, etc.

  13. #13
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Room assignment help (Access 2000/SP2)

    Well I guess it all depends on how long Brent is willing to grapple with it.
    Over to you Brent.
    Always willing to help.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  14. #14
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Seattle, Washington, USA
    Posts
    179
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Room assignment help (Access 2000/SP2)

    Would love to post the database and have you help me make it snazzy -looking. Unfortunately, you are right, it does contain sensitive data. <img src=/S/bummer.gif border=0 alt=bummer width=15 height=15>

    But, do keep those suggestions rolling in. I'm now going to try out John's suggestion and keep you all updated on the progress. I still also have Tom's if John's doesn't work...

    Brent

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Room assignment help (Access 2000/SP2)

    You might try having the lower level forms as datasheets. You can nest these.
    David Grugeon
    Brisbane Australia

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
  •