filter Combo box values based on form Checkbox

[CRobledo]

I have a form with a combobox and a checkbox. I would like the dropdown values of the combobox to be filtered based on whether the checkbox is checked or not. The values are in a table named ChemicalList under the field name PruductName and there is a checkbox next to each name in that table. I would like that if the checkbox is checked in the form then all the records with a checked box next to them be returned to the combo box. if not then return all.

please help

[ranman256]

Make 2 queries, qsAll and qsChecked. The default is qsAll.
When user checks the box, swap out the cbo query.

sub chkBox_afterupdate()
If chkBox.value then
   CboBox.rowsource = "qsChecked"
else
  CboBox.rowsource = "qsAll"
End if
end sub

[Micron]

Maybe someone knows how to do this with a query, but I don't see a way to totally eliminate query criteria in one case, but use it in another by writing some sort of IIF expression. So my solution would be a coded one, on the combo Enter event. In my experience, the Click event, while logical, is flaky. So it would be something like

If Me.chkMyCheck = False Then
Me.cmbMyCombo.RowSource = "SELECT ChemicalList.PruductName FROM ChemicalList"
Else
Me.cmbMyCombo.RowSource = "SELECT ChemicalList.PruductName FROM ChemicalList WHERE ChemicalList.myCheckedField = True"
End If

Obviously you have to substitute your own field and control names.

[CRobledo]

thanks! that worked great!! I added multiple cboboxes what syntax do i need to use to add more to the code you gave me?

[Micron]

you had 2 proposals, so to whom are you referring? If me, then you need one for each checkbox, or if there's more than say 5 or 6 and they're all doing the same thing, I might create a function and pass parameters to it. Certainly the checkbox name for one, but you'd probably need to pass something that told it what sql to create. That function could then check for the second parameter value and create and return the appropriate sql. Kind of like
strSql = CreateSqlControlName, "ChemList" (where ControlName is the actual checkbox control object and ChemList denotes a sql for chemical list)
then the function processes based on those 2 parameters
Public Function CreateSql (ctl As Control, Flag As String)
'do stuff
End Function
I just wasted a few precious minutes if you weren't referring to me!