Help with filter command when no records found

[gojets1721]

I have the below command that allows the user to search a field in a form and filter to whatever the user inputs. It works great but I just realized that if no matching records are found, the form just goes completely blank. This requires the user to exit the form and get back in.

Ideally, if no records are found, it will stay on the current record and just populate a msg box stating nothing found. Any suggestions on how to code that?

Private Sub btnSearchCategory_Click()
On Error GoTo btnSearchCategory_Click_Err

    Dim S As String
    
    S = InputBox("Enter Category", "Category Search")
    If S = "" Then Exit Sub
    
    Me.Filter = "Category LIKE ""*" & S & "*"""
    Me.FilterOn = True

btnSearchCategory_Click_Exit:
    Exit Sub

btnSearchCategory_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    Resume btnSearchCategory_Click_Exit
 
End Sub

 

[plog]

Do a DCount (https://www.techonthenet.com/access/functions/domain/dcount.php) using your filter and the form's data source. If 0 show your message, if not do the filter.

 

[gojets1721]

Could you provide an example using my code? I'm not really sure what you mean. I've never used DCount that way

 

[plog]

That's not an example, that's giving you code.

Post the name of the datasource the form is based on and I am sure someone with something to prove will come along and give you the specific code you need. Until then, give it a shot and post back what you try and how it is not working.

 

[MarkK]

It might work to just check Me.Recordset.RecordCount after you apply the filter, and if zero, then remove the filter.

Me.FilterOn = Me.Recordset.RecordCount

 

[gojets1721]

That's not an example, that's giving you code.

Post the name of the datasource the form is based on and I am sure someone with something to prove will come along and give you the specific code you need. Until then, give it a shot and post back what you try and how it is not working.

So I tried the below and the search works, but it still behaves the same when I type gibberish into the input box. It blanks the form and doesn't display the msgbox

Private Sub btnSearchCategory_Click()
On Error GoTo btnSearchCategory_Click_Err

    Dim S As String
    
    S = InputBox("Enter the Category", "Category Search")
    If S = "" Then Exit Sub
    
    If DCount("Category", "tblComplaints") > 0 Then
        Me.Filter = "Category LIKE ""*" & S & "*"""
        Me.FilterOn = True
    Else
       MsgBox "There are no records to view."
    End If

btnSearchCategory_Click_Exit:
    Exit Sub

btnSearchCategory_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    Resume btnSearchCategory_Click_Exit
 
End Sub

 

[theDBguy]

Hi. Could you give this one a try? Thanks.

Private Sub btnSearchCategory_Click()
On Error GoTo btnSearchCategory_Click_Err

    Dim S As String
    Dim var As Variant

    S = InputBox("Enter Category", "Category Search")
    If S = "" Then Exit Sub

    var = Me.Bookmark
   
    Me.Filter = "Category LIKE ""*" & S & "*"""
    Me.FilterOn = True

    If Me.Recordset.RecordCount = 0 Then
        Me.FilterOn = False
        Me.Bookmark = var
        MsgBox "No matching record for that category.", vbInformation, "Info"
    End If

btnSearchCategory_Click_Exit:
    Exit Sub

btnSearchCategory_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
    
    Resume btnSearchCategory_Click_Exit
 
End Sub

 

[plog]

Right now your DCount is just looking at total records in tblComplaints. You want to know if the filter will have any results. So you need to add your criteria to the DCount.

 

[plog]

There we go, you found your guy with something to prove.

 

[theDBguy]

There we go, you found your guy with something to prove.

Hi. Sorry for jumping in...

 

[gojets1721]

Hi. Could you give this one a try? Thanks.

Private Sub btnSearchCategory_Click()
On Error GoTo btnSearchCategory_Click_Err

    Dim S As String
    Dim var As Variant

    S = InputBox("Enter Category", "Category Search")
    If S = "" Then Exit Sub

    var = Me.Bookmark
  
    Me.Filter = "Category LIKE ""*" & S & "*"""
    Me.FilterOn = True

    If Me.Recordset.RecordCount = 0 Then
        Me.FilterOn = False
        Me.Bookmark = var
        MsgBox "No matching record for that category.", vbInformation, "Info"
    End If

btnSearchCategory_Click_Exit:
    Exit Sub

btnSearchCategory_Click_Err:

    MsgBox "Error #" & Err.Number & " - " & Err.Description, , "Error"
   
    Resume btnSearchCategory_Click_Exit

End Sub

When I use that, I'm getting a 3159 error on the 'Me.Bookmark = var' line

 

[theDBguy]

When I use that, I'm getting a 3159 error on the 'Me.Bookmark = var' line

Hi. What is the error message for 3159? Try commenting out that line for now.

 

[gojets1721]

Hi. What is the error message for 3159? Try commenting out that line for now.

That worked! Thanks!

 

[theDBguy]

That worked! Thanks!

So, now you just need to troubleshoot why you were getting that error.

 

[MarkK]

I think applying a filter opens a new recordset, so a bookmark from a previous recordset is no longer valid.

 

[theDBguy]

I think applying a filter opens a new recordset, so a bookmark from a previous recordset is no longer valid.

Thanks. You're probably right.