Access VBA Loop (Not Responding)

问题:

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 

回答1:

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.

回答2:

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.

回答3:

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.