How loop through records in a subform and run a query

I have a sub-form on a main form.  The sub-form can have one or more records.  On the main form is a command button that when clicked I want to run a SQL statement that will update records in an unrelated table.

The VBA SQL code line is:  (At least I hope this code is right)

strSQL = "UPDATE tblCMTinventory SET tblCMTinventory.Used = True " & vbCrLf & "WHERE (((tblCMTinventory.ID)=[Forms]![subfrmCMTworkOrderGroup1Details]![cboNewSN]));"

But what do I need to add to the code to make it loop through the records in the sub-form?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Recordsets bound to forms are not meant to be used this way.  If you want to write a loop that processes a recordset, you would NOT use a form's recordset.  Use DAO (or ADO if you prefer) to open a recordset and read it.

In reality, you probably don't need to do this with a code loop at all.  Include the table/query on which the subform is based in your update query.  Make sure you include selection criteria so the query will be filtered to just the set of records you want to update.  Using an inner join to join to the recordsource of the subform will restrict the update to only the matching rows.  This is essentially what you would be doing with a code loop but it is infinitely more efficient since you are running only one update query and the query engine is managing the join process rather than using a DAO code loop or a clunky automation of a subform where you will run a separate update query for each row of the controlling recordsource.
You can loop through the subform by setting a DAO.Recordset to the subform  Recordset.
   Dim rs As DAO.Recordset
   Set rs = Me.subfrmCMTworkOrderGroup1Details.Form.Recordset
   If rs.RecordCount > 0 Then
      Do Until rs.EOF
         CurrentDb.Execute "UPDATE tblCMTinventory SET tblCMTinventory.Used = True " & _
                        "WHERE (((tblCMTinventory.ID)=" & rs!cboNewSN & "));"
   End If

Open in new window

SteveL13Author Commented:
Is not working.  I'm sure I posted bad information.

The table I'm trying to update is tblCMTinventory.
The field in the table I'm trying to update to true is "Used"
The subform is bound to tblCMTworkOrderGroup1
The field on the form that the match to the first table is cboNewSN

Is this enough information to straighten me out?  (at least on this topic)
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

ok so change subfrmCMTworkOrderGroup1Details to tblCMTworkOrderGroup1
Set rs = Me.tblCMTworkOrderGroup1.Form.Recordset

Open in new window

What's the name of the sub form ?
Do NOT create a code loop to run a separate query for each and every row in the subform's recordsource when by simply using a join (or possibly some criteria), you can run a single query and update all the rows.

CurrentDb.Execute "UPDATE tblCMTinventory Inner Join qYourQuery ON tblCMTinventory.ID = qYourQuery.NewSN  SET tblCMTinventory.Used = True;"


CurrentDb.Execute "UPDATE tblCMTinventory  SET tblCMTinventory.Used = True WHERE tblCMTinventory.ParentID = " & Me.ParentID  & ";"

In the first example, qYourQuery is controlling which records get updated.  In the second example, the ID of the parent record is controlling what gets updated.

If your subform never has more than a few rows, it really doesn't make a difference how inefficiently you do this task.   But, why not learn how to do it the correct way so you can add a tool to your arsenal?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

I would also recommend to not use a recordset loop like Pat said above. But depending on what you want to do you can of course use the form's recordset in the same way as a "normal" recordset. But if you loop through the form's recordset you would also trigger events of the form like Form_Current which can have other side-effects. This method is useful if you want the user to see which record is currently be processed. I use that sometimes for print purposes.

If you do not want to have these side-effects you can use the form's "RecordsetClone" property (not "Recordset.Clone", that creates a complete copy of the recordset). This is a clone of the form's recordset which Access automatically creates for each form where you can walk through the recordset without any visual effects on the form or triggering any events - that's the exact purpose of this object. Creating an additional recordset in VBA with the same data would be nonsense because you would need to load all the data again the form already contains.

But again: If there is no real need of looping through a recordset (not only in this case) you should always try to solve that with SQL instead, like Pat said also, which is much faster. Normally the user prefers "faster" if you ask him...;-)


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.