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
– June7, Commented Jun 22, 2017 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
– Macs, Commented Jun 22, 2017 at 20:15
Output to another table. Still recommend you just do calc in SELECT query. — June7
– June7, Commented Jun 22, 2017 at 20:20

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

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, 2017 at 22:46

David Marten

1617 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David Marten Over a year ago

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 Over a year ago

Great! Please upvote the answer so others with a similar problem will find it.

Macs Over a year ago

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

Collectives™ on Stack Overflow

Access Update Query using VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related