Form Filter Issue (1 Viewer)

[EternalMyrtle]

Hello

I am using the following code to filter my form with a search box:

Me.RecordSource = "qryCompanies"
Me.Filter = "CompanyName Like '*" & Me.SearchTxt & "*' Or webpage Like '*" & Me.SearchTxt & "*' Or PriorName Like '*" & Me.SearchTxt & "*'"
Me.FilterOn = True

'Show message box if no records are found and set focus on search box
If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "No Results Found. Please try again", , "No Records Found"
Me.FilterOn = False
Me.SearchTxt = Null
Me.SearchTxt.SetFocus
End If

It works great with one minor issue: if I try to search for a name containing an apostrophe, I get a syntax error. So, it won't find Children's Hospital for example.

Can anyone tell me how to alter the code so that I won't have this problem?

Thank you!

 

[jdraw]

Try using 2 quotes in your search string (untested)

eg Children''s

 

[EternalMyrtle]

That works for me but my users will not remember this. I guess I can put in an error handler that instructs them to use double apostrophes or something. That would be a solution if I cannot make it work perfectly.

 

[EternalMyrtle]

JDraw: Can you please help me with my error handling? I am using this code but even if my search term does not result in an error, the message box appears:

Private Sub SearchTxt_AfterUpdate()
'Set recordsource and perform search

Me.RecordSource = "qryCompanies"

On Error GoTo ErrHandler
Me.Filter = "CompanyName Like '*" & Me.SearchTxt & "*' Or webpage Like '*" & Me.SearchTxt & "*' Or PriorName Like '*" & Me.SearchTxt & "*'"

Me.FilterOn = True


'Show message box if no records are found and set focus on search box
If DCount("*", Me.RecordSource, Me.Filter) = 0 Then
MsgBox "No Results Found. Please try again", , "No Records Found"
Me.FilterOn = False
Me.SearchTxt = Null
Me.SearchTxt.SetFocus
End If

ErrHandler:
MsgBox ("Your search query needs to be modified.  If you are using an apostrophe in your search text, i.e. Children's, you need to enter two apostrophes, i.e Children''s")
End Sub

Ia lso tried putting the call to the error handler after the search but to no avail. I wish VBA allowed Try...Catch

 

[EternalMyrtle]

Ok, I figured it out. Just need an Exit Sub before the error handler.

Thanks for your help.