Trouble Getting Filter to work after record is updated

[Liz_I3]

Hi
I am working in Access 2010. I have created a form with an ubound textbox that acts as a filter. I work as long as I dont update any fields on a record. Once the user updates a field the filter cannot find any records but the first one. I have tried Requery (the forms record source is a query) and tried turning the filters on and off but cannot get it to work correctly. The below code is on the After Update event of the unbound textbox (txtSearch)

Thanks L

Dim strFilter As String
On Error GoTo ErrHandler
If Me.txtSearch <> "" Then
'strFilter = "[BoxNumber] like '*" & Me.txtSearch.Text & "*'"
strFilter = "[BoxNumber] = """ & (Me.txtSearch) & """"
Me.Filter = strFilter
Me.FilterOn = True
If Me.RecordsetClone.RecordCount = 0 Then
MsgBox "Filter returns no records"
Me.FilterOn = True
End If
Else
Me.Filter = ""
Me.FilterOn = False
End If
With Me.txtSearch
.SetFocus
.Value = " "
.SelStart = Len(Me.txtSearch.Text)
End With
Exit Sub
ErrHandler:
MsgBox Err.Description, vbExclamation
End Sub

 

[Micron]

Here's my guesses: change strFilter = "[BoxNumber] = """ & (Me.txtSearch) & """" to
- strFilter = "[BoxNumber] = ' " & (Me.txtSearch) & " ' "
I have included spaces after the single quotes only to show them more clearly. You should remove them. Your method causes 'expected end of statement' errors in sql because the first quote ends the sql text when you nest them, so I'm wondering if it's an issue here too. Another approach is to use ASCII character codes when you need special characters in code strings.

- ApplyFilter is a method of the DoCmd object. I think the proper usage is docmd.applyfilter, strFilter (not sure about this)
- should not be an issue, but you already have turned on the filter before this:
MsgBox "Filter returns no records"
Me.FilterOn = True
End If

- I don't see where you are executing the requery nor on what (form control? form?). If it involves the value of txtSearch, note that you have set it to the value of a single space. How does that work out with your query that's behind the form? Did you mean .Value = "" ?

 

[Liz_I3]

Thank you very very much Micron that did the trick. You made my day, I have been playing with it for a couple of days now. Also yes Value = " " was a typo it should be Value = "" thank for catching it

L

 

[Micron]

You're welcome - but which suggestion was the fix?