Help with a filter

[iisjman07]

Hi, I'm currently creating a database solution for a computer shop. Basically, I'm having issues with a filter I'm using so that a user can easily 'jump' to a certain record based on the ID (primary key). My issue is that I can enter an ID into the search field, press the 'Apply Filter' button, and it will jump to the correct job, but after pressing the 'Remove Filter' button which turns off the filter, trying to create a new filter fails. So it works once but not more.

The code running behind the buttons is:
Apply Filter

Private Sub Command78_Click()
        Me.Filter = "ID = Forms![Workshop_frm].[SearchBox].Value"
        Me.FilterOn = True
End Sub

Remove Filter:

Private Sub Command79_Click()
        Me.FilterOn = False
End Sub

I have attached a small version of the database as I realise this could be confusing... Any help would be greatly appreciated

 

[iisjman07]

Aah, thanks for that. I added in a couple of 'Me.Refresh's which didn't fix everything, but I also added a couple of 'Me.Requery's which appears to have solved it. Thanks

 

[vbaInet]

Try this:

Private Sub Command78_Click()
[COLOR=Red]        Me.Filter = vbNullString[/COLOR]
        Me.Filter = "ID = Forms![Workshop_frm].[SearchBox].Value"
        Me.FilterOn = True
End Sub

 

[vbaInet]

Just a little note re Refresh and Requery - Refresh will update (or recalc) the current record whilst Requery will do so for the whole recordset. So you use one or the other where necessary.

 

[iisjman07]

Just a little note re Refresh and Requery - Refresh will update (or recalc) the current record whilst Requery will do so for the whole recordset. So you use one or the other where necessary.

Cool, good to know. Thanks Uncle Gizmo & vbaInet for the help