Do-loop only gets 25 records

[gnosis13]

The following code segement is behind a button on a form. There is a function call within the loop that changes some data on the form. This code works perfectly except that it only scrolls through 25 of the 2000+ records. Is there an easy way to fix this code without a rewrite?

Private Sub Command0_Click()
On Error GoTo MemFiles_ERR

Dim Endmark As String
Dim Currentmark As String
Dim varCustomer As String

DoCmd.GoToRecord , , acLast
Endmark = Me.Bookmark

DoCmd.GoToRecord , , acFirst

Do
Currentmark = Me.Bookmark

If Not (IsNull(txtCustomer)) Then
varCustomer = GetCustomer(txtCustomer)

If Not (varCustomer = &quot;<none>&quot Then
txtCustomer = varCustomer
End If
End If

If Currentmark = Endmark Then
Exit Do
End If

DoCmd.GoToRecord , , acNext
Loop

DoCmd.GoToRecord , , acFirst

MsgBox &quot;Data conversion complete&quot;

Exit Sub
MemFiles_ERR:
MsgBox &quot;Something hosed...&quot;
End Sub
A+, N+, MCP

 

[gnosis13]

Never mind...

If Not (StrComp(Currentmark, Endmark, 0)) Then
Exit Do
End If

fixed the loop. A+, N+, MCP

 

[scking]

Do you move through 2000+ records using DoCmd.GoToRecord,,acNext? It is not necessary to see those records go whizzing by to perform the update using DAO and the RecordsetClone, and you don't need to handle bookmarks because the updates are independent of the form bound record and the 'If Not rst.EOF' handles the checking for the end of the recordset. You could actually make a procedure that would not require the form to be open.

Private Sub Command0_Click()

Dim varCustomer As String
Dim rst As DAO.Recordset
Dim lngRecordCount As Long

On Error GoTo HandleErr

Set rst = Me.RecordsetClone
If rst.RecordCount <> 0 Then
rst.MoveLast
rst.MoveFirst
lngRecordCount = rst.RecordCount
Else
Exit Sub
End If

If MsgBox(&quot;You are about to update &quot; & lngRecordCount & &quot; records. Proceed&quot;, vbOkCancel) = vbOk Then
Do While Not rst.EOF
' Do your thing to the underlying
' record fields not to the control values.

If Not (IsNull(rst!Customer)) Then
varCustomer = GetCustomer(rst!Customer)

If Not (varCustomer = &quot;<none>&quot Then
rst.Edit
rst!Customer = varCustomer
rst!Update
End If
End If

DoEvents
rst.MoveNext
Loop
End If
MsgBox &quot;Data conversion complete&quot;

Exit Sub

Exit_Proc:
rst.Close
Set rst = Nothing
Me.Requery
Exit Sub

HandleErr:
If MsgBox(Err.Number & vbCrLf _
& Err.Description & vbCrLf
& &quot;Something hosed...&quot; & vbCrLf
& &quot;Do you want to debug this?&quot;, _
vbYesNo) = vbYes Then
Stop
Else
Resume Exit_Proc
Resume
End If

End Sub
----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[gnosis13]

scking, I originally put similar code together, not as good as yours, but I was working in someone elses database and he wanted me to keep it simple so that he could understand it on his level. I have saved your code for later use....thank you very much. A+, N+, MCP