Trapping for Last record on Cont. Form, I'm getting desperate...

[Guest]

Does anyone know how I can make this code trap for the Last Record

The procedure cycles through a number of the same selection box on one
continuous form, and if the choice is correct, then copies the data to
another field on the target field/form. Then moves to the next selection,
until it fills in the last blank field record, that’s where it bombs out with
a no next record error: At the moment I have a quick fix in the shape of an
On Error line, but it’s not ideal as It realies on a natural error rather
than clean elegant code. I have tried every which way to get my head round
this, but have got nowhere for nearly 3 days.


Private Sub Waypoint_Selector_Click()
'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector
'If the Target field is empty then place the Selection from the Selector
Form into the Target Field on Target Form
End If

If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] <>
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Null
'This checks if the selection is wrong, if so, then reset (blank) the
Waypoint Target
End If


If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Waypoint_Combo.SetFocus
DoCmd.GoToControl "Direction_Combo"
RunCommand acCmdRecordsGoToNext
'This sets the Focus back to the Run Test Form/Direction Control

End If
Proc_Err:
'MsgBox "All fields are filled"

End Sub

 

[George Nicholson]

Quite often "clean elegant code" means allowing for the possibility of an
error & reacting to it. This is one of them.

Either rely on the EOF (end of file) property of the Form's Recordset to
tell you if you've "gone past" available records, or simply assume that if
you raise an error from GoToNext that you've hit EOF. In either case, you
can't reliably test for it before trying to move.

On Error GoTo Next
RunCommand acCmdRecordsGoToNext
If Err.Number = 0 then
'No error
Else
'No record is available. Go Back to Last record of recordset
RunCommand acCmdRecordsGoToLast
' ?Add MsgBox here?
End If
'Restore original Error handler, reset Err object
On Error GoTo Proc_Err

HTH,

 

[Guest]

George,

Do I paste your code in verbatim, at the beggining of my code?

Your first line: 'On Error GoTo Next' Just comes back as Red Highlighted
error code?
Is it because Maybe 'Next' is a reserved word?.

Also, what does your code actually do in relation to my code, or is it just
a generic example?. Apart from relieving my code of errors, I also want to
the code to be able to recognise when it has reached the last record, because
I want to assign some additonal actions to that occurance.

Quite often "clean elegant code" means allowing for the possibility of an
error & reacting to it. This is one of them.

Either rely on the EOF (end of file) property of the Form's Recordset to
tell you if you've "gone past" available records, or simply assume that if
you raise an error from GoToNext that you've hit EOF. In either case, you
can't reliably test for it before trying to move.

On Error GoTo Next
RunCommand acCmdRecordsGoToNext
If Err.Number = 0 then
'No error
Else
'No record is available. Go Back to Last record of recordset
RunCommand acCmdRecordsGoToLast
' ?Add MsgBox here?
End If
'Restore original Error handler, reset Err object
On Error GoTo Proc_Err

HTH,

Does anyone know how I can make this code trap for the Last Record

The procedure cycles through a number of the same selection box on one
continuous form, and if the choice is correct, then copies the data to
another field on the target field/form. Then moves to the next selection,
until it fills in the last blank field record, that's where it bombs out
with
a no next record error: At the moment I have a quick fix in the shape of
an
On Error line, but it's not ideal as It realies on a natural error rather
than clean elegant code. I have tried every which way to get my head round
this, but have got nowhere for nearly 3 days.


Private Sub Waypoint_Selector_Click()
'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector
'If the Target field is empty then place the Selection from the Selector
Form into the Target Field on Target Form
End If

If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] <>
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Null
'This checks if the selection is wrong, if so, then reset (blank) the
Waypoint Target
End If


If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Waypoint_Combo.SetFocus
DoCmd.GoToControl "Direction_Combo"
RunCommand acCmdRecordsGoToNext
'This sets the Focus back to the Run Test Form/Direction Control

End If
Proc_Err:
'MsgBox "All fields are filled"

End Sub

 

[Douglas J. Steele]

Typo on George's part. It should be

On Error Resume Next

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

George,

Do I paste your code in verbatim, at the beggining of my code?

Your first line: 'On Error GoTo Next' Just comes back as Red Highlighted
error code?
Is it because Maybe 'Next' is a reserved word?.

Also, what does your code actually do in relation to my code, or is it
just
a generic example?. Apart from relieving my code of errors, I also want to
the code to be able to recognise when it has reached the last record,
because
I want to assign some additonal actions to that occurance.

Quite often "clean elegant code" means allowing for the possibility of an
error & reacting to it. This is one of them.

Either rely on the EOF (end of file) property of the Form's Recordset to
tell you if you've "gone past" available records, or simply assume that
if
you raise an error from GoToNext that you've hit EOF. In either case, you
can't reliably test for it before trying to move.

On Error GoTo Next
RunCommand acCmdRecordsGoToNext
If Err.Number = 0 then
'No error
Else
'No record is available. Go Back to Last record of recordset
RunCommand acCmdRecordsGoToLast
' ?Add MsgBox here?
End If
'Restore original Error handler, reset Err object
On Error GoTo Proc_Err

HTH,

Does anyone know how I can make this code trap for the Last Record

The procedure cycles through a number of the same selection box on one
continuous form, and if the choice is correct, then copies the data to
another field on the target field/form. Then moves to the next
selection,
until it fills in the last blank field record, that's where it bombs
out
with
a no next record error: At the moment I have a quick fix in the shape
of
an
On Error line, but it's not ideal as It realies on a natural error
rather
than clean elegant code. I have tried every which way to get my head
round
this, but have got nowhere for nearly 3 days.


Private Sub Waypoint_Selector_Click()
'~~~~~~~~~~~~~~~~~~~~~~
'set up Error Handler
On Error GoTo Proc_Err

'~~~~~~~~~~~~~~~~~~~~~~

If IsNull(Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Me.Waypoint_Selector
'If the Target field is empty then place the Selection from the
Selector
Form into the Target Field on Target Form
End If

If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] <>
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] = Null
'This checks if the selection is wrong, if so, then reset (blank) the
Waypoint Target
End If


If (Forms.Runs.[frm_Run_Test].Form.[Waypoint_Combo] =
Forms.Runs.[frm_Run_Test].Form.[Run_waypoint]) Then
Parent.frm_Run_Test.SetFocus
Parent.frm_Run_Test.Form.Waypoint_Combo.SetFocus
DoCmd.GoToControl "Direction_Combo"
RunCommand acCmdRecordsGoToNext
'This sets the Focus back to the Run Test Form/Direction Control

End If
Proc_Err:
'MsgBox "All fields are filled"

End Sub