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, Jun 22 '17 at 20:10
Oh! Thats right... its a shame.. is it possible to achieve the result as expected by any other means? Any idea? — Macs, Jun 22 '17 at 20:15
Output to another table. Still recommend you just do calc in SELECT query. — June7, Jun 22 '17 at 20:20

score 3 · Accepted Answer · answered Jun 22 '17 at 22:46

3

  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

answered Jun 22 '17 at 22:46

David Marten

116
6

Update both fields in each SQL statement. Don't bother opening a recordset - it's not doing anything here since you are using action queries. Use CurrentDb.Execute() rather than DoCmd.RunSQL() - it doesn't prompt the user that records will be updated, but will rollback if an error occurs. – David Marten Jun 22 '17 at 22:48
Great! Please upvote the answer so others with a similar problem will find it. – David Marten Jun 23 '17 at 22:11
I couldn't able to up vote it, because i am new member in stackoverflow, i dont have sufficient point to do that....If you can up vote the question, i might able to up vote the answer....thx – Macs Jun 26 '17 at 05:21

Access Update Query using VBA

1 Answers1