我正在使用MS Access VBA并尝试：
我的VBA代码：
Private Sub btnAddWorkID21_Click()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim sql As String
Set dbs = CurrentDb
sql = "SELECT f.FinancesID, " _
& "f.CustomerID, " _
& "f.FinancesDate, " _
& "f.Price AS FinPrice, " _
& "f.PriceLaser, " _
& "f.PaymentID, " _
& "iif(f.ReceiptYesNo='No',1,2) AS receipt_id, " _
& "iif(f.FinancesMemo is null,'',f.FinancesMemo) AS FinMemo, " _
& "a.AppointmentID, " _
& "a.CustomerID, " _
& "a.AppointmentDate, " _
& "a.Price, " _
& "a.WorkID " _
& "FROM " _
& "(SELECT s.*, IIf(s.PriceLaser>0,21,0) AS WorkID " _
& "FROM tblFinances AS s) AS f " _
& "LEFT JOIN " _
& "tblAppointment AS a ON " _
& "(f.WorkID=a.WorkID) AND " _
& "(f.PriceLaser=a.Price) AND " _
& "(f.Price=a.Price) AND " _
& "(f.FinancesDate=a.AppointmentDate) AND " _
& "(f.CustomerID=a.CustomerID) " _
& "WHERE a.AppointmentID IS NOT NULL;"
Set rst = dbs.OpenRecordset(sql, dbOpenDynaset)
With rst
Do Until .EOF
If !AppointmentID > 0 Then
sql = "UPDATE [tblAppointment] SET [FinPrice] = " & !FinPrice & " , " _
& "[PaymentID] = " & !PaymentID & " , " _
& "[ReceiptYesNo] = " & !receipt_id & " , " _
& "[FinancesMemo] = '" & !FinMemo & "' " _
& "WHERE [AppointmentID] = " & !AppointmentID & " ;"
dbs.Execute (sql)
sql = "DELETE * FROM [tblFinances] WHERE [FinancesID] = " & !FinancesID & " ;"
dbs.Execute (sql)
End If
.MoveNext <===== ERROR ======>
Loop
End With
MsgBox "All done...", vbYes
rst.Close
dbs.Close
End Sub
我收到错误3167。因为我已删除，所以找不到ID。
我该如何解决此错误。
考虑避免VBA循环和DAO记录集和运行一个单一的，因为MS Access支持SQL语句UPDATE...JOIN上未查询只读和支持IIF表达。
更高效的是，将以下内容另存为Access引擎的存储查询，以保存最佳执行计划（需要JOIN优化）。
的SQL
UPDATE [tblAppointment] a
LEFT JOIN [tblFinances] f
ON f.PriceLaser = a.Price AND
f.Price = a.Price AND
f.FinancesDate = a.AppointmentDate AND
f.CustomerID = a.CustomerID
SET a.[FinPrice] = f.[Price]
, a.[PaymentID] = f.[PaymentID]
, a.[ReceiptYesNo] = IIF(f.ReceiptYesNo = 'No', 1, 2)
, a.[FinancesMemo] = IIF(f.FinancesMemo IS NULL, '', f.FinancesMemo)
VBA
Private Sub btnAddWorkID21_Click()
DoCmd.OpenQuery "myUpdateQuery" ' NO NEED TO CLOSE ACTION QUERIES
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句