我在編輯和更新記錄集時面臨ODBC插入失敗-錯誤3146

我已成功使用 ODBC 連接器將 MS Access - Frontend 與 MySQL 資料庫連接起來。我有tblCustomerand 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。然后我也更改了表的資料型別。

但仍然沒有插入資料。

我對這些 VBA - MS Access 很陌生。如果專家討論那么肯定會解決這個問題。謝謝

uj5u.com熱心網友回復：

雖然 Andre 向您展示了該FindFirst函式的正確用法，但我發現打開整個客戶表然后搜索單個客戶是沒有意義的，因為您可以在創建點過濾記錄集以僅回傳您需要的客戶。

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

然后，您可能應該更改rsCustomers為rsCustomer更有意義。

uj5u.com熱心網友回復：

1. 之后rs.FindFirst，您必須檢查If rs.NoMatch Then是否確實找到了要編輯的記錄。

2. 使用它來查找錯誤 3146“ODBC-Call failed”的根本問題：
   使用 ms-access 確定 ODBC 失敗（錯誤 3146）的真正原因？

標籤：mysql vba 毫秒访问 数据库

上一篇：將舊的Access200032位資料庫轉換為Access36564位

下一篇：DoCmd.CancelEvent/Cancel=真實用法