cycle through each record until EOF

G

Guest

I have a command button on a form that opens a query which verifies
information on the current record. Depending on the criteria that is met,
certain check box(s) are checked off. This works fine so I don't want to mess
with it too much.

However, because I need to verify hundreds of records this takes too much
time. Can I include some code in that command button to verify the current
record, then move to the next record, etc. until it reaches the last record
in the table and then it stops. I thought a loop would work but I don't know
how to work it into my code.

This is the command button code:

Private Sub cmdVerifyScrubbedSupplierName123_Click()
On Error GoTo Err_cmdVerifyScrubbedSupplierName123_Click

Dim stDocName As String

stDocName = "mcrVerifyScrubbedSupplierName123"
DoCmd.RunMacro stDocName

If DCount("*", "qryVerifyScrubbedSupplierName1") > 0 Then

Me.Name1Verified = True
Me.OnEPLSDB1 = True

Else

Me.Name1Verified = 1
Me.OnEPLSDB1 = 0

End If

If DCount("*", "qryVerifyScrubbedSupplierName2") > 0 Then
Me.Name2Verified = True
Me.OnEPLSDB2 = True
Else

Me.Name2Verified = 1
Me.OnEPLSDB2 = 0
End If

If DCount("*", "qryVerifyScrubbedSupplierName3") > 0 Then
Me.Name3Verified = True
Me.OnEPLSDB3 = True
Else

Me.Name3Verified = 1
Me.OnEPLSDB3 = 0
End If

Exit_cmdVerifyScrubbedSupplierName123_Cl:
Exit Sub

Err_cmdVerifyScrubbedSupplierName123_Click:
MsgBox Err.Description
Resume Exit_cmdVerifyScrubbedSupplierName123_Cl

Exit Sub

End Sub

Thank you!!
 
G

Guest

A loop is a good thing.

Here is a way.

Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
Me.Bookmark = rst.Bookmark
'Your Code goes here
rst.MoveNext
Loop
set rst = Nothing
 
G

Guest

Nothing happens when I click on the command button. Did I put the code in
the wrong place?

Thanks,

Private Sub cmdVerifyAllScrubbedSupplierNames123_Click()

Dim stDocName As String
Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
Me.Bookmark = rst.Bookmark

On Error GoTo Err_cmdVerifyallScrubbedSupplierNames123_Click

stDocName = "mcrVerifyScrubbedSupplierName123"
DoCmd.RunMacro stDocName

If DCount("*", "qryVerifyScrubbedSupplierName1") > 0 Then

Me.Name1Verified = True
Me.OnEPLSDB1 = True

Else

Me.Name1Verified = 1
Me.OnEPLSDB1 = 0

End If

If DCount("*", "qryVerifyScrubbedSupplierName2") > 0 Then
Me.Name2Verified = True
Me.OnEPLSDB2 = True
Else

Me.Name2Verified = 1
Me.OnEPLSDB2 = 0
End If

If DCount("*", "qryVerifyScrubbedSupplierName3") > 0 Then
Me.Name3Verified = True
Me.OnEPLSDB3 = True
Else

Me.Name3Verified = 1
Me.OnEPLSDB3 = 0
End If

Exit_cmdVerifyScrubbedSupplierName123_Cl:
Exit Sub

Err_cmdVerifyallScrubbedSupplierNames123_Click:
MsgBox Err.Description
Resume Exit_cmdallVerifyScrubbedSupplierNames123_Cl

Exit Sub
rst.MoveNext
Loop
Set rst = Nothing

End Sub
 
G

Guest

I didn't get a response to my question from last week so I continued to work
on my code. It seems to work in the sense that it opens the queries but the
checkboxes don't get checked off and I get the error message:

Update or CancelUpdate without AddNew or Edit

Can anyone tell me why this is happening? I want the command button to
verify the text field, check off the appropriate check boxes as a result of
the information in the text field, move to the next record and go through the
process again until it gets to the last record.

Here's my updated code:

Private Sub cmdVerifyAllScrubbedSupplierNames123_Click()
Dim rst As Recordset

Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
Me.Bookmark = rst.Bookmark

Dim stDocName As String
On Error GoTo Err_cmdVerifyAllScrubbedSupplierNames123_Click

stDocName = "mcrVerifyScrubbedSupplierName123"
DoCmd.RunMacro stDocName

If DCount("*", "qryVerifyScrubbedSupplierName1") > 0 Then

Me.Name1Verified = True
Me.OnEPLSDB1 = True
Else

Me.Name1Verified = 1
Me.OnEPLSDB1 = 0
End If

If DCount("*", "qryVerifyScrubbedSupplierName2") > 0 Then
Me.Name2Verified = True
Me.OnEPLSDB2 = True
Else

Me.Name2Verified = 1
Me.OnEPLSDB2 = 0
End If

If DCount("*", "qryVerifyScrubbedSupplierName3") > 0 Then
Me.Name3Verified = True
Me.OnEPLSDB3 = True
Else

Me.Name3Verified = 1
Me.OnEPLSDB3 = 0

End If

Exit_cmdVerifyScrubbedSupplierName123_Cl:
Exit Sub

Exit_cmdVerifyAllScrubbedSupplierNames123_Cl:
Exit Sub

Err_cmdVerifyAllScrubbedSupplierNames123_Click:
MsgBox Err.Description
Resume Exit_cmdVerifyAllScrubbedSupplierNames123_Cl

Exit Sub

rst.MoveNext
Loop
Set rst = Nothing

End Sub

Any help is greatly appreciated!!!
 
G

Guest

Sorry, Tina, I did not see your post from Friday.
The error you are getting indicates you have done something to the
recordset without completing what you started. I think the corrections I
have made will fix this. Let me know if they continue to happen. If they
do, it may be something going on in your macro. The problem is where the
bottom of the loop is placed. Here is how it should be:
Some notes:

Inented so it is readable
Put All your Dims at that top of the procedure (some will argue this, but
they are wrong)
Put the on error statement as the first executable line in a procedure
(There are execptions, but this in not one of them)

Private Sub cmdVerifyAllScrubbedSupplierNames123_Click()
Dim rst As Recordset
Dim stDocName As String

On Error GoTo Err_cmdVerifyAllScrubbedSupplierNames123_Click

Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
Me.Bookmark = rst.Bookmark
stDocName = "mcrVerifyScrubbedSupplierName123"
DoCmd.RunMacro stDocName
If DCount("*", "qryVerifyScrubbedSupplierName1") > 0 Then
'Here you are mixing data types and making this confusing. I changed it to
what
'I think you mean
Me.Name1Verified = True
Me.OnEPLSDB1 = True
Else
Me.Name1Verified = False
Me.OnEPLSDB1 = False
End If

If DCount("*", "qryVerifyScrubbedSupplierName2") > 0 Then
'I left these alone, but you should be consistent
Me.Name2Verified = True
Me.OnEPLSDB2 = True
Else
Me.Name2Verified = 1
Me.OnEPLSDB2 = 0
End If

If DCount("*", "qryVerifyScrubbedSupplierName3") > 0 Then
Me.Name3Verified = True
Me.OnEPLSDB3 = True
Else
Me.Name3Verified = 1
Me.OnEPLSDB3 = 0
End If
rst.MoveNext
Loop

Exit_cmdVerifyScrubbedSupplierName123_Cl:
Set rst = Nothing
Exit Sub

Err_cmdVerifyAllScrubbedSupplierNames123_Click:

MsgBox Err.Description
Resume Exit_cmdVerifyAllScrubbedSupplierNames123_Cl

End Sub

Try the above and see if it works a little better.
 
G

Guest

That worked great! Thank you so much!!
--
Tina


Klatuu said:
Sorry, Tina, I did not see your post from Friday.
The error you are getting indicates you have done something to the
recordset without completing what you started. I think the corrections I
have made will fix this. Let me know if they continue to happen. If they
do, it may be something going on in your macro. The problem is where the
bottom of the loop is placed. Here is how it should be:
Some notes:

Inented so it is readable
Put All your Dims at that top of the procedure (some will argue this, but
they are wrong)
Put the on error statement as the first executable line in a procedure
(There are execptions, but this in not one of them)

Private Sub cmdVerifyAllScrubbedSupplierNames123_Click()
Dim rst As Recordset
Dim stDocName As String

On Error GoTo Err_cmdVerifyAllScrubbedSupplierNames123_Click

Set rst = Me.RecordsetClone
rst.MoveLast
rst.MoveFirst
Do While Not rst.EOF
Me.Bookmark = rst.Bookmark
stDocName = "mcrVerifyScrubbedSupplierName123"
DoCmd.RunMacro stDocName
If DCount("*", "qryVerifyScrubbedSupplierName1") > 0 Then
'Here you are mixing data types and making this confusing. I changed it to
what
'I think you mean
Me.Name1Verified = True
Me.OnEPLSDB1 = True
Else
Me.Name1Verified = False
Me.OnEPLSDB1 = False
End If

If DCount("*", "qryVerifyScrubbedSupplierName2") > 0 Then
'I left these alone, but you should be consistent
Me.Name2Verified = True
Me.OnEPLSDB2 = True
Else
Me.Name2Verified = 1
Me.OnEPLSDB2 = 0
End If

If DCount("*", "qryVerifyScrubbedSupplierName3") > 0 Then
Me.Name3Verified = True
Me.OnEPLSDB3 = True
Else
Me.Name3Verified = 1
Me.OnEPLSDB3 = 0
End If
rst.MoveNext
Loop

Exit_cmdVerifyScrubbedSupplierName123_Cl:
Set rst = Nothing
Exit Sub

Err_cmdVerifyAllScrubbedSupplierNames123_Click:

MsgBox Err.Description
Resume Exit_cmdVerifyAllScrubbedSupplierNames123_Cl

End Sub

Try the above and see if it works a little better.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Problem adding new record to table via form - error 2105 "You can'tgo to the specified record" 1
Open for to Edit or Add Record 6
How to update control 3
Cmd Button Does not work on first click 2
Type Mismatch 2
popup issue if no existing record 1
Print Form with single record 1
if function call is true exit sub 2

Top