Combo box alternate values

[Guest]

Hi, guys - Rather simple but baffling question I have here...

I have a booking form for an accomodation database. I want the user to be able
to select the bed number they wish to book a customer into, bearing in mind,
rooms can contain any number of beds between 1 and 8.

For example,
John Smith wishes to book into Room 119, this room has 4 beds in it, the
user wishes to book him into bed No.2
Gary Smith wishes to book into Room 119 also, but this time into Bed No.3

I know I need a combo box for this but how can I deal with the RowSource?
i.e sometimes it will display a list of numbers 1 - 3 and other times it will
display a list of numbers 1 - 8, depending on how many beds are in the
selected room.
Any ideas how I can achieve this? Many thanks!

Ash.

 

[Rick Brandt]

Hi, guys - Rather simple but baffling question I have here...

I have a booking form for an accomodation database. I want the user to be able
to select the bed number they wish to book a customer into, bearing in mind,
rooms can contain any number of beds between 1 and 8.

For example,
John Smith wishes to book into Room 119, this room has 4 beds in it, the
user wishes to book him into bed No.2
Gary Smith wishes to book into Room 119 also, but this time into Bed No.3

I know I need a combo box for this but how can I deal with the RowSource?
i.e sometimes it will display a list of numbers 1 - 3 and other times it will
display a list of numbers 1 - 8, depending on how many beds are in the
selected room.
Any ideas how I can achieve this? Many thanks!

You need a table that holds the data for what beds are in each room (assume you
already have that). Then the fist ComboBox has a RowSource that simply lists
all the rooms....

SELECT RoomNumber FROM Rooms

The second ComboBox will list bed numbers and will have a criteria referencing
back to the first ComboBox...

SELECT BedNumber
FROM Rooms
WHERE RoomNumber = Forms!FormName!RoomNumber

You will need to use code in the AfterUpdate event of the RoomNumber ComboBox
that requeries the BedNumber ComboBox...

Me!ComboBox.Requery

 

[Douglas J. Steele]

You need a table that holds the data for what beds are in each room
(assume you already have that). Then the fist ComboBox has a RowSource
that simply lists all the rooms....

SELECT RoomNumber FROM Rooms

The second ComboBox will list bed numbers and will have a criteria
referencing back to the first ComboBox...

SELECT BedNumber
FROM Rooms
WHERE RoomNumber = Forms!FormName!RoomNumber

You will need to use code in the AfterUpdate event of the RoomNumber
ComboBox that requeries the BedNumber ComboBox...

Me!ComboBox.Requery

Actually, the SQL for the RowSource will probably need to be a little more
complicated than that, to ensure that the second time they look at Room 119,
it only lists beds 1, 2 and 3 (since John Smith already has bed 4)

Ash: If you need help with the SQL, post the details of the tables in your
application.