CODEWITHSUNDEEP
vbams-access
YvetteLee
YvetteLee

Reputation: 1090

MS Access VBA, Trying to update one table and delete from another

I am using MS Access VBA and trying to:

My VBA code:

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

I am getting ERROR 3167. As it can't find ID because i have delete.

How can i fix this error.

Upvotes: 1

Views: 292

Answers (2)

YvetteLee
YvetteLee

Reputation: 1090

After June7 comment, i am posting this solution.

Create in Table B a column Status and Update it to Yes or what ever you want. After the loop, simple Delete all rows in Table B where Status = Yes.

The code:

... 
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 = "UPDATE [tblFinances] SET [Status] = YES WHERE [FinancesID] = " & !FinancesID & " ;"
             dbs.Execute (sql)
        End If
        .MoveNext   
    Loop
End With

MsgBox "Update Finished.", vbOK
sql = "DELETE * FROM [tblFinances] WHERE [Status] = YES ;"
dbs.Execute (sql)
MsgBox "Delete Finished", vbOK

rst.Close
dbs.Close
End Sub

Upvotes: 0

Parfait
Parfait

Reputation: 107567

Consider avoiding VBA looping and DAO recordsets and run a single SQL statement since MS Access supports UPDATE...JOIN on queries that are not read-only and supports IIF expressions.

Even more efficient, save below as a stored query for Access engine to save best execution plan (needed for JOIN optimization).

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

Upvotes: 4

Related Questions