我使用ODBC连接器将MS Access -前端与MySQL数据库连接。
这里有tblCustomer和tblPayments。tblCustomer通过外键与tblPayments链接。
我已经编写了代码来更新相应客户的付款详细信息。
为此，我们必须用新的支付条目更新tblCustomer的现有记录集。
基本上，现有客户的付款信息可以更改。在tblCustomer中，我们必须更新新的付款详细信息。
假设以前的余额是10美元，现在这个人已经支付了10美元，那么现在的余额将是0美元。
当我尝试编辑新的$0余额并将其更新到tblCustomer时，它显示
ODBC -插入失败。

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

当rsCustomers.Update行执行时，发生ODBC -插入失败错误- 3146。
我检查了错误是否暗示数据类型不匹配代码13。
然后，我也更改了表的数据类型，但仍然没有插入数据。