how to make one combox depend on another combox

[msand]

My question is:

I have two combox: 1 & 2. I want the content of combox 2 depends on the selection of the contents in combox1. So far, i have put two queries in the rowsource for two combox. However, i found when i pick one item in combox 1, the contents in combox2 don't automatically updated although when you go back to check the queriy result, the query do bring back the correct result. I wonder if i should add an event or something to make automatic update happen. I would really appreciate your guidance.

Thanks

 

[msand]

This problem was solved: here is the solution from another website.

An easy way to do this would be to assign a dynamic SQL statment to the RowSource of the secondary combo/list box at runtime.

Let's say you have two comboboxes, cbxCombo1 and cbxCombo2. The RowSourceType of cbxCombo1 is set to "Field List" and RowSource to a table Category. cbxCombo2 doesn't have anything under RowSource.

In this case, you can put code in the AfterUpdate event of cbxCombo1 that assigns the proper RowSource to cbxCombo2.

'**************** Code Start *************
Private Sub cbxCombo1_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me!cbxCombo1
strSQL = strSQL & " from Categories"
Me!cbxCombo2.RowSourceType = "Table/Query"
Me!cbxCombo2.RowSource = strSQL
End Sub
'**************** Code End *************
To filter records in a combo/listbox based on the value selected in another combo/listbox, you can use a stored query which uses the first control's value as a parameter. For example,

Select PeopleID, PeopleName from tblPeople Where PeopleID = Forms!FormName!NameOfFirstControl;

Then all you need to do is issue a Requery on the second combo/listbox in this first control's AfterUpdate event.

Private Sub NameOfFirstControl_AfterUpdate()
Me!NameOfSecondControl.Requery
End Sub

 

[NitherWise]

Microsoft also has an article on how to do this.

http://support.microsoft.com/default.aspx?scid=kb;en-us;209576