List Boxes

[SG]

I have a form with free list boxes.

The first list box is populated on the form load event with room conditions.

What I want to happen now is if user clicks on the room condition in list0
it will display the corresponding rooms types which have the selected room
condition, then the user selects the room type and the corresponding room
classes are displayed. FInally when a room class is selected the
corresponing room numbers are displayed.

The cose I have behind the afterupdate event of list0 is

With Me.List2
.RowSource = _
"SELECT DISTINCT tblRoomCondition.RoomCondition, tblRoomType.RoomType" & _
"FROM tblRoomCondition INNER JOIN (tblRoomType INNER JOIN tblRooms ON
tblRoomType.RoomTypeID = tblRooms.RoomTypeID) ON
tblRoomCondition.ConditionID = tblRooms.ConditionID" & _
"Where tblRoomcondition.Condition = " & Chr(34) & Me.List0 & Chr(34)
.Requery
End With
Me.Label3.Caption = Me.List2.ListCount & _
" Rooms " & _
Me.List0
End Sub


tbl room contains the individual rooms configuration but instead of
containing Excellent as a room condition it has a value of number 1 which is
a primary key from tblroomcondition. The same goes for room type, room
class.

Any help would be gratefully recieved.

Thank you

S

 

[Guest]

Hello "SG".

First: Name you controls appropriately:
lstRoomcondition, lstRoomtype, lstRoomclass, lstRoomnumbers

I suggest to base the listboxes on a query that selects records according to
the previous listbox:
For lstRoomtype:
Select RoomTypeID, RoomType From tblRoomType Where ConditionID =
[Forms]![NameOfTheForm]!lstRoomcondition
and so on.
I'd always select the key column (the bound one) as the first column and set
the ColumnWidths propety to 0. This will allow the descriptive text
(RoomType) to use all the rest of the width of the listbox (whose ListWidth
should be set to 0 = Automatic).
Then, in the AfterUpdate event for such a listbox, requery the rest of the
listboxes (not the previous ones).
I like the label thing.