Filter Combo box based on check box selection

[BJS]

I have 4 checkboxes a user can check.

If checkbox 1 is checked, I would like the combo box on my form to be filtered so that only items that start with the letter "A" will appear in the combo box for selection.

If checkbox 2 is checked, all items except those that start with the letter "A" will appear in the combo box for selection.

I've been playing with an If statement in the criteria of the query for the combo box....no luck so far. I'm sure this is quite simple.

Any ideas anyone?....thanks!
BJS

 

[Fizzio]

Try this, type these into the field boxes in the query

Iif([Forms]![formName]![Checkbox1] = True, [FieldtoFilter] like "A*", Null)

Iif([Forms]![formName]![Checkbox2] = True, [FieldtoFilter] not like "A*", Null)

I'm not sure about the null bit though.

on the onChange event of the checkboxes, you will need to requery the combo box ie

Sub Checkbox1_OnChange()
Me.NameOfCombo.Requery
End Sub

hth

 

[BJS]

Fizzio,

I have tried implementing your suggestion. It is not working for me. There is no OnChange event for the unbound checkbox, so I am requerying the combo box on the OnClick Event.

I have put the IIf statements in the criteria of the particular field, but now my combo box shows no data, regardless of which checkbox is checked (true).

Any idea what is going on???

 

[BJS]

Still can't get the Iif statement to work within the query, so instead I I put the following code on the GotFocus Event of the combo box to change the row source of the combo box:

If Me.Checkbox1 = True Then
cboComboBox.RowSource = "SELECT DISTINCTROW [TableName].*, [TableName].[FieldName] FROM [TableName] WHERE ((([TableName].[FieldName]) Like 'Z*'));"

ElseIf Me.Checkbox2 = True Then
cboComboBox.RowSource = "SELECT DISTINCTROW [TableName].*, [TableName].[FieldName] FROM [TableName] WHERE ((([TableName].[FieldName]) Not Like 'Z*'));"

End If

This works great....but I still wouldn't mind knowing what I was doing wrong the other way (putting the iif statement in the query).

Thanks for the help!