Processing Through a Loop Question

[nag9127]

I have a puzzling loop question. The code is below. The code inserts information which exists in the InventoryTransferForm into a table (InventoryTransferTable) and then proceeds to perform data manipulations of the form data to master data existing in a third table (ItemTable). The code works perfectly as long as there are no duplicate items in the InventoryTransferForm. The strange part is that the insertion part of this code handles the duplication properly (each record exists in the InventoryTransferTable and any duplicate items are listed with each record's data), while the data manipulation part of the code goes back to the first instance of the item in the form (in spite of the fact that the code is within the loop) and runs the manipulation over again on that instance rather than performing the manipulation using the duplicated (second) instance. When I say duplicated, I don't mean that all the data is duplicated, I just mean that the item number appears twice in the original form with different data attached. Sorry if I haven't explained this too well. I will be happy to further explain if needed. Do I need a requery or something to purge out processed records? I just don't understand why the loop works in one area but not in the other! Thanks for any help!

Dim strSQL As String
Dim CurDB As Database
Dim rs As recordset

Set CurDB = CurrentDb
Set rs = Forms.InventoryTransferForm.RecordsetClone

Do Until rs.EOF

    strSQL = ""
    strSQL = strSQL & "INSERT INTO InventoryTransferTable"
    strSQL = strSQL & "([ItemTransferred], [Description], [UnitOfMeasure], [TransferredQuantity],"
    strSQL = strSQL & " [TransferredTotalCost], [TransferredUnitCost])"
    strSQL = strSQL & "VALUES ('" & rs!ItemProduced & "','" & rs!ItemProducedDescription & "','" & rs!UnitOfMeasure & "','" & rs!QuantityProduced & "',"
    strSQL = strSQL & "'" & rs!ProcessedBatchCost & "','" & rs!ProcessedBatchUnitCost & "')"
    DoCmd.RunSQL strSQL

    DoCmd.RunSQL "UPDATE ItemTable SET LastQuantityOnHand = QuantityOnHand" & _
    " WHERE (Item = Forms!InventoryTransferForm.RecordsetClone.ItemProduced)"

    DoCmd.RunSQL "UPDATE ItemTable SET QuantityOnHand = QuantityOnHand - QuantityProduced" & _
    " WHERE (Item = Forms!InventoryTransferForm.RecordsetClone.ItemProduced)"

    DoCmd.RunSQL "UPDATE ItemTable SET LastTotalCostOnHand = TotalCostOnHand" & _
    " WHERE (Item = Forms!InventoryTransferForm.RecordsetClone.ItemProduced)"

    DoCmd.RunSQL "UPDATE ItemTable SET TotalCostOnHand = TotalCostOnHand - ProcessedBatchCost" & _
    " WHERE (Item = Forms!InventoryTransferForm.RecordsetClone.ItemProduced)"

    DoCmd.RunSQL "UPDATE ItemTable SET CurrentCost = 0" & _
    " WHERE ((Item = Forms!InventoryTransferForm.RecordsetClone.ItemProduced) And (QuantityOnHand = 0))"
    
    DoCmd.RunSQL "UPDATE ItemTable SET CurrentCost = TotalCostOnHand/QuantityOnHand" & _
    " WHERE ((Item = Forms!InventoryTransferForm.RecordsetClone.ItemProduced) And (QuantityOnHand <> 0))"
    
    DoCmd.RunSQL "UPDATE ProductionBatchMasterTable SET IsTransferred = -1" & _
    " WHERE (BatchID = Forms!InventoryTransferForm.RecordsetClone.BatchID)"

    DoCmd.RunSQL "UPDATE ProductionBatchMasterTable SET TransferredDate = Now()" & _
    " WHERE (BatchID = Forms!InventoryTransferForm.RecordsetClone.BatchID)"
    
    rs.MoveNext

Loop

rs.Close
Set rs = Nothing

 

[JoeAtWork]

The code works perfectly as long as there are no duplicate items in the InventoryTransferForm.

And what is it doing wrong when there are duplicate items?

 

[nag9127]

When the process encounters a duplicate item number in the form, it inserts this second record correctly in the InventoryTransferTable along with all the related data from the second instance (the duplicate) of the item number, but when it processes the data in the ItemTable it reuses the data from the first instance of that item number rather than the data from the second instance. In other words the data from the first instance of the item number is processed twice rather than the first instance once and the second instance once.

 

[PHV]

Replace this:
Set rs = Forms.InventoryTransferForm.RecordsetClone
with this ?
Set rs = Forms.InventoryTransferForm.Recordset

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[TheAceMan1]

How are ya nag9127 . . .

[blue][purple]Change:[/purple]
   " WHERE (Item = Forms!InventoryTransferForm.RecordsetClone.ItemProduced)"
[purple]To:[/purple]
" WHERE ([Item] = " & rs.ItemProduced)[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[nag9127]

PHV:

I'll give that a try and let you know.

I was thinking I might also have the form requery which would cause the processed record to drop out, and then instead of using MoveNext, i would use MoveFirst and have the loop continue until the recordcount is zero.

 

[nag9127]

I was able to resolve this using the Requery and MoveFirst method with the RecordCount going down to zero. Thanks for your help.