Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2013
    Posts
    12

    Unanswered: VB FindRecord crash if criteria not found

    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:

    Code:
    Like "*" & [Forms]![Part_List_form]![Stat_Box_Select] & "*"
    Code:
    Like "*" & [Forms]![Part_List_form]![Cat_Box_Select] & "*"
    for the combo boxes the code is:

    Code:
    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


    Code:
    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.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The error message is saying it cant find any rows
    either put in an error handler or a nasty work around is on error resume next
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2013
    Posts
    12
    Hi, Thanks for the replay

    I'm new to access and VB, so I din't really understood what you mean.

    what I want it to do is show no records.
    I was thinking of doing an if statement like:

    If DoCmd.FindRecord (Me!Cat_Box_Select.Value) is false them
    exit sub

    but that didn't work

    or if DoCmd.Findnext (Me!Cat_Box_Select.Value) is False them
    exit Sub

    That didn't work either.
    How would you go around it
    Thank so much for you help

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    id put in some error trapping
    or fialing htat put in 'on error resume next', effectively ignore any errors and keep processing. as opposed to placing an error handler and trapping that sepcific error and handle it more gracefully...

    ..gracefully could be a message box saying no rows returned
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2013
    Posts
    12
    Hey that worked

    Awesome, thanks so much.
    On to the next mission

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •