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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BlueWolverine - 09 Apr 2008 20:41 GMT

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.

Signature

BlueWolverine
MSE - Mech. Eng.
Go BLUE!

Reply to this Message

Paolo - 10 Apr 2008 09:51 GMT

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

> Hello,
> MS Access 2003 on XP Pro.
[quoted text clipped - 33 lines]
>
> Thank you in advance for your help.

Reply to this Message

BlueWolverine - 10 Apr 2008 13:04 GMT

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.

Signature

BlueWolverine
MSE - Mech. Eng.
Go BLUE!

> Hi BlueWolverine,
> Actually The openquery execute an existing query.
[quoted text clipped - 45 lines]
> >
> > Thank you in advance for your help.

Reply to this Message