I have connected the MS Access - Frontend with MySQL database using ODBC Connector successfully. 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 change. In tblCustomer we have to Update the new payment details.

Suppose the old balance is $10. Now the person have 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 the ODBC - inserting failed.

Here is the code.

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 exicuted then ODBC - insert failed error - 3146 Occurs.

I have checked that the error implies the datatype-Mismatch - code 13. Then I have changed the datatype of my table as well.

But still not inserting the data.

I m very new to these VBA - MS Access. If experts discuss then will solve this problem definitely. Thanks

CodePudding user response：

1. After rs.FindFirst you must check with If rs.NoMatch Then if you actually found a record to edit.

2. Use this to find the underlying problem of error 3146 "ODBC-Call failed":
   Determine real cause of ODBC failure (error 3146) with ms-access?

CodePudding user response：

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.