I found this information from http://home.att.net/~dashish/forms/frm0028.htm
If anyone can explain it to me a little better id be very grateful as I don't really use SQl at all, I want the value from cboProjectName to limit whats in cbosub(2nd column)
thanks for the time,
bobby
(Q) How can I limit the contents of one combo/list box based on what's selected in another combo/list box?
(A) 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
--------
If you read all the way down here thanks!
If anyone can explain it to me a little better id be very grateful as I don't really use SQl at all, I want the value from cboProjectName to limit whats in cbosub(2nd column)
thanks for the time,
bobby
(Q) How can I limit the contents of one combo/list box based on what's selected in another combo/list box?
(A) 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
--------
If you read all the way down here thanks!