Limit 2nd pulldown list - dependant upon 1st pulldown choice 1

[w860098]

I have a Form with 2 pulldown lists. I wish to limit the output from the 2nd list, dependant upon the selection made from the 1st list (there is a one-to-many dependence between the 1st data item and the 2nd data item, where both items are present within the single table from which the form has been generated).
How can I best achieve that ?

 

[PHV]

I'd dynamically set the RowSource property of the 2nd list in the AfterUpdate event procedure of the 1st list.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[w860098]

I am not too sure what form of coding I need to enter for the AfterUpdate event. Presumably I need to use/pass a parameter to identify which row has been selected. How ?

 

[willir]

w860098
I am surprized that you did not post this problem in...
Microsoft: Access Forms Forum

I believe what you are attempting to do is called "Cascading combo boxes"...

There is an FAQ on the topic...
Combo Box - dependant on another Combo Box

You have not provided much in the way of specifics, so I will improvise...

Suppose you want to list students by grade. You select a grade in the first list box or comb box and the second list box or combo box displays the students in the selected grade.

Assumptions...

tblStudent
StudentID - primary key
StudentName
StudentGradeCode - foreign key to tblGrade

tblGrade
GradeCode - primary key

cmbGrade - combo box based tblGrade, RowSource is
SELECT GradeCode FROM tblGrade ORDER BY GradeCode

cmbStudent - combo box based on tblStudent. Initial RowSource is...
SELECT StudentID, StudentName FROM tblStudent ORDER BY StudentName

The end-user wants to search only for the Grade 6 students.
They select "Grade 6" from cmbGrade. This fires off an EventProdcedure AfterUpdate which dynamically tweaks the RowSource for cmbStudent.

How? The form is open in design mode, and the properties window is open ("View" -> "Properties") Select cmbGrade combo box, click on the "Events" tab, select the "AfterUpdate" field, select "[Event Procedures]" from the pick list, and then click on the "..." command button that appears to the right. This will take you to the VBA coding window.

Dim strSQL as String, strQ as String

strSQL = "SELECT StudentID, StudentName FROM tblStudent"
strQ = Chr$(34)    'Double quote character

If Len(Nz(cmbGrade, "")) Then
   strSQL = strSQL & " WHERE StudentGrade = " & strQ & Me.cmbGrade & strQ
End If

strSQL = strSQL & " ORDER BY StudentName"

Me.cmbStudent.RowSource = strSQL
Me.cmbStudent.Requery

The resulting SQL statement for the above example is...

SELECT StudentID, StudentName FROM tblStudent
WHERE StudentGrade = "Grade 6"
ORDER BY StudentName

If GradeCode is numeric, the WHERE clause would be...

If (Nz(cmbGrade, 0)) Then
   strSQL = strSQL & " WHERE StudentGrade = " &  Me.cmbGrade
End If

Hope you can apply this example to your own situation.

Richard

 

[w860098]

You are correct in pointing out that I should have really raised this issue on the other forum.
I have now got both alternative solutions working; interesting variations !