Updating all records at once in a continuous form with cmd button instead of just the first record

[Kipandrews06]

I wanted to ask if it is possible to update multiple records in a continuous form at once. I currently have a button that updates the record how I want it to, but it only does it for 1 record at a time and not all records displayed within the form. Is this possible to do?



So just in a brief summary, I have an inventory/order database I'm working on. The form in question is a subform within a mainform. The main form is an order form and has 2 subforms.

Subform1 is a product list where they select the products they want to order
Subform2 is an "item cart" where the selected products go from subform1

So when I click a "submit order" button which is in subform2, I want it to update the product amounts from all the products in the cart. Basically adjusting the onhand inventory (deducting the qty ordered from each product). Like I said, my button does the first record perfectly, but I cant find anything that lets you do all the records at once. If a customer orders 25 products, I don't want them or myself to have to individually click a button for all 25 products. Surely there's something that can loop through all the records and adjust each one within the order? Here is my code so far:

 Private Sub cmdSubmit_Click()10        On Error GoTo cmdUpdateStock_Click_Error
20    If Me.Dirty Then Me.Dirty = False
      Dim rsTemp As DAO.Recordset
      Dim strSQL As String
      'Create a copy of this forms Recordset


30    Set rsTemp = Me.RecordsetClone
40    rsTemp.MoveFirst
50    Do Until rsTemp.EOF


      'Loop through all records and update records....
60    strSQL = "UPDATE tblProducts SET QtyAvailable = " & Me.NQA _
      & " WHERE ProductID = " & Me.ProductID & ";"
70            Debug.Print strSQL
80            CurrentDb.Execute strSQL, dbFailOnError


90    rsTemp.MoveNext
100   Loop
      'Release resources
110   MsgBox "Your order has been successfully submitted", vbInformation, "Success"
120   rsTemp.Close


130   Set rsTemp = Nothing


140   [Forms]![frmOrderDetails]![frmItemCartOrdersSubform].[Form].Requery


          
150       On Error GoTo 0
160       Exit Sub


cmdUpdateStock_Click_Error:


170       MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure cmdUpdateStock_Click, line " & Erl & "."


End Sub

I also have a txt box I put in the subform for referencing. It is set to not be visible but is the total of the QtyAvailable - QtyOrdered and is called "NQA" (New Qty Available) which is in the code and is the value I want the products to be adjusted to in tblProducts. Any suggestions or tips would be much appreciated. I really want to be able to get this working on all the records in the item cart without doing it individually.

Thanks,
Kip

[pbaldy]

Most of us would recommend against trying to maintain quantity on hand. We would calculate it from transactions. More here:

http://allenbrowne.com/AppInventory.html

To your issue, in your SQL you're referring to the form for values, and you're not changing the record the form is on. Try getting values from the recordset, like

rsTemp!NQA

[Kipandrews06]

@pBaldy thanks for the suggestions. I tried the rsTemp!NQA and it didn't work. The problem I believe with referring to the recordset is that the NQA is an unbound calculated field of AvailableQty - QtyOrdered. I was referencing that value of NQA and updating the table with QtyAvailable to the value of NQA. I'm going to look through the link you sent and see if that would work for me, but ideally I'd really like to keep the current system I have if I can get this to update all the records at once in the item cart for each order. I'm open to doing it a better way though, I just have to make it easy for the users because they are not tech saavy and I am trying to make it as pretty straightforward and easy as possible.

[ssanfu]

Would you post your dB?
Change any sensitive data, do a "Compact and Repair", then zip it.

[pbaldy]

You might try

rsTemp!AvailableQty - rsTemp!QtyOrdered

[Kipandrews06]

Sorry for the delayed response, I left my laptop at work yesterday so I couldn't do anything on the database. Anyways, I was going to post a sample of my database, but after I tried what @pBaldy recommended it fixed my problem and is now working great! I took out the NQA in my code and changed it to rsTemp!QtyAvailable - rsTemp!QtyOrdered and it still only updated the first record but with the wrong amount, it pulled the amount from another product on the list and changed it to that value. So then on the WHERE clause, I changed Me.ProductID to rsTemp!ProductID and it fixed everything and now updates all the product qty's on the list at the same time. So referring to the form was definitely where I was going wrong whereas it needed to be referring to the recordset. Thank you very much pBaldy! I appreciate the help!

[Kipandrews06]

and one last thing...I have a msgbox prompt through vba to pop up if someone in the item cart types in a qty that is greater than the qtyavailable. The problem I'm getting is that it is doing the same thing as my original problem in this thread. It is using only the first record (or product) in the cart as the QtyAvailable value for all records in the cart. So lets say I only have 2 in stock of Product A and I have 50 in stock of Product B, it displays the msgBox on Product B if I type in more than 2. How would I go about getting it to do each record individually? I'm not sure how to go about it since its not a SQL Update query. My current code for the msgbox prompt works perfect on the first item that gets added to the cart, but not the others. Here is my code:

 Private Sub QtyOrdered_AfterUpdate()    Dim QtyOrdered As Integer
    QtyOrdered = Me.QtyOrdered.Value


    Dim QtyAvailable As Integer
    QtyAvailable = [Forms]![frmOrderDetails]![frmProductListSubform].[Form]![QtyAvailable].Value


    If QtyOrdered <= QtyAvailable Then Exit Sub


    If QtyOrdered > QtyAvailable Then
        ' Why checking the result of the MsgBox when only one button (Ok) can be pressed (see below)?
        If MsgBox("The Qty ordered must be less than the amount in stock", vbOKOnly + vbCritical, "Insufficient Inventory") = vbOK Then
            Me.QtyOrdered.Value = 0
            Me.Requery
        End If
    End If
End Sub

[pbaldy]

You'll probably need a DLookup() with a criteria on product to pull the value from the table:

http://www.theaccessweb.com/general/gen0018.htm

[Kipandrews06]

Yup that fixed it! Thank you!

[pbaldy]

Happy to help! Sorry I wasn't clear in my initial response that all references using "Me" would need to be changed.