Continious form. Small checkbox filter oddness...

[NBRJ]

I have a continious form (fStaff, based off tStaff) that has the following fields:

• IDStaff (autonumber)
• Firstname
• Surname
• FIDRole (FK to tRole.IDRole), cboFIDRole
• Ex (Yes/No), CheckEx

In the header of this form (fStaff) I have a checkbox (called CheckExFilter)

If it's checked I want it to filter to all the records that have CheckEx checked. And it almost works:

[B]Private Sub CheckExFilter_AfterUpdate()[/B][COLOR=Green]
'Filter fStaff form records based on if they're currently employed or not[/COLOR]
If CheckExFilter = True Then
    Me. RecordSource = "SELECT * FROM [tStaff] WHERE [tStaff].[Ex] = True"
ElseIf CheckExFilter = False Then
    Me. RecordSource = "SELECT * FROM [tStaff] WHERE [tStaff].[Ex] = False"
End If

[B]End Sub[/B]

What happens is whichever record is selected when I check the CheckExFilter I get this:

I DO get all the records that also have CheckEx already checked. But I also get the record that was selected before I checked CheckExFilter (to filter the results) now with it's CheckEx box selected. If I uncheck CheckExFilter that selected record is unchecked, and the records without CheckEx selected show up.

However, if I don't uncheck CheckExFilter, and click my clear filter button, that record's CheckEx field stays selected.

Can someone please explain what is happening? I know it's going to be embarrassingly simple.

 

[CJ_London]

you can simplify your code

Private Sub CheckExFilter_AfterUpdate()
    'Filter fStaff form records based on if they're currently employed or not
    Me. RecordSource = "SELECT * FROM [tStaff] WHERE [tStaff].[Ex] = " & CheckExFilter
End Sub

but sounds like you issue is the CheckExFilter is bound to the Ex field - it needs to be unbound

 

[NBRJ]

Perfect, that works! Thank you (for both suggestions!), I knew it would be something simple.