Hi All,
I have a form with a few combo boxes that will allow the user to select values and then a subform will return results that are restricted based on the user's selections (more on that in this thread: https://www.access-programmers.co.u...-filter-not-working-prompts-for-value.310922/). One of the combo boxes is linked to one of the other combo boxes. In this case, the user will select a Regulation Set from one box and then a Regulation from the second box, where the results are filtered based on the Regulation Set selected.
I have done this exact thing on other forms that all work great. The After Update event of the first combo box triggers a requery of the second combo box, and the Row Source for the second one is simply:
However, on the offending form, I am unable to get the second combo box to update. It shows no list of values when I try to set the Row Source in Design Mode as long as I am referencing the value of the first combo box as the criteria. If I remove the criteria, it returns everything. I thought I'd try using VBA to set the Row Source and clear out the value from the Design Mode properties box. When I do that and select the first combo box, Access crashes. Every time. I stripped the Where clause out of the SQL to try and just select the whole value set; it still crashes. Here is the code added to the After Update event for the first combo box:
What is even more strange is that I put a debug statement in to print the value of strSQL to the immediate window, and if I copy/paste that exact value into the Row Source of the combo box in Design Mode, it works - meaning it returns the full value set; getting it to restrict the values will be the next step.
This form is a little different compared to the ones that work; this is only a query form where the users do not add/update/delete data and I want the users to be able to select multiple values from each combo box. On the forms where this works, the user can only select a single value for entry/update.
Any ideas on what I am missing?
I have a form with a few combo boxes that will allow the user to select values and then a subform will return results that are restricted based on the user's selections (more on that in this thread: https://www.access-programmers.co.u...-filter-not-working-prompts-for-value.310922/). One of the combo boxes is linked to one of the other combo boxes. In this case, the user will select a Regulation Set from one box and then a Regulation from the second box, where the results are filtered based on the Regulation Set selected.
I have done this exact thing on other forms that all work great. The After Update event of the first combo box triggers a requery of the second combo box, and the Row Source for the second one is simply:
Code:
SELECT tblRegulation.ID, tblRegulation.RegulationCode
FROM tblRegulation
WHERE (((tblRegulation.RegulationSetID)=[Forms]![frmResponse]![cboRegSet]))
ORDER BY tblRegulation.RegulationCode;
However, on the offending form, I am unable to get the second combo box to update. It shows no list of values when I try to set the Row Source in Design Mode as long as I am referencing the value of the first combo box as the criteria. If I remove the criteria, it returns everything. I thought I'd try using VBA to set the Row Source and clear out the value from the Design Mode properties box. When I do that and select the first combo box, Access crashes. Every time. I stripped the Where clause out of the SQL to try and just select the whole value set; it still crashes. Here is the code added to the After Update event for the first combo box:
Code:
Private Sub cboSelRegSet_AfterUpdate()
Dim strSQL As String
On Error GoTo Query_Error
strSQL = "SELECT tblRegulation.ID, tblRegulation.RegulationCode " _
& "FROM tblRegulation " _
& "ORDER BY tblRegulation.RegulationCode;"
Me.cboSelReg.RowSource = strSQL
'Me.cboSelReg.Requery
ExitNow:
Exit Sub
Query_Error:
MsgBox Err.Description
Resume ExitNow
End Sub
What is even more strange is that I put a debug statement in to print the value of strSQL to the immediate window, and if I copy/paste that exact value into the Row Source of the combo box in Design Mode, it works - meaning it returns the full value set; getting it to restrict the values will be the next step.
This form is a little different compared to the ones that work; this is only a query form where the users do not add/update/delete data and I want the users to be able to select multiple values from each combo box. On the forms where this works, the user can only select a single value for entry/update.
Any ideas on what I am missing?