VBA Recordset problem

  • Thread starter Thread starter Steel Banana
  • Start date Start date
S

Steel Banana

Hi all!
When I run the code below, I get and error. The error I ‘m stuck on is
“Update or Cancel without AddNew or Edit†at the point noted in the code
below. What’s wrong? (I assume it's probably just syntax, but I'm not very
experienced)

The main plan here is to:
1)take a record from a query (qryLabelsPicture),
2)break the record down into multiple records and store the new records in
tblToteLabelsBreakdown (note that BinQty is the only field changed),
3)and move to the next record in the query and repeat the process.
Clear enough?

CODE START

DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * from tblLabels"
DoCmd.RunSQL "DELETE * from tblToteLabelsBreakdown"
DoCmd.TransferText acImportDelim, "SHIPLABL Import Specification",
"tblLabels", "C:\Shipping Labels\shiplabl.csv"

Dim db As Database
Dim loc As Recordset
Dim rec As Recordset

Set db = CurrentDb
Set loc = db.OpenRecordset("qryLabelsPicture")
Set rec = db.OpenRecordset("tblToteLabelsBreakdown")

loc.MoveFirst
While Not loc.EOF

While loc!QuantityOrdered > loc!ToteQty
rec.AddNew
rec!PartNumber = loc!PartNumber
rec!PartDescription = loc!PartDescription
rec!RevisionNumber = loc!RevisionNumber
rec!Tote = loc!Tote
rec!PrimeLocation = loc!PrimeLocation
rec!BinQty = loc!ToteQty
rec.Update
loc!QuantityOrdered = loc!QuantityOrdered - loc!ToteQty
‘<<ERROR HERE!!
Wend

If loc!QuantityOrdered > 0 Then
rec.AddNew
rec!PartNumber = loc!PartNumber
rec!PartDescription = loc!PartDescription
rec!RevisionNumber = loc!RevisionNumber
rec!Tote = loc!Tote
rec!PrimeLocation = loc!PrimeLocation
rec!BinQty = loc!QuantityOrdered
rec.Update
End If

loc.MoveNext
Wend

loc.Close
rec.Close
Set db = Nothing
DoCmd.SetWarnings True
DoCmd.OpenReport "rptLabels", acViewPreview

CODE END
 
Actually, no. John's answer was correct. Note that the last line refers to a
different recordset than the llines above it.
 
Back
Top