Form Will Not Display records after Error Handling/Requery

[joesommers]

Hello folks!

I am bit of a novice to Access VBA, I know just enough to be dangerous...to my own progress! I have a continuous form, frmList_MC that employs search as you type feature. The code works great for its intended purpose, however once I type in a value that is not in the record source, a RTE 2185 gets thrown and a requery does not return the records, the form stays empty. Code as follows:

Private Sub txtSearch_Change()
On Error GoTo CleanFail

Me.AllowAdditions = True

Me.RecordSource = "SELECT * FROM qryList_MC " & _
"WHERE " & _
"[LF] LIKE ""*" & txtSearch.Text & "*"" OR " & _
"[MCY] LIKE ""*" & txtSearch.Text & "*"" OR " & _
"[LicensePlate] LIKE ""*" & txtSearch.Text & "*"""


txtSearch.SetFocus
txtSearch.SelStart = Len(txtSearch.Text)

Me.AllowAdditions = False

CleanExit:
Exit Sub


CleanFail:
If Err.Number = 2185 Then
MsgBox "Search criteria has no match."
Err.Clear
Resume CleanExit
End If
End Sub


Private Sub cmdRefresh_Click()
SetDefaults 'txtSearch = ""


Me.Requery
DoCmd.GoToControl "txtSearch"
End Sub

In order to test the requery I swapped it out for DoCmd.Close then OpenForm and thatt does not work either. However, clearing the text field and .setfocus works just fine. Scratching my pea brain here and would truly appreciate a bail out. I do not like wasting folks' time, so i hope I provided the correct/sufficient info! TIA!

[Bob Fitz]

Perhaps add the following line:
Me.RecordSource = "SELECT * FROM qryList_MC "
before the ilne:
Resume CleanExit

[joesommers]

Thank you so much, Mr. Fitz! Worked perfectly!

[Bob Fitz]

Thank you so much, Mr. Fitz! Worked perfectly!

You're welcome. Always happy to help, if I can

[Welshgasman]

If later on, you wish to change your recordsource, you will need to amend the code as well as the search criteria.
I would save the recordsource to a string variable and then the recordsource to that variable if you egt that error as Bob has advised.

Just one less thing to change, especially if inexperienced.