Is it possible to restrict a drop down menu based on results in a different drop down

[wedd]

Is it possible to create a drop down menu box (Locations) that when a specific selection is chosen, it is either not able or able to be selected as an option based on a separate drop down box(Rooms and Venues), which has a list of items that is related to what is selected in the first drop down box (Locations)? Is it possible to either restrict or limit or validate the choses or selections that are chosen based on the options in the first drop down box (Locations) that are set to be matched up to the options in the second drop down menu (Rooms and Venues). Is this possible to do using either vba, query etc? If so, how can this be done? Do you have any examples were this has be done before?



Thanks for your contributions:friends:

[hansup]

See whether this link from msdn is useful to you.

Synchronizing Combo Boxes on Forms in Access 2007 (http://msdn.microsoft.com/en-us/bb404901.aspx)

[wedd]

Hansup, it's very useful info. Thanks! However, it explains how to create 2 combo boxes from 2 different tables. I have 2 colums in the same table one called location and the other rooms; of which I will like to create one combo box called location and the other rooms. And based on the lists that match restrict the user's selection. Is it possible to do this? Even simply using a query or combo box wizard or possibly vba? Thanks!

[hansup]

It doesn't matter whether the row sources for the two combos are based on the same or different tables. Either way, use the After Update event of the first combo to modify the RowSource property of the second combo.

Private Sub cboLocations_AfterUpdate()
Dim strSql As String
strSql = "SELECT room_id FROM YourTable WHERE location_id =" & _
Me.cboLocations & " ORDER BY room_id;"
'Debug.Print strSql
Me.cboRooms.RowSource = strSql
End Sub

Changing the RowSource property of the rooms combo will automatically update its contents (without the need for a separate Me.cboRooms.Requery step).

[wedd]

Hansup, I tried the code but nothing appears in the cboRooms list or cbo cbolocations list...do I have to write something in the cbo RowSourceProperty for the list of data to appear in either combo boxes? Thanks!

[hansup]

Yes put SELECT statements in each of those RowSource properties.

[wedd]

Ok, Thanks! Hopefully it should work this time.