Method ‘FindFirst’ of object ‘Recordset2’ failed. After not saving new record

Question

In a form, create a new record, edit some data but before saving it use a combo box on the form to select another record to navigate to. This triggers the cboSalePicker_AfterUpdate. Then during this sub Form_BeforeUpdate executes. The user clicks no on the MsgBox to not save the new record. Then the rest of cboSalePicker_AfterUpdate is executed however the following error message is displayed:

Error Message

Error number -2147417848: Method ‘FindFirst’ of object ‘Recordset2’ failed.

Associated with the line Me.Recordset.FindFirst "[SaleID] = " & Str(Nz(cboSalePicker.Value, 0))

However, if the new record is saved no error is produced and the code performs as expected.

Form_BeforeUpdate

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
    Dim strMsg As String
    Dim iResponse As Integer

    'Specify the mesage to display
    strMsg = "Do you wish to save the changes?" & Chr(10)
    strMsg = strMsg & "Click Yes to Save or No to Discard changes."

    'Display the msg box
    iResponse = MsgBox(strMsg, vbQuestion + vbYesNo, "Save Record?")

    'Check response
    If iResponse = vbNo Then
        'Undo the change.
        DoCmd.RunCommand acCmdUndo
        'Cancel the update
        Cancel = True
    End If

    Exit Sub

ErrorHandler:
    MsgBox "Error number " & Err.Number & ": " & Err.Description
End Sub

cboSalePicker_AfterUpdate

Private Sub cboSalePicker_AfterUpdate()
On Error GoTo ErrorHandler

    Me.Recordset.FindFirst "[SaleID] = " & Str(Nz(cboSalePicker.Value, 0))

    Exit Sub
ErrorHandler:
    MsgBox "Error number " & Err.Number & ": " & Err.Description
End Sub

Thanks

Minty Minty · Accepted Answer · 2019-01-22T11:57:07

You are converting Your SaleID into a String using this

Str(Nz(cboSalePicker.Value, 0))

But your find first is looking for a number. If SaleID is a number then remove the Str() function from your code around the combobox value.

To show the concatenation try this

  Private Sub cboSalePicker_AfterUpdate()
On Error GoTo ErrorHandler

    Dim sCriteria as String

    sCriteria = "[SaleID] = " & Nz(Me.cboSalePicker, 0)
    debug.print sCriteria

     Me.Recordset.FindFirst sCriteria

    Exit Sub
ErrorHandler:
    MsgBox "Error number " & Err.Number & ": " & Err.Description
End Sub

Comment out the first error handler line whilst you are debugging things.

Method ‘FindFirst’ of object ‘Recordset2’ failed. After not saving new record

1 Answers