how do I add a do until loop to this code?

  • Thread starter Thread starter efandango
  • Start date Start date
E

efandango

I have two forms side by side: The Source form and the Target Form. They are
used as a memory test scenario.

Each time the button is pressed, the code copies a sequential, single record
from the source form onto the Target Form, then moves onto the next record.
moving through the source records one at a time (after each button click).

I no longer want to rely on the button each time because it is getting
painful hitting thousands of button pushes, but instead want to hit the
button once and after a certain time delay between each record, move to the
next 'copy record action' and repeat until all records are copied. Can
someone help me put a loop into my code below?




The Code:

On Error GoTo Proc_Err
'Run_Direction
DoCmd.SetWarnings False
'If Me.Postcode = "X" Then
'DoCmd.GoToRecord , , acNext

'This part checks if current record textbox on the target form is blank, if
so it copies the record value from the source form and moves to the next
source record.
If not (implying that it has already copied the latest record), it moves to
the next record, ready to copyt that data to a new blank record.

If IsNull(Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint]) Then
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] = Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] = Me.Run_Direction
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNext


This part checks if the recipient record has been copied, if so, it creates
a new record and copies from the next source record to the target record

ElseIf Not
IsNull(Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint]) Then
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNewRec

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] = Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] = Me.Run_Direction
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq


After each record copy, this part moves the focus back to the source form,
but moves on one record, ready to copy the next record in the sequence.

'go back to the selector form
Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

End If
'End
Proc_Err:
MsgBox "Run Complete"
DoCmd.SetWarnings True
 
In the Click event of the button, set the form's Time Interval property, and
put your code in the form's Timer event instead of the button's Click event.

Carl Rapson

efandango said:
I have two forms side by side: The Source form and the Target Form. They
are
used as a memory test scenario.

Each time the button is pressed, the code copies a sequential, single
record
from the source form onto the Target Form, then moves onto the next
record.
moving through the source records one at a time (after each button click).

I no longer want to rely on the button each time because it is getting
painful hitting thousands of button pushes, but instead want to hit the
button once and after a certain time delay between each record, move to
the
next 'copy record action' and repeat until all records are copied. Can
someone help me put a loop into my code below?




The Code:

On Error GoTo Proc_Err
'Run_Direction
DoCmd.SetWarnings False
'If Me.Postcode = "X" Then
'DoCmd.GoToRecord , , acNext

'This part checks if current record textbox on the target form is blank,
if
so it copies the record value from the source form and moves to the next
source record.
If not (implying that it has already copied the latest record), it moves
to
the next record, ready to copyt that data to a new blank record.

If IsNull(Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint]) Then
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNext


This part checks if the recipient record has been copied, if so, it
creates
a new record and copies from the next source record to the target record

ElseIf Not
IsNull(Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint]) Then
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNewRec

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq


After each record copy, this part moves the focus back to the source form,
but moves on one record, ready to copy the next record in the sequence.

'go back to the selector form
Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

End If
'End
Proc_Err:
MsgBox "Run Complete"
DoCmd.SetWarnings True
Click to expand...
 
Carl,

I was hoping toget some help on the actual loop code itself. I have not done
a loop before, and no matter what I try, always ends in some compile error
message or another.


Carl Rapson said:
In the Click event of the button, set the form's Time Interval property, and
put your code in the form's Timer event instead of the button's Click event.

Carl Rapson

efandango said:
I have two forms side by side: The Source form and the Target Form. They
are
used as a memory test scenario.

Each time the button is pressed, the code copies a sequential, single
record
from the source form onto the Target Form, then moves onto the next
record.
moving through the source records one at a time (after each button click).

I no longer want to rely on the button each time because it is getting
painful hitting thousands of button pushes, but instead want to hit the
button once and after a certain time delay between each record, move to
the
next 'copy record action' and repeat until all records are copied. Can
someone help me put a loop into my code below?




The Code:

On Error GoTo Proc_Err
'Run_Direction
DoCmd.SetWarnings False
'If Me.Postcode = "X" Then
'DoCmd.GoToRecord , , acNext

'This part checks if current record textbox on the target form is blank,
if
so it copies the record value from the source form and moves to the next
source record.
If not (implying that it has already copied the latest record), it moves
to
the next record, ready to copyt that data to a new blank record.

If IsNull(Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint]) Then
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq
DoCmd.GoToRecord , , acNext


This part checks if the recipient record has been copied, if so, it
creates
a new record and copies from the next source record to the target record

ElseIf Not
IsNull(Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint]) Then
Forms![frm_Runs].[frm_Run_Reveal_Target].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Target].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNewRec

Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_waypoint] =
Me.Run_waypoint
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Postcode] = Me.Postcode
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[Run_Direction] =
Me.Run_Direction
Forms.frm_Runs.[frm_Run_Reveal_Target].Form.[OrderSeq] = Me.OrderSeq


After each record copy, this part moves the focus back to the source form,
but moves on one record, ready to copy the next record in the sequence.

'go back to the selector form
Forms![frm_Runs].[frm_Run_Reveal_Selector].SetFocus
Forms![frm_Runs].[frm_Run_Reveal_Selector].Form.[Run_waypoint].SetFocus
DoCmd.GoToRecord , , acNext

End If
'End
Proc_Err:
MsgBox "Run Complete"
DoCmd.SetWarnings True
Click to expand...
Click to expand...
 
Back
Top