vba - cascading combo boxes in a datasheet

[pteare]

hello,

I've a it of a head scratcher here. I've been going at it for a good while without avail!

I have a bookings database for ski chalets. There is a table tblBedSpace, and there are 4 fields being ID, chalet, roomnumber and bednumber. The idea is each guest gets placed in, for example, chalet bruyeres room 1 bed 2. Having each bedspace have a unique ID helps me in my systems to make sure that i don't double book any spaces.

I'm trying to create my main bookings form. A booking has a group of people all booked into different bedspaces

frmBookings has a subform called sfrmqryGuestBookings (which contains info on on the guests within that booking)

In this subform I have a field for the guestname (linked to a separate table called tblGuest) and a field for the bedspace.

I have put 2 unbound combo boxes on the subform, first to select chalet, next to select room number and these two put a WHERE clause into the SQL rowsource for the bedspace field so only the available beds are there to pick from (to save me scrolling through every possible bed in resort)

I've also put in some code so that the on current event of the subform removes the WHERE clause the bedspace back to having all bedspaces available. This makes all the bedspaces show (otherwise ones that are not in the current chalet don't show up)

Finally I've put some dlookups so that the unbound combo box shows the chalet name for that bedspace, again on the on current event for the subform.

It all works great when its viewed as a form. I want to view it as a datasheet, as then I can see all the guests in th booking at once.

The unbound combo for the chalet is repeated on each line for each guest booking. They all say the same thing, I guess as the are effectively the same box. I'd like them to show individually which chalet each guest is in.

My code so far:

Private Sub form_current()
bedspace.RowSource = "SELECT tblBedSpace.ID, [tblBedSpace].bednumber & ' (in ' & [tblChalet].chaletname & ' room ' & [tblBedSpace].roomnumber & ')' " & _
                          "FROM tblChalet INNER JOIN tblBedSpace ON tblChalet.ID = tblBedSpace.chalet " & _
                          "ORDER BY tblBedSpace.bednumber;"
cboRoomNumber = DLookup("roomnumber", "tblBedSpace", "[ID] = bedspace")
cboChalet = DLookup("chalet", "tblBedSpace", "[ID] = bedspace")
End Sub

Private Sub cboChalet_AfterUpdate()
cboRoomNumber.RowSource = "SELECT DISTINCT tblBedSpace.roomnumber " & _
                          "FROM tblBedSpace " & _
                          "WHERE (((tblBedSpace.chalet) = " & Me.cboChalet & ")) " & _
                          "ORDER BY tblBedSpace.roomnumber;"
End Sub
Private Sub cboRoomNumber_AfterUpdate()
bedspace.RowSource = "SELECT tblBedSpace.ID, [tblBedSpace].bednumber & ' (in ' & [tblChalet].chaletname & ' room ' & [tblBedSpace].roomnumber & ')' " & _
                          "FROM tblChalet INNER JOIN tblBedSpace ON tblChalet.ID = tblBedSpace.chalet " & _
                          "WHERE (((tblChalet.ID) = " & Me.cboChalet & ") And ((tblBedSpace.roomnumber) = " & Me.cboRoomNumber & ")) " & _
                          "ORDER BY tblBedSpace.bednumber;"

End Sub

Any ideas? I've been going some serious head scratching. I've been looking into looping through each record but this does not get away from the fact that in effect I have a column of the same unbound combo box.

All ideas on directions I should look into would be greatly appreciated! I'm pretty stuck at the moment. I've new to this VBA so apologies if it's a ally simple thing!

Many thanks,

Phil.

 

[stopher]

The unbound combo for the chalet is repeated on each line for each guest booking. They all say the same thing, I guess as the are effectively the same box. I'd like them to show individually which chalet each guest is in.

Why are you using an unbound control? Surely when you make a booking you have to store the roomnumber and bednumber for each guest?

Chris

 

[pteare]

hello,

thanks for the post. I have a table of each available bedspace, which has a column for chalet, for room number and for bed number. This means each bedspace has a unique ID, which is linked to from a field in the guestbooking table. I thought that would be a good way as you then are linking to the ID for each bedspace. My concern was, let's say I have 3 column in my guest booking, on for chalet, one for room number and one for bed number. I was worried incase a mistake was made so that you put, for example, someone in room 7 in a chalet that only has 5 rooms.

Also I thought this would make for a systems that's easy to update. lets say I add another chalet, then all I would do is add it's details to the bedspaces table and then all the right bedspaces are available. If I code in that, say for chalet 1 only allow rooms 1-5, for chalet 2 only allow rooms 1-8 etc, then it might be harder for me / others to all new chalets to the system.

These are just my ideas, please feel free to correct me if you think this is a silly way to do things!

Many thanks,

Phil.