FindRecord Crash if criteria not found

[quicova]

Hello,

I made a continuous form from a query with 2 comboBoxes to filter the query.

in the query the code of each column is:

Like "*" & [Forms]![Part_List_form]![Stat_Box_Select] & "*"

Like "*" & [Forms]![Part_List_form]![Cat_Box_Select] & "*"

for the combo boxes the code is:

Private Sub Cat_Box_Select_AfterUpdate()


    Me!Category.SetFocus
    If IsNull(Me!Cat_Box_Select.Value) Then
        Exit Sub
    
    Else:
    DoCmd.FindRecord (Me!Cat_Box_Select.Value)
    End If
    
    DoCmd.ShowAllRecords

and

Private Sub Stat_Box_Select_AfterUpdate()
    
    Me!Status.SetFocus
    If IsNull(Me!Stat_Box_Select.Value) Then
        Exit Sub
    
    Else:
    
    DoCmd.FindRecord (Me!Stat_Box_Select.Value)
    End If
    
    DoCmd.ShowAllRecords
    
End Sub

It all works fine. I can use both combobox to filter or just 1 and leave the other blank and it still works.

However when I use both and the criteria is not found, for example if a category doesn't have any records with that status, it will crash saying:


Run-Time Error "2137"
You can't use Find or replace now


anyone have any ideas on how to fix this.

[ItsMe]

Based on your comment of it crashing when no record is found you could insert the following code to check if a record exists for the form's recordset.

Dim rcd As Recordset
Set rcd = Me.RecordsetClone
If rcd.RecordCount <= 0 Then
Exit Sub
End If

[mrojas]

Use the DCount or the DLookup function. It will count records if the provided criteria is matched. If so, proceed with next line of code, otherwise bail out.

[quicova]

Awesome that worked
Thanks so much everyone
on to the next mission

[mrojas]

If solved, please mark it so.