DropDown Box(es) linked? filtered?

[Steve Stad]

Can you tell me how to set up a drop down combo box (e.g., dropdowncombobox2)
based on a filtered set of data where the filtered field is a previous drop
down box1. For example I have 204 titles in 14 Categories. Dropdown 1
populates category field with 1 of 14 categories. I need a dropdown for
titles field that is filtered by the category selected in Dropdown1 for
Category field. Let me know if you need further explanation.

 

[Dirk Goldgar]

Can you tell me how to set up a drop down combo box (e.g.,
dropdowncombobox2)
based on a filtered set of data where the filtered field is a previous
drop
down box1. For example I have 204 titles in 14 Categories. Dropdown 1
populates category field with 1 of 14 categories. I need a dropdown for
titles field that is filtered by the category selected in Dropdown1 for
Category field. Let me know if you need further explanation.

This page:

http://www.mvps.org/access/forms/frm0028.htm

.... shows you two methods:

1. Have the RowSource of the second combo box refer explicitly to the first,
and requery the second combo box in the AfterUpdate event of the first,

or

2. In the AfterUpdate event of the first combo box, completely rewrite the
RowSource SQL of the second combo box.

 

[Steve Stad]

Dick - I am getting error msg w/ the code below for two combo boxes saying
'Syntax error (missing operator) in qry expression '108from tbl_std_Category'
I have 2 combo boxes, and two tables.
tbl_Std_Category has 2 fields ID, and EMP_STANDARD_LAB_CAT.
tbl_Std_Duty_Title -- has 2 fields - ID, and EMP_STANDARDIZED_DUTY_TITLE
108 is an id in both tables. Any help is appreciated.


Private Sub Combo8_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me.Combo8
strSQL = strSQL & "from tbl_Std_Category"
Me!Combo10.RowSource = "Table/Query"
Me!Combo10.RowSource = strSQL
Me.Combo10.Requery
End Sub

 

[Dirk Goldgar]

Dick - I am getting error msg w/ the code below for two combo boxes saying
'Syntax error (missing operator) in qry expression '108from
tbl_std_Category'
I have 2 combo boxes, and two tables.
tbl_Std_Category has 2 fields ID, and EMP_STANDARD_LAB_CAT.
tbl_Std_Duty_Title -- has 2 fields - ID, and EMP_STANDARDIZED_DUTY_TITLE
108 is an id in both tables. Any help is appreciated.


Private Sub Combo8_AfterUpdate()
Dim strSQL As String
strSQL = "Select " & Me.Combo8
strSQL = strSQL & "from tbl_Std_Category"
Me!Combo10.RowSource = "Table/Query"
Me!Combo10.RowSource = strSQL
Me.Combo10.Requery
End Sub

What you've told me about your table and form design doesn't altogether make
sense to me, so I can't be too specific. Your code needs to do something
like this:

'----- start of *rough* example code -----
Private Sub Combo8_AfterUpdate()

Dim strSQL As String

strSQL = _
"SELECT ID, EMP_STANDARD_LAB_CAT " & _
"FROM tbl_Std_Category " & _
"WHERE ID = " & Me.Combo8

Me.Combo10.RowSource = strSQL

End Sub
'----- end of *rough* example code -----

However, it isn't clear to me which combo is which, what table each combo is
based on, and how the tables are related. Normally a field name "ID" in a
table would be the primary key of that table, and you wouldn't be relating
two tables in a one-to-many relationship on a field named ID in both tables.
You said:

tbl_Std_Category has 2 fields ID, and EMP_STANDARD_LAB_CAT.
tbl_Std_Duty_Title -- has 2 fields - ID, and EMP_STANDARDIZED_DUTY_TITLE

That doesn't really give us any field to serve as the foreign key. Is that
really all the fields in these tables? What type of field is ID, in each
table? How are the tables related, and what do they represent? What is the
RowSource of Combo8?

Incidentally, it's a good idea to give your controls more meaningful names
than the things like "Combo8", "Combo10", etc., that Access often assigns.
Meaningful names make for better understanding when someone has to debug the
code.