Updating a recordset in a continuous form but skipping records that don't have a checkbox marked

[Kipandrews06]

I have a continuous form that has a checkbox for each record. Yes, it is bound to a table. Basically I need fix a vba code I have on a cmd button that loops through all the recordset and updates them. I posted a similar thread last week....... https://www.accessforums.net/showthread.php?t=85420

For this problem I need to do the same thing (update all the records within the recordset at once instead of just the first record or one record at a time), BUT I need to somehow make access skip any records in the recordset if the checkbox isn't selected. Currently my code loops through and updates all the records perfectly (if all the checkboxes are marked), but lets say one or two records are not checked, then it updates it with a value I don't want.

So just to explain better, its a product return form. All it is, is products for companies we store in a warehouse and they order them when they need them shipped out to tradeshows or certain events. After the show or event is over they ship them back to us and we store them back in the warehouse. So when we receive an order back, sometimes not everything is sent back for various reasons. So on the form I have you can pull up the order # and each product has the checkbox (ReturnedToStock) and you go through and check each one that was returned, then if its checked then the new stock quantity would be the value of QtyOrdered + QtyAvailable. The form I have lists the orderID (which is the recordset), the product code, qty that was ordered, the qty available in stock, the checkbox which is used as a "ReturnToStock" yes/no and finally the new stock qty if the product is returned. I had a code that updated each record individually, but I want to streamline it better to update them all at once so the code I have now is from the thread I posted above, I just changed the strSQL a tad to add rather than subtract. I have tried a million combinations of stuff, searched the web and cannot get the loop to skip over a record if the checkbox isn't selected. Let's say I have 6 products from an order and only 4 of them were returned. If I checked the box for the 4 products that were returned, I want it to update those records and not update or do anything for the records in the recordset that don't have the checkbox marked.



Here is a pic of my form to give you an idea of what I'm talking about:


Here is my current code that updates all of them correctly except for ones that don't have the checkbox marked:

 

Private Sub cmdUpdateStock_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 = " & rsTemp!QtyAvailable + rsTemp!QtyOrdered _
      & " WHERE ProductID = " & rsTemp!ProductID & ";"
70            Debug.Print strSQL
80            CurrentDb.Execute strSQL, dbFailOnError


90    rsTemp.MoveNext
100   Loop
      'Add resources back to stock
110   MsgBox "Product amounts were successfully returned to stock.", vbInformation, "Success"
120   rsTemp.Close


130   Set rsTemp = Nothing


140   Me.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've tried adding the IF statements like If Me.ReturnedToStock = -1 Then ..... or If Me.ReturnedToStock = 0 Then rstTemp.MoveNext and I can't get it to work. It keeps saying "There's no DO with Loop" which is frustrating cuz there clearly is a DO in the code. I guess (I'm not sure) that If statements can't be between the DO and Loop? Anyways, any suggestions on how to specify in my code to update all the records in the recordset that have "ReturnedToStock" checked and if a record does not have it checked then don't update and go to next record would be greatly appreciated!

One last disclaimer- This may be important to know (or it may not), but this form's record source is a qry. The strSQL update in the code adjusts the QtyAvailable in another table called "tblProducts" and on that table there is no checkbox. The checkbox is on qryProductOrders which is the recordset.

[Bob Fitz]

Perhaps I'm not understanding the requirement correctly but I don't think you need to loop through a recordset, just run an Update query with the appropriate value and criteria.

[pbaldy]

An If/End If block can be within the loop, but it must be completely contained by the loop. You would have:

If Me.ReturnedToStock = -1 Then
   60    strSQL = "UPDATE tblProducts SET QtyAvailable = " & rsTemp!QtyAvailable + rsTemp!QtyOrdered _
        & " WHERE ProductID = " & rsTemp!ProductID & ";"
   70            Debug.Print strSQL
   80            CurrentDb.Execute strSQL, dbFailOnError
End If

[Kipandrews06]

AAHHAA!! Thank you Paul, that's where I went wrong. I didn't put the End If in between the Do and Loop. Once I made that change it works perfect now. I hate how you can be so close to something and then the simplest thing like End If was the cause of all your problems. And stuff as simple as a comma or " has got me stuck for hours on end before too. Anyways, the code is working great now so thanks for helping me figure out where my problem was!

[pbaldy]

Happy to help! Sadly, the error when you do that isn't really descriptive.