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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

efandango - 28 Mar 2007 21:12 GMT

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

Reply to this Message

George Nicholson - 28 Mar 2007 22:29 GMT

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
>
[quoted text clipped - 42 lines]
>
> End Sub

Reply to this Message

efandango - 29 Mar 2007 00:30 GMT

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.
[quoted text clipped - 64 lines]
> >
> > End Sub

Reply to this Message

Douglas J. Steele - 29 Mar 2007 12:51 GMT

Typo on George's part. It should be

On Error Resume Next

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> George,
>
[quoted text clipped - 86 lines]
>> >
>> > End Sub

Reply to this Message