Use data in one combo box to populate other

[BSJoJaMAx4]

Hi all



I have a table (named locations) as follows:

Building	Room
Building A	Smith
Building B	Yarra

Building A	Grand
Building A	Elite
Building C	Small

I want the user to select the building first from a combo box, but then only allow the user to select the rooms relating to the building (ie select A and get Smith, Grand, Elite) from a combo box.

In building combo box in row source I have

SELECT DISTINCT locations.Building FROM locations;

which works (unless I need to change for the room combo box query).
Code I have issue with is for the room.
In Room combo box in row source I have

SELECT locations.room FROM locations WHERE locations.building = building ORDER BY locations.room;

Thanks in advance

[pbaldy]

Is this what you're looking for?

http://www.baldyweb.com/CascadingCombo.htm

[BSJoJaMAx4]

Thanks Paul - exactly what I am after. I have had a look at the sample and tried to adapt it to my file. Its kind of working but not pulling the room only data for the sites.

Table is called bay with building (text) and bay (text)

For the building combo I have as follows:

Control Source: BuildingNew
Name: buildingcheck
Row Source:

SELECT bay.building FROM bay GROUP BY bay.building;

in after update

Private Sub BuildingNew_AfterUpdate()    Me.Bay = vbNullString
    Me.Bay.Requery
End Sub

For the room data

Control Source: Bay
Name: Bayconfirmation
Row Source:

SELECT Bay.Bay FROM Bay WHERE (((bay.building)=Forms![Parking Bookings]!Buildingnew)) ORDER BY [bay].[bay];

[BSJoJaMAx4]

Actually, on second inspection it appears to be working. What I was doing was selecting a building, then going to the room (which works first time). When I try to select another building it still displays the rooms from the other building. How do I reset it each time?

[pbaldy]

The requery should update the selections. It doesn't look like you're using the name of the combo in the requery though. Change to that and see how it works.

[BSJoJaMAx4]

Thanks Paul - I dont understand what i did but fixing that up did the trick. Appreciate the help.

[pbaldy]

No problem.