Access Update Query using VBA

Question

I have a Table like this

SNo Block   SAP SAG BAP BAG DEP DEG
1   600403  1   3   5   4       
2   600405  1   3   1   3   1   1
3   600407  3   1           2   4
4   600409                  3   1
5   600410  1   3   2   5   1   3
6   600413  1   4           1   3

I want to NULL the Cells of SAP and SAG where SAP = 1 and SAG = 3, and null the cells of BAP and BAG where BAP = 1 and BAG = 3 and like wise for DEP and DEG, i am expecting result like this below

SNo Block   SAP SAG BAP BAG DEP DEG
1   600403          5   4       
2   600405                  1   1
3   600407  3   1           2   4
4   600409                  3   1
5   600410          2   5       
6   600413  1   4

Some how after googling, I wrote a code for this, and the code runs successfully without any error but only the SAP Column gets NULLed and the SAG column was not NULLed (the second Query for SAG docmd doesn't work) !

Below is my VBA, Sorry I am new to Access VBA !

Private Sub VbaModule()

Dim db As DAO.Database
Dim rs As Recordset
Dim sSQL As String
Dim sSQL1 As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("T05_Pr2_Null_Not_In_Rem")

sSQL = "UPDATE T05_Pr2_Null_Not_In_Rem SET SAP = NULL " & _
     " WHERE (SAP = 1 AND SAG = 3)"
     DoCmd.RunSQL sSQL

sSQL = "UPDATE T05_Pr2_Null_Not_In_Rem SET SAG = NULL " & _
     " WHERE (SAP = 1 AND SAG = 3)"
     DoCmd.RunSQL sSQL

rs.Close
Set rs = Nothing
db.Close

End Sub

Any Suggestion ?

The second query doesn't work as expected because you have already changed the data in SAP field with the first UPDATE — June7
Oh! Thats right... its a shame.. is it possible to achieve the result as expected by any other means? Any idea? — Macs
Output to another table. Still recommend you just do calc in SELECT query. — June7

David Marten David Marten · Accepted Answer · 2017-06-22T22:46:57

  Dim strSQL As String

  strSQL = "UPDATE T05_Pr2_Null_Not_In_Rem " & _
           "SET " & _
           "  SAP = NULL, " & 
           "  SAG = NULL " & _
           "WHERE SAP = 1 AND SAG = 3;"
  CurrentDb.Execute strSQL, dbFailOnError

  strSQL = "UPDATE T05_Pr2_Null_Not_In_Rem " & _
           "SET " & _
           "  BAP = NULL, " & 
           "  BAG = NULL " & _
           "WHERE BAP = 1 AND BAG = 3;"
  CurrentDb.Execute strSQL, dbFailOnError

  strSQL = "UPDATE T05_Pr2_Null_Not_In_Rem " & _
           "SET " & _
           "  DEP = NULL, " & 
           "  DEG = NULL " & _
           "WHERE DEP = 1 AND DEG = 3;"
  CurrentDb.Execute strSQL, dbFailOnError

Access Update Query using VBA

1 Answers