Repeat Expression using code

  • Thread starter Thread starter Guest
  • Start date Start date
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
 
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
 
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...
 
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
Looping until end. 2
Repeating Code 16
Loop/Repeat Code 5
Check box question 2
Code quits after record delete 4
Can I eliminate the Yes/No Append message box? 4
Using Custom Warning Boxes 1

Back
Top