Combo box alternate values

A

AshGrant

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.
 
R

Rick Brandt

AshGrant said:
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
 
D

Douglas J. Steele

Rick Brandt said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top