limiting the "go to previous record" macro

[Guest]

I've created a button that takes me to the "previous record" in a MS Runtime
application. If I happen to be at the beginning of the record list, the
program will just quit ( in Access, it will give you an error msg- in
Runtime, the program will quit) Any suggestions on how to limit the "previous
record" macro to go as far back as the first record and no further?

 

[Guest]

On the Onclick event of the form, use code instead of Macro

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click


DoCmd.GoToRecord , , acPrevious

Exit_ButtonName_Click:
Exit Sub

Err_ButtonName_Click:
MsgBox Err.Description
Resume Exit_ButtonName_Click

End Sub

 

[Guest]

To add some to Ofer's reply, you will want to convert all macros (except
Autoexec and Autokeys) to the equivalent VBA code, and make sure that each
procedure includes error handling. Unhandled errors in run-time applications
will cause the types of crash that you described.

Here is some code that you can try, for (5) command buttons on a form named
cmdFirst, cmdPrevious, cmdNext, cmdLast and cmdAdd

Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error GoTo ProcError

If IsNull(Me.CategoryID) Then
cmdPrevious.SetFocus
cmdNext.Enabled = False
Else
cmdNext.Enabled = True
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Current..."
Resume ExitProc
End Sub

Private Sub cmdPrevious_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acPrevious

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2105
Me.cmdNext.SetFocus
Me.cmdPrevious.Enabled = False
Me.cmdFirst.Enabled = False
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdPrevious_Click..."
End Select
Resume ExitProc
End Sub

Private Sub cmdNext_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acNext

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdNext_Click..."
Resume ExitProc

End Sub

Private Sub cmdFirst_Click()
On Error GoTo Err_cmdFirst_Click

DoCmd.GoToRecord , , acFirst
cmdPrevious.Enabled = False
cmdNext.SetFocus
cmdFirst.Enabled = False

Exit_cmdFirst_Click:
Exit Sub

Err_cmdFirst_Click:
MsgBox Err.Description
Resume Exit_cmdFirst_Click

End Sub

Private Sub cmdLast_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acLast

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdLast_Click..."
Resume ExitProc

End Sub

Private Sub cmdAdd_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acNewRec

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdAdd_Click..."
Resume ExitProc

End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

On the Onclick event of the form, use code instead of Macro

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click


DoCmd.GoToRecord , , acPrevious

Exit_ButtonName_Click:
Exit Sub

Err_ButtonName_Click:
MsgBox Err.Description
Resume Exit_ButtonName_Click

End Sub
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck
__________________________________________

:

I've created a button that takes me to the "previous record" in a MS Runtime
application. If I happen to be at the beginning of the record list, the
program will just quit ( in Access, it will give you an error msg- in
Runtime, the program will quit) Any suggestions on how to limit the "previous
record" macro to go as far back as the first record and no further?

 

[Guest]

Tom,

thanks for the advice. I'll try your suggestions.

Phil

 

[Guest]

Ofer,

thanks for the help. I'll give it a try.

P nahser

 

[Guest]

Thank you- it worked perfectly. 1 additional question- The message box comes
up the generic "Microsoft Office Access" across the top in bold. I would
like to change the name of the label. could you suggest how that is done?
Thanks.

Phil

 

[Guest]

Worked great. The error box generated is the generic "Microsoft Office
Access" box . Can I change the label of the box"

Phil

 

[Guest]

Hi Phil,

What error are you getting with the code I gave you? The method Ofer
presented would result in an error 2105 if you attempt to click on the
cmdPrevious button when sitting on the first record. However, in the method I
gave you, I trapped for this error and handled it using the SELECT Case ....
END SELECT construct.

I'm not using Access 2003, so I'm not sure about the generic "Microsoft
Office
Access" box that you are seeing. In Access 2002, I just see the Msgbox title
for the cmdPrevious (after commenting out the Case 2105 block) which reads:
"Error in procedure cmdPrevious_Click...". You can make this read whatever
you want.

Oh, now that I look at it a little closer, the error handler that Ofer
included does not include the optional Msgbox title string. It is written
like this:

MsgBox Err.Description

You can change this to read:

MsgBox Err.Description, ,"AnyTextYouWant"
or
MsgBox Err.Description, vbInformation, "AnyTextYouWant"

if you want the Information Message icon. Other appropriate choices might
include the following:

vbCritical Display Critical Message icon.
vbQuestion Display Warning Query icon.
vbExclamation Display Warning Message icon.

Select the MsgBox statement in VBA code, and then press F1 to open context
sensitive help on this statement.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

Worked great. The error box generated is the generic "Microsoft Office
Access" box . Can I change the label of the box"

Phil

 

[Guest]

I forgot to mention that the code I presented below was written using the
Categories form in the sample Northwind database. You will need to modify
this line of code:

If IsNull(Me.CategoryID) Then

in the Private Sub Form_Current() procedure, so that it references the
applicable primary key field name.


Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

To add some to Ofer's reply, you will want to convert all macros (except
Autoexec and Autokeys) to the equivalent VBA code, and make sure that each
procedure includes error handling. Unhandled errors in run-time applications
will cause the types of crash that you described.

Here is some code that you can try, for (5) command buttons on a form named
cmdFirst, cmdPrevious, cmdNext, cmdLast and cmdAdd

Option Compare Database
Option Explicit

Private Sub Form_Current()
On Error GoTo ProcError

If IsNull(Me.CategoryID) Then
cmdPrevious.SetFocus
cmdNext.Enabled = False
Else
cmdNext.Enabled = True
cmdFirst.Enabled = True
cmdPrevious.Enabled = True
End If

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure Form_Current..."
Resume ExitProc
End Sub

Private Sub cmdPrevious_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acPrevious

ExitProc:
Exit Sub
ProcError:
Select Case Err.Number
Case 2105
Me.cmdNext.SetFocus
Me.cmdPrevious.Enabled = False
Me.cmdFirst.Enabled = False
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdPrevious_Click..."
End Select
Resume ExitProc
End Sub

Private Sub cmdNext_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acNext

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdNext_Click..."
Resume ExitProc

End Sub

Private Sub cmdFirst_Click()
On Error GoTo Err_cmdFirst_Click

DoCmd.GoToRecord , , acFirst
cmdPrevious.Enabled = False
cmdNext.SetFocus
cmdFirst.Enabled = False

Exit_cmdFirst_Click:
Exit Sub

Err_cmdFirst_Click:
MsgBox Err.Description
Resume Exit_cmdFirst_Click

End Sub

Private Sub cmdLast_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acLast

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdLast_Click..."
Resume ExitProc

End Sub

Private Sub cmdAdd_Click()
On Error GoTo ProcError

DoCmd.GoToRecord , , acNewRec

ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure cmdAdd_Click..."
Resume ExitProc

End Sub



Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

On the Onclick event of the form, use code instead of Macro

Private Sub ButtonName_Click()
On Error GoTo Err_ButtonName_Click


DoCmd.GoToRecord , , acPrevious

Exit_ButtonName_Click:
Exit Sub

Err_ButtonName_Click:
MsgBox Err.Description
Resume Exit_ButtonName_Click

End Sub
--
Please respond to the group if your question been answered or not, so other
can refer to it.
Thank you and Good luck
__________________________________________

:

I've created a button that takes me to the "previous record" in a MS Runtime
application. If I happen to be at the beginning of the record list, the
program will just quit ( in Access, it will give you an error msg- in
Runtime, the program will quit) Any suggestions on how to limit the "previous
record" macro to go as far back as the first record and no further?

 

[Guest]

Sorry for the confusion Tom. I'm not getting an error message with your
code. I was referring to the Msg Box that was generated when I forced my
program into an error situation. I've tried the sub programs in isolated
buttons but I've not entered the whole code that you wrote yet. So far, so
good. I've been able to create a msg box with my label and message. Another
question- your code was a series of 5 different commands- can I write all of
these into 1 macro and then have different buttons bring up the specific sub
commands requested or do I have to write a separate code for each button.
Thank you again for all of your help.

Phil