CurrentDB.Execute Syntax error.... or a better way to insert

[JayRab]

Ok trying to update specific values into a query. here is the code I have now

Dim strtime As Date
Dim streqid As Integer
Dim strstatus As String
streqid = Me.ID
strtime = Me.Time_Delivered
strstatus = "Closed"

strSQL = "UPDATE HotCallsQ SET HotCallsQ.Status = '" & strstatus & "', HotCallsQ.[Time Job Ended] = #" & strtime & "# WHERE (((HotCallsQ.[Equipment DownTime ID])=" & streqid & "))"
Debug.Print (strSQL)
CurrentDb.Execute (strSQL)

strtime is a date
streqid is autonumber

the print results give the following:


UPDATE HotCallsQ SET HotCallsQ.Status = 'Closed', HotCallsQ.[Time Job Ended] = #1/24/2014 1:50:00 AM# WHERE (((HotCallsQ.[Equipment DownTime ID])=313))

I get the too few parameters error on the CurrentDb.Execute line.

Any help would be appreciated.

Or is there is a better way to do this besides SQL string?

Thanks

[ItsMe]

What happens if you paste the SQL from the immediate window into a new query object's SQL view? Are the three field names in table HotCallsQ spelled correctly?

[JayRab]

It works correct if I copy and paste in SQL window.

I built the SQL string from getting it to work in SQL query window and just adding in the concatenated variables

[ItsMe]

Another possible difference between the original SQL and what the Immediate Window provided could be the literal values provided by the variables. Running an UPDATE query using the literal values might recreate the exception. Maybe revert the record to a previous state and test the SQL.

It is just that I do not see where it is going wrong. I usually write my Execute lines like
CurrentDb.Execute strSQL
without the parenthesis

But I doubt that is the issue. So, I am still stuck on recreating the error in a query object using the SQL provided by the Immediate Window.

[JoeM]

Just for kicks, what happens if you try running it with this command?

DoCmd.RunSQL strSQL

[ItsMe]

I am thinking that is a good idea

[JayRab]

Works now with the do command... wonder why that was???

Also how to I keep them from having to click the YES to you are about to update rows?

[JoeM]

Works now with the do command... wonder why that was???

Also how to I keep them from having to click the YES to you are about to update rows?

Not sure, but I have always had better luck doing it that way.

To suppress the messages, use this:

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

[ItsMe]

There is a reason and I can't remember why. Thanks Joe

[JayRab]

Awesome Guys. I appreciate all the help. Wish there was a way to REP people who give great help.

[JoeM]

Awesome Guys. I appreciate all the help. Wish there was a way to REP people who give great help.

Your welcome. There is. See: https://www.accessforums.net/forum-s...ped-30072.html

There is a reason and I can't remember why. Thanks Joe

If you remember or figure out why, let me know. I would love to know too!
There are some interesting discussions out there on the web on the two methods, like:
http://www.utteraccess.com/wiki/inde...SQL_vs_Execute
and
http://www.eileenslounge.com/viewtopic.php?f=29&t=9414

I am sure if I had the time to sift through them all, eventually I could probably figure out why.

[ItsMe]

Paul explained it to me once. I will shoot you a PM if my rusty cage rattles the memory loose.

[JayRab]

Rep all who helped me thanks for that. I will read up on them. Fairly new to a lot of this. Had a Visual Basic course back in college but havent used that in 8 years. Thankfully spent a lot of time with Matlab and learned a lot of the coding and loping in that which helps me.