object required Walter 11/19/2008 1:08:06 PM 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 -- Thanks for your help! Walter

Re: object required Stefan Hoffmann <ste5an[ at ]ste5an.de> 11/19/2008 1:15:46 PM hi Walter, Walter wrote: [Quoted Text] > 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 <--

Re: object required "Dirk Goldgar" <dg[ at ]NOdataSPAMgnostics.com.invalid> 11/19/2008 3:47:08 PM "Walter" <Walter[ at ]discussions.microsoft.com> wrote in message news:266850D5-0352-4D39-B877-8789B7F2EDA0[ at ]microsoft.com... [Quoted Text] > 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. -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup)

Re: object required Walter 11/19/2008 5:13:01 PM -- Thanks for your help! Walter "Stefan Hoffmann" wrote: [Quoted Text] > hi Walter, > > Walter wrote: > > 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. > Clerification: "As Variant" is unneccessary? > > > 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. > I'm not sure what you mean here. > > > mfG > --> stefan <-- >

Re: object required Walter 11/19/2008 5:49:01 PM 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));" -- Thanks for your help! Walter "Stefan Hoffmann" wrote: [Quoted Text] > hi Walter, > > Walter wrote: > > 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 <-- >

Re: object required "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/19/2008 6:01:37 PM 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 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Walter" <Walter[ at ]discussions.microsoft.com> wrote in message news:BBF159F5-7BC7-4566-A334-BCC0BE35B5E2[ at ]microsoft.com... [Quoted Text] >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));" > -- > Thanks for your help! > Walter > > > "Stefan Hoffmann" wrote: > >> hi Walter, >> >> Walter wrote: >> > 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 <-- >>

Re: object required Walter 11/19/2008 9:26:09 PM Thanks! That worked but now I'm getting a "Too few parameters. Expected 2 " error. -- Thanks for your help! Walter "Douglas J. Steele" wrote: [Quoted Text] > 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 > > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no e-mails, please!) > > > "Walter" <Walter[ at ]discussions.microsoft.com> wrote in message > news:BBF159F5-7BC7-4566-A334-BCC0BE35B5E2[ at ]microsoft.com... > >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));" > > -- > > Thanks for your help! > > Walter > > > > > > "Stefan Hoffmann" wrote: > > > >> hi Walter, > >> > >> Walter wrote: > >> > 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 <-- > >> > > >

Re: object required "Douglas J. Steele" <NOSPAM_djsteele[ at ]NOSPAM_gmail.com> 11/19/2008 10:29:52 PM 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. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Walter" <Walter[ at ]discussions.microsoft.com> wrote in message news:2F527E1F-BF52-41AB-9FE0-72BE331AB630[ at ]microsoft.com... [Quoted Text] > Thanks! That worked but now I'm getting a "Too few parameters. Expected 2 > " > error. > -- > Thanks for your help! > Walter > > > "Douglas J. Steele" wrote: > >> 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 >> >> >> -- >> Doug Steele, Microsoft Access MVP >> http://I.Am/DougSteele >> (no e-mails, please!) >> >> >> "Walter" <Walter[ at ]discussions.microsoft.com> wrote in message >> news:BBF159F5-7BC7-4566-A334-BCC0BE35B5E2[ at ]microsoft.com... >> >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));" >> > -- >> > Thanks for your help! >> > Walter >> > >> > >> > "Stefan Hoffmann" wrote: >> > >> >> hi Walter, >> >> >> >> Walter wrote: >> >> > 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 <-- >> >> >> >> >>

Re: object required Walter 11/19/2008 11:40:06 PM Perfect! There were 3 text fields and 1 date which I had already figured out. -- Thanks for your help! Walter "Douglas J. Steele" wrote: [Quoted Text] > 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. > > -- > Doug Steele, Microsoft Access MVP > http://I.Am/DougSteele > (no private e-mails, please) > > > "Walter" <Walter[ at ]discussions.microsoft.com> wrote in message > news:2F527E1F-BF52-41AB-9FE0-72BE331AB630[ at ]microsoft.com... > > Thanks! That worked but now I'm getting a "Too few parameters. Expected 2 > > " > > error. > > -- > > Thanks for your help! > > Walter > > > > > > "Douglas J. Steele" wrote: > > > >> 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 > >> > >> > >> -- > >> Doug Steele, Microsoft Access MVP > >> http://I.Am/DougSteele > >> (no e-mails, please!) > >> > >> > >> "Walter" <Walter[ at ]discussions.microsoft.com> wrote in message > >> news:BBF159F5-7BC7-4566-A334-BCC0BE35B5E2[ at ]microsoft.com... > >> >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));" > >> > -- > >> > Thanks for your help! > >> > Walter > >> > > >> > > >> > "Stefan Hoffmann" wrote: > >> > > >> >> hi Walter, > >> >> > >> >> Walter wrote: > >> >> > 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 <-- > >> >> > >> > >> > >> > > >