Repeat Expression using code

G

Guest

Hi,
I have the following code running from a button on a form. It works for the
current record and then moves to the next record, but the user has to click
the button again and so on. How can I make this code run through every record
on the form until the field [Forms]![frmInvoiceReceived]![ItemNo] Is Null?

Private Sub CopyInvoiceNumberToAllChecked_Click()
With CodeContextObject
DoCmd.SetWarnings False
If (.VendorWasPaid Like "-1") Then
Forms!frmInvoiceReceived!InvoiceNumber =
Forms!frmInvoiceReceived!InvoiceNo
End If
DoCmd.RunCommand acCmdRefresh
DoCmd.GoToRecord , "", acNext
End With
mcrSetValueInvoiceReceived_Exit:
mcrSetValueInvoiceReceived_Err:
End Sub



Thanks so much for your help!
Emmy
 
G

Guest

You can either use an update query to update all the records, or use this
code to update all the reords displayed in the form

Dim I As Integer
' Move to the first record
DoCmd.GoToRecord , , acFirst
' Create a loop for all the records
For I = 1 To Me.RecordsetClone.RecordCount
' Your code
' Move to the next record
DoCmd.GoToRecord , , acNext
Next I
 
G

Guest

Thank you. I tried the code and get the message "Compile Error: Next without
For." This is what I have:

Private Sub CopyInvoiceNumberToAllChecked_Click()

Dim I As Integer
'Move to the first record
DoCmd.GoToRecord , , acFirst
'Create a loop for all the records
For I = 1 To Me.RecordsetClone.RecordCount
With CodeContextObject
DoCmd.SetWarnings False
If (.VendorWasPaid Like "-1") Then
Forms!frmInvoiceReceived!InvoiceNumber =
Forms!frmInvoiceReceived!InvoiceNo
End If
DoCmd.RunCommand acCmdRefresh
DoCmd.GoToRecord , "", acNext

Next I

End With
mcrSetValueInvoiceReceived_Exit:
mcrSetValueInvoiceReceived_Err:
End Sub

Ofer Cohen said:
You can either use an update query to update all the records, or use this
code to update all the reords displayed in the form

Dim I As Integer
' Move to the first record
DoCmd.GoToRecord , , acFirst
' Create a loop for all the records
For I = 1 To Me.RecordsetClone.RecordCount
' Your code
' Move to the next record
DoCmd.GoToRecord , , acNext
Next I

--
Good Luck
BS"D


Emmy said:
Hi,
I have the following code running from a button on a form. It works for the
current record and then moves to the next record, but the user has to click
the button again and so on. How can I make this code run through every record
on the form until the field [Forms]![frmInvoiceReceived]![ItemNo] Is Null?

Private Sub CopyInvoiceNumberToAllChecked_Click()
With CodeContextObject
DoCmd.SetWarnings False
If (.VendorWasPaid Like "-1") Then
Forms!frmInvoiceReceived!InvoiceNumber =
Forms!frmInvoiceReceived!InvoiceNo
End If
DoCmd.RunCommand acCmdRefresh
DoCmd.GoToRecord , "", acNext
End With
mcrSetValueInvoiceReceived_Exit:
mcrSetValueInvoiceReceived_Err:
End Sub



Thanks so much for your help!
Emmy
Click to expand...
Click to expand...
 
L

Lynn Trapp

Emmy,
Your Next I statement is inside your With statement and thus you are
confusing Access. Try this

For I = 1 To Me.RecordsetClone.RecordCount
With CodeContextObject
DoCmd.SetWarnings False
If (.VendorWasPaid Like "-1") Then
Forms!frmInvoiceReceived!InvoiceNumber =
Forms!frmInvoiceReceived!InvoiceNo
End If
DoCmd.RunCommand acCmdRefresh
DoCmd.GoToRecord , "", acNext

End With
Next I

Thank you. I tried the code and get the message "Compile Error: Next without
For." This is what I have:

Private Sub CopyInvoiceNumberToAllChecked_Click()

Dim I As Integer
'Move to the first record
DoCmd.GoToRecord , , acFirst
'Create a loop for all the records
For I = 1 To Me.RecordsetClone.RecordCount
With CodeContextObject
DoCmd.SetWarnings False
If (.VendorWasPaid Like "-1") Then
Forms!frmInvoiceReceived!InvoiceNumber =
Forms!frmInvoiceReceived!InvoiceNo
End If
DoCmd.RunCommand acCmdRefresh
DoCmd.GoToRecord , "", acNext

Next I

End With
mcrSetValueInvoiceReceived_Exit:
mcrSetValueInvoiceReceived_Err:
End Sub

Ofer Cohen said:
You can either use an update query to update all the records, or use this
code to update all the reords displayed in the form

Dim I As Integer
' Move to the first record
DoCmd.GoToRecord , , acFirst
' Create a loop for all the records
For I = 1 To Me.RecordsetClone.RecordCount
' Your code
' Move to the next record
DoCmd.GoToRecord , , acNext
Next I

--
Good Luck
BS"D


Emmy said:
Hi,
I have the following code running from a button on a form. It works for the
current record and then moves to the next record, but the user has to click
the button again and so on. How can I make this code run through every record
on the form until the field [Forms]![frmInvoiceReceived]![ItemNo] Is Null?

Private Sub CopyInvoiceNumberToAllChecked_Click()
With CodeContextObject
DoCmd.SetWarnings False
If (.VendorWasPaid Like "-1") Then
Forms!frmInvoiceReceived!InvoiceNumber =
Forms!frmInvoiceReceived!InvoiceNo
End If
DoCmd.RunCommand acCmdRefresh
DoCmd.GoToRecord , "", acNext
End With
mcrSetValueInvoiceReceived_Exit:
mcrSetValueInvoiceReceived_Err:
End Sub



Thanks so much for your help!
Emmy
Click to expand...
Click to expand...
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

Repeat Expression 2
Loop/Repeat Code 5
Repeating Code 16
how do I add a do until loop to this code? 2
Can I eliminate the Yes/No Append message box? 4
Check box question 2
Event Procedure with ACCDE 3
Assign Alpha Code to Child Record 2

Top