Room assignment help (Access 2000/SP2)

Topic

New Reply

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.

Reply | Quote

Replies

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.

Reply | Quote

Tom: Now, I’m hollering.

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?

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

THANK YOU, THANK YOU, THANK YOU!

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!

Reply | Quote

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:

Me!cboSelectRoom.RowSource = “SELECT [Rooms].[RoomID], [Rooms].[RoomNumber] ” & _
“FROM [Rooms] WHERE [CabinID] = ” & Me!ID

(2) Requery the combo control:

Me!cboSelectRoom.Requery)

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

Me!cboSelectRoom = CurrentDb.CreateQueryDef(“”, _
Me!cboSelectRoom.RowSource).OpenRecordset!RoomID

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

(4) Requery the Bed subform:

Me!subformBeds.Requery

(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. cboSelectRoom, 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…

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

Would love to post the database and have you help me make it snazzy -looking. Unfortunately, you are right, it does contain sensitive data.

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

Reply | Quote

You might try having the lower level forms as datasheets. You can nest these.

Reply | Quote

Wanted to let you guys know that I’m still alive and kicking…I had to put this project on hold for a while. I tried John Hutchison’s suggestion of using query as a basis, it came extremely close to what I needed. I need to assess what is wrong with the suggestion and get back to you.

Brent

Reply | Quote

John – Your suggestion worked, I got it very close to what I needed, however, there is a minor detail that needs to be worked out and I don’t know if it is possible or not. Instead of listing ALL rooms, I’d like to change the Room subform from Datasheet to a combo box that would show me only the rooms that belong to the Cabin I choose and show those rooms as a datasheet. My question: How do I tell the darn combo box to look up rooms based on the selection I make in cboCabin?

Tom: Your suggestion came extremely close to what John suggested too but I felt that John’s was much more easier to do but, THANKS for your help! I appreciate it.

Brent

Reply | Quote

Brent

I have read this, and I am not clear about a few things.
Does the subform only show the rooms that belong to the current cabin? I would expect it to. Have you set the linking field? (if you used the form wiard it will have been set automatically).
I don’t understand what you mean by : “change the Room subform from Datasheet to a combo box.”

A combo box is a control on a form, a datasheet is a style of presentation for a subform. You can’t change one into the other.

I haven’t time at the moment, but I will try to post an example of what I meant in my previous post.

Reply | Quote

When I followed your directions, the Form Wizard set up two subforms, both in Datasheet views. I didn’t want the Room subform to be in a datasheet form because it wasn’t too inuitive (You would need to click on the room you wanted to force the Bed subform to change) and I wanted to change the Room subform to Single Form, then change the field to a Combo box. When I did that, I suddenly lost “filter” (For lack of a better word) that was applied to the Room Subform whenever I changed Cabins in the cboCabin. Hence, the new topic that I created. I wanted to force the Room subform to list rooms that belong only to the cabin I choose via a combo box, not a datasheet.

Basically, I wanted what the Form Wizard was able to do: Change the Room subform to show rooms specific to the cabin I choose in the main form, but show the rooms as a combo box instead of a datasheet.

Brent

Reply | Quote

You may not need this anymore, but I have had a go at doing what I think you want, and I enclose it.

there are two solutions: a cabins form bound to the cabins table, with a room subform. In this one, the linking fields do the job of ensuring that only relevant rooms/beds re display.
the other form is unbound, and uses a combo box. In this case the query/ requery ensure that only relevant rooms/beds are displayed.

Reply | Quote

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.

Reply | Quote

The allocation will be indefinite. The previous poster (I’m sorry, I forgot your name. ) 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.

Reply | Quote