Do Loop with 2 different recordsets

[jamarogers]

Hi All and thanks for this forum. I am an Old Newbie, trying to keep out the cobwebs in retirement. Been about 10 years since I dabbled into VBA...here's my problem. I'm writing a runtime app for my neighbor's small business and all is progressing relatively well, but slow. I'm stuck on something I've never done before. I wish to update InventoryTbl when I receive new purchases (POItemsTbl). I have a POItemsForm bound to POItemsTbl, which has a button to "Receive all Items". I wish this button to execute code to update Quantity in InventoryTbl. This would be the existing InventoryQuantity + POItemsQty. Logic would be similar to the following, but I have no idea how to express programmatically:

Do
Look at each record in POItemsTbl, where "[POID] = Forms![POs]![POItems]![POID]" (Many records will exist here)
On found record1, Remember value for "POItems.Qty" as PQ
Now look for related record in InventoryTbl, where "InventoryTbl.Name = Forms![POs]![POItems]![POItName]" (Only 1 record exists here)
On found OnlyRecord, Remember value for "InventoryTbl.Qty as IQ
InventoryTbl.Qty = PQ + IQ
Update
.movenext
Loop

Logically, it seems the above should work...can someone please advise me of how to write this code? I've spent days looking at Case/Select, If/Then, Do/Loop and the more I read the more confused I get. I've attached the relationship map should anyone need to review. Thanks for any help or advise.
Jake

[June7]

Suggest you review this before proceeding http://allenbrowne.com/AppInventory.html

[ItsMe]

I second June7's comment. You shouldn't have fields with names like "Quantity" because this is something that always changes relative to a calculation of fields that already exist within your table. The result is you have a need to write compex code to UPDATE your table with the calculation.

Aside from that issue. Addressing ONLY the title of the thread and how to loop through two recordsets similtaneaously, here is an example. I use it to give the user to edit information they already produced.

The user is viewing a form and the form displays a result of their actions. The user ticks the check box to be removed from their selection. The code grabs the form's recordset and loops through. When it finds a record the user chose to edit the code then loops through the second recordset to do the necesary editing.

    
 

    Dim lngPrime As Long
    Dim rcdClone As Recordset
    Set rcdClone = frm.RecordsetClone
    
    Dim db As DAO.Database
    Dim rcdFind As DAO.Recordset
    Set db = CurrentDb
    Set rcdFind = db.OpenRecordset("qryShipOut")
    'Find a key for the DAO to select items for editing"
    rcdClone.FindFirst "[In] = -1"
    lngPrime = rcdClone![Prime] 'Here is the prime key
        If Not rcdClone.NoMatch Then
        Do Until rcdClone.NoMatch
        rcdFind.MoveLast    'Populate the recordset
        rcdFind.FindFirst "[Prime] = " & lngPrime
        
            rcdFind.Edit
            rcdFind![Hold] = 0
            rcdFind![Out] = 0
            rcdFind![OutDate] = Null
            rcdFind![ShipOrder] = ""
            rcdFind![OutID] = 0
            rcdFind![FillerDest] = gintFillerDest
            rcdFind.Update
            rcdClone.FindNext "[In] = -1"
            lngPrime = rcdClone![Prime]
            
        Loop
        
        Else
        MsgBox "Please select at least one item to be removed from the Outbound BOL", vbInformation, "No Item Selected"
        End If
    rcdFind.Close
    Set rcdFind = Nothing
    rcdClone.Close
    Set rcdClone = Nothing

IMO it is much easier to calculate quantities in a report or form. I usually use two loops when I need to get information from one place to another. Like one list from an Email to a list in my DB or An order recived from a customer to a data recordset within my DB.

[Dal Jeanis]

This is a classic inventory situation. It definitely would be worth your time to review a few sample databases that handle it in different ways, before finalizing your own architecture.

There are two basic approaches -
1) Don't store the current inventory quantity, always calculate it fresh from whatever transactions are in the database.
2) Periodically calculate an inventory, and archive any transactions that went into making it.

The first is a database-driven approach, the second an accounting-driven approach. I tend to prefer the second, since my background includes lots of feedback from the auditing side of the business.

By the way, in the second approach, I prefer a "virtual" archiving method such as a flag that indicates the record has been processed. The actual archiving would happen after some point where the detail records were no longer needed for detail reporting. This avoids having to UNION the current transaction details to the archived transaction details.

In either approach, please remember to include a method of reconciling the calculated inventory against the actual inventory, which in the real world will seldom match precisely. Provide a simple form for entering inventory adjustment transactions for this purpose - preferably by entering the actual count as/of a certain date and having the form calculate and create the adjustment transaction, if any is required.

[jamarogers]

Thanks to everyone for such a quick and detailed response...I've got a lot to digest and think about here, but I'll be back to followup or close this thread with resolution...
Jake