Solved Query returning no records causes erro on form command (1 Viewer)

[The Rev]

I pilfered the code from this thread:

Dynamically search multiple fields

This DB is a modification of a search tool that was posted in these forums several years ago. The tool allows the user to type text into a text box (the text is matched against any part of a number of fields) with the results being shown dynamically in a list box as the user types. Essentially...

www.access-programmers.co.uk

And removed the logic for trailing blanks because I don't care about them. I built my form and the query exactly as described. It works fine except if no results are returned. If there are no results, I get a Runtime Error 2105; You can't go to the specified record, and the offending line is:

Me.SearchFor.SetFocus

How do I tell the form that if no records are returned, just say "No records returned"?

Thanks again for the assist!!

 

[The Rev]

Thread address is https://www.access-programmers.co.uk/forums/threads/dynamically-search-multiple-fields.188663/

 

[theDBguy]

Hi. Can't download the demo right now, but does the code include an error handler? If not, you could try adding one.

 

[The Rev]

Hi. Can't download the demo right now, but does the code include an error handler? If not, you could try adding one.

Added one, and the error vanishes, but the form completely blanks out. All the text boxes vanish and I have to close and reopen it to search again.

 

[theDBguy]

Added one, and the error vanishes, but the form completely blanks out. All the text boxes vanish and I have to close and reopen it to search again.

Hi. Maybe if you can post your version of the demo, we can take a look and help you fix it?

 

[The Rev]

Hi. Maybe if you can post your version of the demo, we can take a look and help you fix it?

Why certainly:

Private Sub SearchFor_Change()
On Error GoTo ErrorHandler
    Dim vSearchString As String

    vSearchString = SearchFor.Text
    SrchText.Value = vSearchString
    Me.SearchResults.Requery

Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus
    DoCmd.Requery

Me.SearchFor.SetFocus
If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If

ErrorHandler:
Exit Sub

End Sub

 

[The Rev]

And this is the exact issue I am having:

https://www.datanumen.com/blogs/avoid-unexpected-blank-forms-ms-access/

Condition 1:
The form is in query mode, in which the criteria contains no record.

 

[The Rev]

Fixed! I had to edit the properties of the List Box SearchResults. "Allow Value List Edits" to NO and Enabled to NO. Well that was weird...

Thanks again for your willingness to assist!

 

[theDBguy]

Fixed! I had to edit the properties of the List Box SearchResults. "Allow Value List Edits" to NO. Well that was weird...

Thanks again for your willingness to assist!

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

 

[The Rev]

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.

Ugh. The form will not let me scroll in the list box when Enabled = NO, but it goes back to blanking when Enabled = YES...

 

[theDBguy]

Ugh. The form will not let me scroll in the list box when Enabled = NO, but it goes back to blanking when Enabled = YES...

If you can, it might help if you could post a sample copy of your db. Just a thought...

 

[The Rev]

Unfortunately, it has some proprietary info built in that my boss won't allow me to leak out... I appreciate you guys a ton.

 

[The Rev]

Got it!! I had to evaluate the count of records returned by the query and if it was 0, set Enabled to NO and if it was not 0, set it to YES (True and False in the code)

On Error GoTo ErrorHandler
Me.SearchResults.Enabled = True

    Dim vSearchString As String
    vSearchString = SearchFor.Text
    SrchText.Value = vSearchString

    Me.SearchResults.Requery

Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim rsCount As Integer
    Dim queryNameOrSQL As String
    queryNameOrSQL = "Qry_SearchAll"
Set db = CurrentDb
Set rs = db.OpenRecordset(queryNameOrSQL)
rsCount = rs.RecordCount

If rsCount > 0 Then
Me.SearchResults.Enabled = True
GoTo ErrorHandler
Else
Me.SearchResults.Enabled = False
Me.SearchResults = Me.SearchResults.ItemData(1)
Me.SearchResults.SetFocus
    End If

    DoCmd.Requery

Me.SearchFor.SetFocus
If Not IsNull(Len(Me.SearchFor)) Then
Me.SearchFor.SelStart = Len(Me.SearchFor)
End If

ErrorHandler:
Exit Sub