object required

  • Thread starter Thread starter Walter
  • Start date Start date
W

Walter

When this query tries to run it generates an "object required" error at the
Set strSQL. I'm unable to see any difference from others I've found in
searches. What am I missing?


Dim db As DAO.Database
Dim strSql As String
Dim varLO, varLL, varLS, varLTD, varTID As Variant

Set varLO = MyControl1
Set varLL = MyControl2
Set varLS = MyControl3
Set varLTD = MyControl4
Set varTID = MyControl5

Set strSql = "UPDATE tblMyTable " _
& "SET MyField1 = varLO, " _
& "MyField2 = varLL " _
& "MyField3 = varLS " _
& "MyField4 = varLTD " & _
"WHERE ((MyField5)= varTID));"

Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
 
hi Walter,
Dim strSql As String
Dim varLO, varLL, varLS, varLTD, varTID As Variant
Click to expand...
In VBA each variable in such a list without an explicit As Type is
declared automatically as Variant. E.g.

Dim a, b, c As Long

declares a and b as Variant and c as Long.

Set varLO = MyControl1
Set varLL = MyControl2
Set varLS = MyControl3
Set varLTD = MyControl4
Set varTID = MyControl5
Click to expand...
This is normally not necessary.
Set strSql = "UPDATE tblMyTable " _
& "SET MyField1 = varLO, " _
& "MyField2 = varLL " _
& "MyField3 = varLS " _
& "MyField4 = varLTD " & _
"WHERE ((MyField5)= varTID));"
Click to expand...
A

MsgBox strSQL

will show you your error. Use

" field = " & MyControl1.Value

instead.



mfG
--> stefan <--
 
Walter said:
When this query tries to run it generates an "object required" error at
the
Set strSQL. I'm unable to see any difference from others I've found in
searches. What am I missing?


Dim db As DAO.Database
Dim strSql As String
Dim varLO, varLL, varLS, varLTD, varTID As Variant

Set varLO = MyControl1
Set varLL = MyControl2
Set varLS = MyControl3
Set varLTD = MyControl4
Set varTID = MyControl5

Set strSql = "UPDATE tblMyTable " _
& "SET MyField1 = varLO, " _
& "MyField2 = varLL " _
& "MyField3 = varLS " _
& "MyField4 = varLTD " & _
"WHERE ((MyField5)= varTID));"

Set db = DBEngine(0)(0)
db.Execute strSql, dbFailOnError
Click to expand...


In addition to what Stefan has pointed out, do not use the "Set" keyword
except when you are assigning to an object variable. strSQL is not an
object, it's a String, so you should assign to it like this:

strSql = "UPDATE tblMyTable " _

No "Set". You probably only want to use the Set keyword on one line in the
above:
Set db = DBEngine(0)(0)
Click to expand...

That is correct, as db is an object variable.
 
--
Thanks for your help!
Walter


Stefan Hoffmann said:
hi Walter,

In VBA each variable in such a list without an explicit As Type is
declared automatically as Variant. E.g.

Dim a, b, c As Long

declares a and b as Variant and c as Long.
Clerification: "As Variant" is unneccessary?

This is normally not necessary.

A

MsgBox strSQL

will show you your error. Use

" field = " & MyControl1.Value

instead.
I'm not sure what you mean here.


mfG
--> stefan <--
Click to expand...
 
I removed the variables and now I'm getting "expected end of statement
error". How is the proper way to use the quotes?


strSql = "UPDATE tblMyTable " _
& "SET MyField1 = " & MyControl1.Value," _
& "MyField2 = " & MyControl2.Value" _
& "MyField3 = " & MyControl3.Value" _
& "MyField4 = " MyControl4.Value" _
& "WHERE ((MyField5)= " & MyControl5.Value));"
 
strSql = "UPDATE tblMyTable " _
& "SET MyField1 = " & MyControl1.Value & ", " _
& "MyField2 = " & MyControl2.Value & ", " _
& "MyField3 = " & MyControl3.Value & ", " _
& "MyField4 = " & MyControl4.Value & ", " _
& "WHERE ((MyField5)= " & MyControl5.Value & "));"

although all you really need is

strSql = "UPDATE tblMyTable " _
& "SET MyField1 = " & MyControl1.Value & ", " _
& "MyField2 = " & MyControl2.Value & ", " _
& "MyField3 = " & MyControl3.Value & ", " _
& "MyField4 = " & MyControl4.Value & ", " _
& "WHERE MyField5= " & MyControl5.Value
 
That SQL assumes that all five fields are numeric. I'm guessing that two of
your fields are actually text.

If the text value will never have an apostrophe in it, you can use

& "MyField2 = '" & MyControl2.Value & "', " _

Exagerated for clarity, that's

& "MyField2 = ' " & MyControl2.Value & " ', " _

If the text value may have an apostrophe in it, but will never have double
quotes in it, you can use

& "MyField2 = """ & MyControl2.Value & """, " _

That's three double quotes in a row before and after.

If there's a chance that the text may have both apostrophes and double
quotes in it, try

& "MyField2 = '" & Replace(MyControl2.Value, "'", "''") & "', " _

Again exagerated for clarity, that's

& "MyField2 = ' " & Replace(MyControl2.Value, " ' ", " ' ' ") & " ', " _

Incidentally, I just noticed an error in what I posted earlier. There's an
extra comma there. It should be:

strSql = "UPDATE tblMyTable " _
& "SET MyField1 = " & MyControl1.Value & ", " _
& "MyField2 = " & MyControl2.Value & ", " _
& "MyField3 = " & MyControl3.Value & ", " _
& "MyField4 = " & MyControl4.Value & " " _
& "WHERE MyField5= " & MyControl5.Value

If after making all those changes you still have problems, put a

Debug.Print strSQL

after your assignment statement. After the code runs, go to the Immediate
window (Ctrl-G) and check what's actually in the statement you're trying to
run.
 
Back
Top