时间戳

首页 > 解决方案 > MS Access VBA，试图更新一个表并从另一个表中删除

vba - MS Access VBA，试图更新一个表并从另一个表中删除

问题描述

我正在使用 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。

我该如何解决这个错误。

标签： vbams-access

解决方案

考虑避免 VBA 循环和 DAO 记录集并运行单个SQL 语句，因为 MS Access 支持非只读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

推荐阅读