Access VBA Loop (Not Responding)

Question

I am looping through a recordset to carry out some basic functions or edits.

Usually with recordsets with more than 50 records, access will stop responding.

I have me.repaint before the loop command but the window always freezes and the access title bar shows: ...(Not Responding).

Any idea how to get around this?

Thanks.

Dave.

EDIT: Added Loop Code

If Me.Dirty = True Then Me.Dirty = False
Dim rs As DAO.Recordset
Set rs = Me.Guardians_Subform1.Form.Recordset

Dim strFirstName, strLastName As String

If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst 

    Do Until rs.EOF = True     
rs.Edit
    strFirstName = Trim(StrConv(rs!FirstName, 3))
    strLastName = Trim(StrConv(rs!LastName, 3))
    If rs!FirstName <> strFirstName Then
    rs!FirstName = strFirstName
    End If

    If rs!LastName <> strLastName Then
    rs!LastName = strLastName
    End If

     rs.Update
       rs.MoveNext
Me.Repaint
    Loop  
Else
    MsgBox "There are no records in the recordset."
End If
Set rs = Nothing

Hi Dave could you provide the code for this please. It will be easier for us to help you. — psychicebola
– psychicebola, Commented Nov 2, 2015 at 9:18
Also, access should be able to handle recordsets up to 1gig in size. How big are your records? — Jens
– Jens, Commented Nov 2, 2015 at 9:21
The last recordset had less than 100 records. I've added the loop code. Thanks! — Dave B
– Dave B, Commented Nov 2, 2015 at 9:24
Why not just run a single update statement to trim the field values? — Nick.Mc
– Nick.Mc, Commented Nov 2, 2015 at 13:20

PhilS · Accepted Answer · 2015-11-02 12:52:25Z

4

You need to call the DoEvents-Function within the loop to pass control to the operating system to redraw your Access-GUI and to process any other Window-Messages that might need processing. By that the application will not be marked as "Not responding" in the Task Manager and the Title Bar.

Do Until rs.EOF = True
  [...]
  rs.MoveNext
  DoEvents
Loop

There is a small performance trade off. If not calling DoEvents, the total execution time for the loop will be a little shorter, but Access will do nothing else then process your loop. Therefore it seems to be not responding.

answered Nov 2, 2015 at 12:52

PhilS

1,66114 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

acr_scout · Accepted Answer · 2015-11-03 15:20:26Z

4

As others have pointed out you can use the DoEvents to release your processor to do other actions before continuing. When I use the DoEvents in a loop I use a counter.

Dim iCounter as Integer

Do Until
' some code here
   iCounter=iCounter+1
   If iCounter = 100 then
      DoEvents
      iCounter=0
   End if
Loop

This keeps the DoEvents from firing too often and causing your overall code to slow. Adjust the counter to whatever iteration you find appropriate.

answered Nov 3, 2015 at 15:20

acr_scout

5791 gold badge5 silver badges27 bronze badges

Comments

Andre · Accepted Answer · 2015-11-02 12:26:55Z

3

It is not the best approach to do such edits with a recordset loop. An UPDATE query is much more efficient.

e.g.

UPDATE tblGuardians
SET FirstName = Trim(StrConv(FirstName, 3))
WHERE StrComp(FirstName, Trim(StrConv(FirstName, 3)), 0) <> 0

and the same for LastName.

This uses StrComp instead of a simple <> comparison, because the latter is case-insensitive. The third parameter 0 = vbBinaryCompare.

answered Nov 2, 2015 at 12:26

Andre

27.7k7 gold badges43 silver badges85 bronze badges

3 Comments

PhilS Over a year ago

I do agree, a single SQL statement is usually much better than looping through a recordset for editing multiple records. - But this answer does not address the general issue of Access freezing while processing a loop.

Dave B Over a year ago

Thanks! So I should be doing something like: DoCmd.RunSQL "UPDATE tblGuardians SET Firstname = FirstName = Trim(StrConv(FirstName, 3)) WHERE ID =" & rs![ID] & ";"

Andre Over a year ago

@DaveB: No, this query doesn't run in the recordset loop, that's the point. It updates all records at once. This query (plus the one for LastName) are a replacement for your Loop code.

Collectives™ on Stack Overflow

Access VBA Loop (Not Responding)

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related