Run-time error '424': Object Required 2

[lbigk]

I am trying to execute the following code:
Set rstQt = dbs.OpenRecordset("SELECT tmpWeeklyLF.State, tmpWeeklyLF.Year, tmpWeeklyLF.DP1, tmpWeeklyLF.Est1, tmpWeeklyLF.DP2, tmpWeeklyLF.Est2, tmpWeeklyLF.DP3, tmpWeeklyLF.Est3, tmpWeeklyLF.DP4, tmpWeeklyLF.Est4, tmpWeeklyLF.DP5, tmpWeeklyLF.Est5, tmpWeeklyLF.DP6, tmpWeeklyLF.Est6, tmpWeeklyLF.DP7, tmpWeeklyLF.Est7, tmpWeeklyLF.DP8, tmpWeeklyLF.Est8, tmpWeeklyLF.DP9, tmpWeeklyLF.Est9, tmpWeeklyLF.DP10, tmpWeeklyLF.Est10, tmpWeeklyLF.DP11, tmpWeeklyLF.Est11, tmpWeeklyLF.DP12, tmpWeeklyLF.Est12 FROM tmpWeeklyLF;")

'Update tblWF_Data Quantity from rstQt to the corresponding year, state, and week

rstQt.MoveFirst
If rstQt.RecordCount > 0 Then
Do While Not rstQt.EOF
'Update quantities from Estimates
For n = 1 To 12
'If n=1 qty=EstJan where week=JanDP
strQbyM = "rstQt![" & "Est" & n & "]"
strMbyM = "rstQt![" & "DP" & n & "]"

DoCmd.RunSQL ("UPDATE tblWF_Data SET tblWF_Data.Qty = " & strQbyM & " WHERE (((tblWF_Data.Year)= '" & rstQ!Year & "') AND ((tblWF_Data.State)='" & rstQ!State & "') AND ((tblWF_Data.WeekNbr)= " & strMbyM & "));")

Next n
rstQt.MoveNext
Loop
Else
'No records found
End If

When the code gets to DoCmd.RunSQL, I am getting an "Object Required" error. It doesn't want to recognize strQbyM or strMbyM. I am currently using Access 2003, any assistance would be appreciated.

Thank you,
lbigk

 

[RoyVidar]

You're entering the string "rstQt![Est1]" into the SQL string, try

[tt]strQbyM = rstQt.fields("Est" & n).value[/tt]

likewise with the other string.

Roy-Vidar

 

[Remou]

You may mean:

            strQbyM = rstQt.Fields("Est" & n)
            strMbyM = rstQt.Fields("DP" & n)

It looks suspiciously likje this database is not normalized. YesNo?

http://lessthandot.com

 

[Remou]

*Sigh* Too Slow

http://lessthandot.com

 

[lbigk]

Thank you, you are the best!