Search code examples
mysqlvbams-accessodbc

ODBC insert Failed - error 3146 while editing and updating the recordset


I connected the MS Access - Frontend with MySQL database using ODBC Connector.

There I have the tblCustomer and tblPayments. tblCustomer is linked with the tblPayments with the foreign key.

I had written the code to update the payment details of the respective customer.

For that, we have to update the existing recordset of the tblCustomer with the new payment entries.

Basically, Existing customers' payment information can be changed. In tblCustomer we have to Update the new payment details.

Suppose the old balance is $10. Now the person has paid the $10. So the current balance will be $0.
When I try to edit and update the new $0 balance to the tblCustomer it shows me

ODBC - inserting failed.

On Error GoTo Proc_Err

' variable for return from msgbox
Dim intRetValue As Integer
If Me.PaymentAmount = 0 Then
    MsgBox "You must enter a payment amount or cancel the transaction.", vbOKOnly
    Exit Sub
End If
If Me.txtPaymentVoucher < 1 Or IsNull(Me.txtPaymentVoucher) Then
    MsgBox "You must enter a voucher number.", vbOKOnly
    Me.txtPaymentVoucher.SetFocus
    Exit Sub
End If
If Me.TransactionType = "Debit" Then
    If Me.PaymentAmount > 0 Then
        Me.PaymentAmount = Me.PaymentAmount * -1
    End If
End If
If Me.PaymentReturnedIndicator Then
    If Me.PaymentAmount > 0 Then
        MsgBox "If this is a returned check enter a negative figure.", vbOKOnly
        Me.PaymentAmount.SetFocus
    End If
End If
If Me.PaymentCustomerID = 0 Then
    Me.PaymentCustomerID = glngPaymentCustomerID
End If
If gbolNewItem Then
    If Me.cboTransactionType = "Payment" Then
        Me.txtLastPayment = Date
    End If
End If
Me.txtCustomerBalance = (Me.txtCustomerBalance + mcurPayAmount - Me.PaymentAmount)
Me.txtPalletBalance = (Me.txtPalletBalance + mintPallets - Me.txtPallets)
  
Dim dbsEastern As DAO.Database
Dim rsCustomers As DAO.Recordset
Dim lngCustomerID As Long
Dim strCustomerID As String
Set dbs = CurrentDb()
Set rsCustomers = dbs.OpenRecordset("tblCustomers")

lngCustomerID = Me.PaymentCustomerID
strCustomerID = "CustomerID = " & lngCustomerID
rsCustomers.MoveFirst
rsCustomers.FindFirst strCustomerID
rsCustomers.Edit
rsCustomers!CustomerBalance = Me.txtCustomerBalance
rsCustomers!Pallets = Me.txtPalletBalance
rsCustomers!CustomerLastPaymentDate = Now()
rsCustomers.Update
rsCustomers.Close
Set rsCustomers = Nothing

FormSaveRecord Me
gbolNewItem = False
gbolNewRec = False
Me.cboPaymentSelect.Enabled = True
Me.cboPaymentSelect.SetFocus
Me.cboPaymentSelect.Requery
Me.fsubNavigation.Enabled = True
cmdNormalMode
Proc_Exit:
    Exit Sub
Proc_Err:
    gdatErrorDate = Now()
    gintErrorNumber = Err.Number
    gstrErrorDescription = Err.Description
    gstrErrorModule = Me.Name
    gstrErrorRoutine = "Sub cmdSaveRecord_Click"
    gbolReturn = ErrorHandler()                           ' Display the error message
    Resume Proc_Exit

End Sub

When rsCustomers.Update line executes then ODBC - insert failed error - 3146 occurs.

I checked that the error implies the datatype-Mismatch - code 13.
Then I changed the datatype of my table as well, but still not inserting the data.


Solution

  • While Andre showed you to the correct use of the FindFirst function, I find it pointless to open the whole customers table and then search for a single customer, when you can filter the recordset at point of creation to return only the customer you need.

    lngCustomerID = Me.PaymentCustomerID
    Set rsCustomers = dbs.OpenRecordset("SELECT * FROM tblCustomers WHERE CustomerID =" & lngCustomerID, dbOpenDynaset)
    
    If rsCustomers.EOF Then
        Debug.Print "Customer not found"
        GoTo Proc_Exit
    End If
    
    'safe to update customer at this point
    With rsCustomers
        .Edit
        '....
        .Update
    End With
    

    You should then probably change rsCustomers to rsCustomer to make more sense.