limit combo box based on whats selected in another combo box

bobby213

New member
Local time
Today, 18:54
Joined
May 25, 2000
Messages
5
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!
 
Combo Box 1: Create a query which the user can make a selection.
Combo Box 2: Create a query that links on the many to one side, with the many being the record source for the second combo box. In the criteria field for the one side, place the following:
[Forms]![FormName]![ComboBox1Name]

In the On Enter of your second combo box, place the following Event Procedure:

Private Sub ComboBox2Name_Enter()
On Error GoTo ComboBox2Name_Enter_Err

DoCmd.Requery "ComboBox2Name"


ComboBox2Name_Enter_Exit:
Exit Sub

ComboBox2Name_Enter_Err:
MsgBox Error$
Resume ComboBox2Name_Enter_Exit

End Sub

Good Luck
 

Users who are viewing this thread

Back
Top Bottom