object required

[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

 

[Stefan Hoffmann]

hi Walter,

Dim strSql As String
Dim varLO, varLL, varLS, varLTD, varTID As Variant

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

This is normally not necessary.

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

A

MsgBox strSQL

will show you your error. Use

" field = " & MyControl1.Value

instead.



mfG
--> stefan <--

 

[Dirk Goldgar]

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

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)

That is correct, as db is an object variable.

 

[Walter]

--
Thanks for your help!
Walter

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 <--

 

[Walter]

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));"

 

[Douglas J. Steele]

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

 

[Walter]

Thanks! That worked but now I'm getting a "Too few parameters. Expected 2 "
error.

 

[Douglas J. Steele]

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.

 

[Walter]

Perfect! There were 3 text fields and 1 date which I had already figured out.