Runtime Error 424 "Object Required" HELP!!

[georgew]

I am trying to update a table based on the contents of another table. I am using some loops to bring me through the data in order to generate shipping labels using quantities and # of cartons required for each sku.

I am sure the solution is simple.....

This is the code and I have highlighted the prolem areas with "***".

I need to know how to define/declare?? the objects so I can use the fields in the table in the loops..

Sub AddValues()
Dim cnn1 As ADODB.Connection
Dim cat1 As New ADOX.Catalog
Dim rst1 As New ADODB.Recordset
Dim rst2 As New ADODB.Recordset
Dim CountQTY
Dim CountCARTONS

'Set context for populating new table (tblLABELS)
Set cnn1 = CurrentProject.Connection
Set cat1.ActiveConnection = cnn1
Set rst1.ActiveConnection = cnn1
Set rst2.ActiveConnection = cnn1

'Open recordsets based on new and original tables
rst1.Open "tblLABELS", , adOpenKeyset, _
adLockOptimistic, adCmdTable
rst2.Open "tblLABELDATA", cnn1, adOpenForwardOnly, _
adLockReadOnly, adCmdTable

'Loop through recordsets to copy from original to new table
With rst1
Do Until rst2.EOF
'QTY Loop Start
CountQTY = 1
Do Until CountQTY > 2 '***I want to change this "2" TO USE THE FIELD VALUE "tblLABELDATA!QTY"
'Like this??: Do Until CountQTY > tblLABELDATA!QTY
'CARTON Loop Start
CountCARTONS = 1
Do Until CountCARTONS > 5 '***I want to change this "5" TO USE THE FIELD VALUE "tblLABELDATA!CARTONS"
.AddNew
.Fields(0) = rst2.Fields(8)
.Fields(1) = rst2.Fields(11)
.Fields(2) = CountQTY & " Of " & 2 '***I want to change this "2" TO USE THE FIELD VALUE tblLABELDATA!QTY
.Fields(3) = CountCARTONS & " Of " & 5 '***I want to change this "2" TO USE THE FIELD VALUE tblLABELDATA!CARTONS
.Update
'CARTON Loop End
CountCARTONS = CountCARTONS + 1
Loop
'QTY Loop End
CountQTY = CountQTY + 1
Loop
rst2.MoveNext
Loop
End With

End Sub



Follow Ups:

 

[RickSpr]

You're almost there. You don't use the table-name!field-name, but the recordset-name!field-name. So your changes are: rst2!QTY and rst2!CARTONS.

Why are you using the Fields(0) syntax? Wouldn't it be easier to read using the actual field names instead of numbers? Or didn't you know you could use Fields("CARTONS&quot, for example?
Rick Sprague