update table from form query will not work

Question

I am trying to update a table from a form with this command

DoCmd.RunSQL "UPDATE Inspections SET Complete=True WHERE WorkOrder=" & WO.Value

where WO is a text field (unbound) on my form. But I always get 0 rows updated. I can manually type in the values I want to update and that works but trying to pull the values from a field does not. How do I resolve?

<The thread has been moved to the correct category by Eli Qian>

Answer 1

I got it! The single quotes were the answer but I missed the point that I need to oomit all spaces because they count as part of the string

Answer 2

1.  What is the data type of the WorkOrder column?

2.  Was the column by any chance created by means of the lookup field wizard?

Answer 3

If it doesn't update the table, then something is wrong with the UPDATE statement. Are you sure you're getting the correct WorkOrder value?

Sometimes it helps to print out the SQL statement before you do this:

Debug.Print "UPDATE Inspections SET Complete=True WHERE WorkOrder=" & WO.Value

Do this just before calling the Execute statement. The results should print in the Immediate window.

Also, if WorkOrder a Text or Numeric value? If it's a Text value, you have to wrap it in quotes:

"UPDATE Inspections SET Complete=True WHERE WorkOrder='  " & WO.Value & " '  "

I used single quotes, and added some spaces so you can see it.

Answer 4

hmmmm... no errors but neither does it update the table

Answer 5

Try using currentdb.Execute instead, with the dbFailOnError argument:

Currentdb.Execute "UPDATE Inspections SET Complete=True WHERE WorkOrder=" & WO.Value, dbFailOnError

In some cases, using RunSQL won't show you any errors (depending on other settings in Access, or in VBA).