CODEWITHSUNDEEP
sqldatabasevbams-access
Bob P
Bob P

Reputation: 237

Can anyone spot the SQL issue here? Trying to update fields in ms-access

Dim sqlUpdate As String

sqlUpdate = "UPDATE [Stock Conversion] SET [Stock Conversion].Status = 'PRINTED' " & _
"WHERE ((([Stock Conversion].Status) = 'NEW'));"

' Turn off warning
DoCmd.SetWarnings False

'Run SQL
DoCmd.RunSQL (sqlUpdate)

'Turn on warning
DoCmd.SetWarnings True

Basically there is no error message, it's just nothing happens at all?

Can anyone help?

Upvotes: 0

Views: 157

Answers (2)

HansUp
HansUp

Reputation: 97131

Paste this statement into SQL View of a new Access query and test it.

UPDATE [Stock Conversion]
SET Status = 'PRINTED'
WHERE Status = 'NEW';

If it doesn't work, hopefully the error message will help you understand why so you can fix it.

If it does work correctly, execute the same statement from VBA using the .Execute method of a DAO.Database object. Since you won't be using DoCmd.RunSQL, there is no motivation to turn SetWarnings off. And that's an important point because you suppress information when you turn SetWarnings off.

Dim db As DAO.Database
Dim sqlUpdate As String

sqlUpdate = "UPDATE [Stock Conversion]" & vbCrLf & _
    "SET Status = 'PRINTED'" & vbCrLf & _
    "WHERE Status = 'NEW';"
Debug.Print sqlUpdate
DoCmd.SetWarnings True ' in case it had been left off somehow '
Set db = CurrentDb
db.Execute sqlUpdate, dbFailOnError
Set db = Nothing

Upvotes: 3

Diego
Diego

Reputation: 36176

somethings to help:

  • remove [Stock Conversion] from the set and the where
  • remove the unnecessary parentheses on the Where
  • remove the semicolon
  • check again.
  • If it doesnt work, remove the where and try again to check if there is an issue with your were clause
  • add a SQL trace to the database to see if the query is really being submited
  • paste the complete SQL statment here so we can help more

Upvotes: 0

Related Questions