Argthtjtr

In a database under my maintenance (MS Access 2010) I use a VBA procedure for cleaning up records, concretely: setting values in a field from "yes" to "no". The procedure loops through all records and sets the values in the respective field as required.

My database has about 900 records so far. Not too many, one should think.

My problem: the VBA procedure operates very slowly. On my current machine I have to wait for about 10 seconds until the loop has gone through the 900 records. That's impractical in everyday work.

What I need: I am looking for ways to speed this up, either through improvements to the code, or through a completely different approach.

Here is my procedure:

Private Sub WipeSelectionMarks_Click()



'Remove any filter that might be set to reduce the number of records in

'the form - We want here to operate on all records in the database!



   Me.Filter = ""

   Me.FilterOn = False

    'Don't know if we really need two commands here; but at least it works 



'Operate on a recordset that holds all the records displayed in the form

   Dim rs As DAO.Recordset

   Set rs = Me.RecordsetClone

   rs.MoveFirst

   Do Until rs.EOF

      With rs

        .Edit

        !Int_IwSelectMove = False

        .Update

      End With

      rs.MoveNext

   Loop



'Message box with info what has been done 

   MsgBox "A total of " & rs.RecordCount & " records were updated by wiping the Move mark"



'Cleaning up

   Set rs = Nothing

   Me.Refresh



 End Sub

Note 1: if a solution would be using an SQL command instead, I will be grateful for practical hints or examples. I use SQL commands at many places, still, getting put on the right track here would be helpful.

Note 2: Or can the VBA procedure be rewritten in a way that only records where the field in question has value "yes" are processed (these are usually only 20-30 of the 900), and those with "no" are left out?

You can use the UPDATE command:

CurrentDb.Execute "UPDATE YourTable SET Int_IwSelectMove = False"

can the VBA procedure be rewritten in a way that only records where
the field in question has value "yes" are processed

Indeed, and that may very well be the fastest method, as you will not have to requery the form:

   With rs

       Do Until .EOF

           If !Int_IwSelectMove.Value = True Then

               .Edit

               !Int_IwSelectMove = False

               .Update

           End If

           .MoveNext

       Loop

       .Close

   End With

Or you could use FindFirst or a filtered recordset. Running SQL on the recordset of a form is usually the last option.