Trying to run update query in VBA, new error Run time 7874

[BlueWolverine]

Hello,
MS Access 2003 on XP Pro.

So I have a little code snippet. I know there is a more direct way to do
what I am doing but I tried it and I don't like it. (Link the form to the
table so that changes to field link back to the table directly.)

What I really want is the following code to work.


Dim v_Color As String, v_VIN As String, strSQL As String

If IsNull(Me.ExtColor.Value) Or Me.ExtColor.Value = "" Then
v_Color = "Not Specified"
Else
v_Color = Me.ExtColor.Value
End If
v_VIN = Me.VIN.Value
strSQL = "UPDATE t_VehicleInventory SET t_VehicleInventory.Color = '" &
v_Color & "' " & _
"WHERE (((t_VehicleInventory.VIN)='" & v_VIN & "'));"
DoCmd.SetWarnings False
MsgBox strSQL
DoCmd.OpenQuery strSQL


This on exit the field whose value I want to force back into the table.

Here's the error I get.

<><><>
Run-time error '7874':

Microsoft Office Access can't find the object 'UPDATE
t_VehicleInventory SET t_VehicleInventory.Color = 'Not Specified'
Where (((t_VehicleInventory.VIN)='ABCDEFGHIJKLMNOPQ'));.'
<><><>

Thank you in advance for your help.

 

[Paolo]

Hi BlueWolverine,
Actually The openquery execute an existing query.
To execute a sql statement use docmd.runsql or execute.
e.g.
docmd.runsql(strSQL)
or
currentdb.execute(strSQL).
using execute you don't need to set the warnings off 'cause this method
doesn't display nothing. It just execute the statement.

HTH Paolo

 

[BlueWolverine]

Thanks! I knew that but wasn't thinking about it. Good call that cleared it
right up.

I will probably remember it next time that's for sure.

As for the warnings, unless it's displaying an error message, I want my code
to run and never show anything I don't specifically write. So I do that a
lot on principle.