Hi all, I have a form with a subform where there are combo boxes at the top to select one or more values and then filter the records returned on the subform based on the selected values. It uses the same logic/code as the demo in this UA post: Multi Value Fields (big thanks to theDBGuy for pointing me in the right direction).
The form and subform are fully designed and show all data by default (which is good). Then I started to slowly add code for one combo box on the After Update event but it doesn't filter the results. It also asks for a parameter when I change the value. Here are the details:
The main form has a record source of a temp table with no data (tblResponseQryTmp) and combo boxes to select the filter criteria (i.e. cboSelIndustry). The combo box row source is the table used to store the list of values.
The subform has a record source of the table that has all of the data and foreign keys (tblResponse) and combo boxes to display the results of the query (cboResIndustry). The subform is view only so all of the controls are "enabled = No".
Here is the code on the cboSelIndustry combo box After Update event:
After putting a few debug statements in, the result of strLinkCriteria at the end is "cboResIndustry.Value = 6" and 6 is the ID that corresponds to the Industry value I selected.
I've tried changing the code to use cboSelIndustry.Value as well as the column name from the record source table but nothing seems to work. And now when I change the value of cboSelIndustry on the form, I get a prompt to enter a value for cboResIndustry.Value.
Where did I go wrong?
The form and subform are fully designed and show all data by default (which is good). Then I started to slowly add code for one combo box on the After Update event but it doesn't filter the results. It also asks for a parameter when I change the value. Here are the details:
The main form has a record source of a temp table with no data (tblResponseQryTmp) and combo boxes to select the filter criteria (i.e. cboSelIndustry). The combo box row source is the table used to store the list of values.
The subform has a record source of the table that has all of the data and foreign keys (tblResponse) and combo boxes to display the results of the query (cboResIndustry). The subform is view only so all of the controls are "enabled = No".
Here is the code on the cboSelIndustry combo box After Update event:
Code:
Private Sub cboSelIndustry_AfterUpdate()
Dim strLinkCriteria As String
Dim strData As String
If Me.Dirty Then Me.Dirty = False
strData = Nz(DLookup("SelIndustry", "tblResponseQryTmp", "ID=1"), "")
If strData <> "" Then
strLinkCriteria = "cboResIndustry.Value = " & Replace(strData, ",", " AND cboResIndustry.Value = ") & " AND "
End If
If strLinkCriteria <> "" Then
strLinkCriteria = Left(strLinkCriteria, Len(strLinkCriteria) - 5)
Me.frmQuerySubForm.Form.Filter = strLinkCriteria
Me.frmQuerySubForm.Form.FilterOn = True
End If
End Sub
After putting a few debug statements in, the result of strLinkCriteria at the end is "cboResIndustry.Value = 6" and 6 is the ID that corresponds to the Industry value I selected.
I've tried changing the code to use cboSelIndustry.Value as well as the column name from the record source table but nothing seems to work. And now when I change the value of cboSelIndustry on the form, I get a prompt to enter a value for cboResIndustry.Value.
Where did I go wrong?