Looping thru a Form's records vs a Recordset-CodePudding

I'm wanting to loop over each record in a query when I click a button on a form. The recordsource of the form is the query. I've seen the typical recordset loops, but is there any reason why using Me! and DoCmd.GoToRecord syntax like this is a bad practice?

Private Sub cmdRefresh_Click()
    Dim RecordCount
    DoCmd.GoToRecord , , acLast
    RecordCount = Me.CurrentRecord
    
    If RecordCount Then
        DoCmd.GoToRecord , , acFirst
        Do Until Me.CurrentRecord >= RecordCount
            tbRefresh = "Searching for new RMDs... " & Int((Me.CurrentRecord / RecordCount) * 100) & "%"
            Me![RMD_Met] = RMDCalculate
            
            DoCmd.GoToRecord , , acNext
            
            DoEvents
        Loop
    End If
    
    Me.Requery
    tbRefresh = "Last Update: " & Date
End Sub

CodePudding user response：

there are as always in Access several approaches to acheiving a task.

However, the concept of data processing means that you can write code, and have it operate against data.

So, if you spend time money buidling some complex update routine? What happens if you NOW want to use that code in anohter form? You can't if you move the form around.

Also, if you have say a few records - 5-10, then you can of course write code for the form to move to the next record. But this is VERY slow, since the form disaplay and upate has to come along for the ride.

So, in place of a nice clean and VERY fast running code? Your update speed is now limited to how fast your screens can update!

Worse yet, you often might have some special logic in a form when you move to the next reocrd - (especially the on-current event). So now, you trying to have two sets of code run (your simple update code run), and a whole more boatload of other forms events etc that might fire. And even if they don't, why have a form do all of that updating which is slow?

So, you also note that you often see code use recordSetClone.

eg this:

Dim rstData     as DAO.RecordSet

set rstData  = me.RecordSetClone

rstData.MoveFirst
do while rstData.EOF = False
   ' process records
   rstData.MoveNext
loop

So, in above, recordset clone is great, because the current rocord you are on and viewing does NOT change. So, recordSetClone is the SAME data, but it allows you to loop, process, and move and work with the data, but the form display does not change nor care.

However, if you do this:

dim rstData   as DAO.RecordSet
set rstData = me.RecordSet

If you now use movenext etc. on above? Then the form display will follow the movement.

As noted, this MORE then 10,000 times slower!!!

I mean, if I want process some data, no need to drag along some huge sandbag of a form for such data processing.

As noted, you also in most cases gain flexiblity.

Such kinds of update routines can run independant of the form.

So, some routine to process invocies or payroll or whatever? You don't even require particular form to be opened. So the idea here is write routines and code that is independent of the forms (when it makes sense to do so).

So, a form is for the USER and interacting with the user.

Recordset processing is for computer code and a process you need to run, and such record by record processing has very little to do with some form for data entry. So no need to use some data entry form for processing of a bunch of records.

So you almost never want to use form movement over reocrds if each reocrd is not going to be interacted with by the user.

I mean, can you imagine your bank machine flashing away on the screen as it processes your transaction?

User interface things are done by forms.

Data processing and row processing of many records, or even just a few does not require a form to "move" along, and such designs tend to really start to fall apart over time, as then small code changes to such update routines now ALSO have to deal with a huge complex form event model.

So, such designs are not a bad idea, they are REALLY bad.