Looping through records

[chappy68]

I need help. I have never dealt with a LOOP before so I am completely in the dark. I searched the forum but am still unclear. Let me summarize what I am doing and what I want to do.

On frmMainMenu I use a tab control that has a search on one of the tabs. The user enters multiple criteria. I setup a query (qrySearch) to deal with their selections and a form (frmSearch) to display the results of their search. I have placed a check box field in the table being searched. For example, I have ten records in the table. Seven of the records match search criteria. The user wants to change four of the records. Once they have checked the box for each of the four records they want to change, they click on the change button and it opens the last form (frmApplySearchChanges). There are only two fields I am allowing to be updated. They make their selections for the two fields and click "Apply Changes" button and that updates the fields for the four records to be changed.

From my reading of posts I think I need to do the loop on frmSearch when they click the "change" button. I guess that will create the recordset of the four records they want to change. Can I then use the recordset on another form to identify the records to be changed?

I hope this makes sense. All help appreciated. I have no code at this point. I don't even know where to start. All suggestions and tips very much appreciated.

 

[vbaInet]

When you want to update a batch of records from a form, think about the underlying record source first. You can fire an update query to update all the records that match the criteria.

 

[Galaxiom]

Are all the changes to the same value or must the user enter each one?

If they are all to the same values entered into a special box then use a query as suggested by vbaInet.

Otherwise the changes will simply be made automatically as they are entered into frmSearch though its bound RecordSource.

 

[chappy68]

Thanks,

I feel real stupid right now. I have been doing so much work using VBA that I completely stopped using normal Access functions. You both sent me in a direction that was so much simpler. All I did was use the query from the record source of the form. Used it in my update query a it worked great. What is funny is a year ago when I knew nothing about VBA at all, I would have done this immediately.

Thanks for sending me in the right direction. I think I should still learn loops just not today.

One more question. As I mentioned in the above fact pattern, I have a checkbox in my table that is being searched. Before I run a search, I am using an update query to set the value of the search checkbox in the table to null. As the table grows significantly larger, which it will, this seems like an inefficient method. Would you recommend another way to reset the value of the checkbox to NULL?

 

[Galaxiom]

Using the Update is about as fast as you can get on that task.

However, although it does make it easy to select the records for the query, since the checkbox is really just a flag to indicate which records are to be changed in the edit session, storing it in the table would be a bit of an anomaly.

The only ways I can think of to avoid this are fairly complex so probably best to stick with what you know.