Solved: VBA Update SQL Query Error

[Bopo2]

Hey all

When I try to run the following line of code, I always get

Object Required runtime error

Here's my update statement, was wondering if anyone can see anything wrong with it, as I've been trying for 2 hours to figure out what's wrong :(

db.Execute ("UPDATE TblResourceType SET TblResourceType.Quantity = " & stockValue & _
" WHERE TblResourceType.ResourceType= " & Forms![FrmResource]![cboresourcetype] & "")

Edited 1-Dec-09 by geekgirlau. Reason: insert line breaks

[geekgirlau]

There are a couple of things to check here.

First, is your SQL statement correct? You need to check that the SQL statement would actually perform as you expect it to.

Have you declared "db" as a variable?

I generally use

DoCmd.RunSQL <statement>

but this is a personal preference. I like to assign my SQL statement to a variable to make it easier to test it - it's all too easy to accidentally leave off a space, quotation mark or comma!

[Bopo2]

Thanks for the suggestion! I tried DoCmd.RunSQL and after playing a little more using msg boxes and figured that Access uses literal strings, so inserting " ' " did the trick.

Although I'm prompted within a update message box, is there a method of overiding this? I really don't want the user to be prompted with this. It seems DoCmd.RunSQL accepts another parameter, but it doesn't look like it's related.

[geekgirlau]

DoCmd.SetWarnings False
DoCmd.RunSQL <statement>
DoCmd.SetWarnings True


Just be careful when using " ' ". Although this will work in most cases, if there is the remotest possibility that your data may contain an apostrophe you are going to have issues. Below are examples of a couple of different methods you can use:


Dim strSQL As String


' option 1
strSQL = "UPDATE TblResourceType " & _
"SET TblResourceType.Quantity = " & stockValue & " " & _
"WHERE TblResourceType.ResourceType= " & _
Chr(34) & Forms![FrmResource]![cboresourcetype] & Chr(34)

' option 2
strSQL = "UPDATE TblResourceType " & _
"SET TblResourceType.Quantity = " & stockValue & " " & _
"WHERE TblResourceType.ResourceType= """ & Forms![FrmResource]![cboresourcetype] & """"


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

[CreganTur]

Whenever you play with the SetWarnings function you must be certain to set it back to True when you're done. If you don't do this, then your alerts will be turned off for almost everything until you set the value to True again. It can cause a number of problems, so be aware of this.

[geekgirlau]

Good point Randy.

Of course normally you would have your error handling there as well, so you might end up with something like this to ensure that the warnings are turned back on:


'...
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

ExitHere:
DoCmd.SetWarnings True
Exit Sub

ErrHandler:
MsgBox "(" & Err.Number & ")" & vbCrLf & _
Err.Description, vbCritical, "Unexpected Error"
Resume ExitHere
End Sub

[Bopo2]

Thanks for the suggestions, a final query (;)) I have, can DoCMD return a value, for example

sqlQuery = "SELECT COUNT (EmpNum) FROM TblRsrcEmp WHERE EmpNum = " & employeeName & " AND Returned = 'No'"
DoCmd.RunSQL (sqlQuery)
I'd like to store the result on that query in a variable, however currently I haven't found a method of DoCMD's that would provide this functionality.

[OBP]

You can set a Recordset to your SQL and that will return the data for you to put it in to a variable.
Like this.
Dim er As Double, rs As Object, SQL As String
SQL = "SELECT LabourRates.* " & _
"FROM LabourRates " & _
"WHERE Date1 <= #" & Format(Me.Date, "mm/dd/yyyy") & "#" & _
"And Date2 > #" & Format(Me.Date, "mm/dd/yyyy") & "#"
Set rs = CurrentDb.OpenRecordset(SQL)
er = rs.EndUserRate
rs.Close
Set rs = Nothing

[Bopo2]

Cheers for the example.