Error 2501 Problem when DoubleClicking to Delete Record is Cancell

G

Guest

as you can see from my humble vba code below, i want to tell the user that
something really serious is about to happen if they proceed on their selected
path.
the system generates a message that's also informative, but i want to make
really really sure that the user 'gets it'. in testing my code, i have found
that when i click the ok from my message prompt and then click 'No' from the
system's generated message that i get a message box telling me 'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i attempt to
debug it, the following line in the vba code is highlighted in yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
 
G

Guest

wouldn't you know it, i forgot to copy the vba code into my posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & Me![Patient Number] & ") in this cycle! This
may result in the deletion of an AE which continues past the current cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's data,
there may be AEs listed as continuing past the preceding one requiring your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..
 
K

Ken Snell [MVP]

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data, there may be AEs listed as continuing past the preceding one requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


Ted said:
wouldn't you know it, i forgot to copy the vba code into my posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & Me![Patient Number] & ") in this cycle!
This
may result in the deletion of an AE which continues past the current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data,
there may be AEs listed as continuing past the preceding one requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

Ted said:
as you can see from my humble vba code below, i want to tell the user
that
something really serious is about to happen if they proceed on their
selected
path.
the system generates a message that's also informative, but i want to
make
really really sure that the user 'gets it'. in testing my code, i have
found
that when i click the ok from my message prompt and then click 'No' from
the
system's generated message that i get a message box telling me 'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i attempt to
debug it, the following line in the vba code is highlighted in yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
 
G

Guest

but of course.....
and it looks like a winner; but what's the lesson underlying the code here?
why does it work and mine not (apart from my being newbie)?

-ted


Ken Snell said:
May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data, there may be AEs listed as continuing past the preceding one requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


Ted said:
wouldn't you know it, i forgot to copy the vba code into my posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & Me![Patient Number] & ") in this cycle!
This
may result in the deletion of an AE which continues past the current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data,
there may be AEs listed as continuing past the preceding one requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

Ted said:
as you can see from my humble vba code below, i want to tell the user
that
something really serious is about to happen if they proceed on their
selected
path.
the system generates a message that's also informative, but i want to
make
really really sure that the user 'gets it'. in testing my code, i have
found
that when i click the ok from my message prompt and then click 'No' from
the
system's generated message that i get a message box telling me 'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i attempt to
debug it, the following line in the vba code is highlighted in yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for the code;
not the old BASIC GoTo actions, but rather the more desired If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


Ted said:
but of course.....
and it looks like a winner; but what's the lesson underlying the code
here?
why does it work and mine not (apart from my being newbie)?

-ted


Ken Snell said:
May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data, there may be AEs listed as continuing past the preceding one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


Ted said:
wouldn't you know it, i forgot to copy the vba code into my posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & Me![Patient Number] & ") in this cycle!
This
may result in the deletion of an AE which continues past the current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data,
there may be AEs listed as continuing past the preceding one requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell the user
that
something really serious is about to happen if they proceed on their
selected
path.
the system generates a message that's also informative, but i want to
make
really really sure that the user 'gets it'. in testing my code, i have
found
that when i click the ok from my message prompt and then click 'No'
from
the
system's generated message that i get a message box telling me 'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i attempt
to
debug it, the following line in the vba code is highlighted in yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hey ken,

there were a couple of add'l places in my form where this sort of msgbox
command was needed. i am trying to adapt your rewrite for it and it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

Ken Snell said:
I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for the code;
not the old BASIC GoTo actions, but rather the more desired If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


Ted said:
but of course.....
and it looks like a winner; but what's the lesson underlying the code
here?
why does it work and mine not (apart from my being newbie)?

-ted


Ken Snell said:
May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data, there may be AEs listed as continuing past the preceding one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & Me![Patient Number] & ") in this cycle!
This
may result in the deletion of an AE which continues past the current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this cycle's
data,
there may be AEs listed as continuing past the preceding one requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell the user
that
something really serious is about to happen if they proceed on their
selected
path.
the system generates a message that's also informative, but i want to
make
really really sure that the user 'gets it'. in testing my code, i have
found
that when i click the ok from my message prompt and then click 'No'
from
the
system's generated message that i get a message box telling me 'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i attempt
to
debug it, the following line in the vba code is highlighted in yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hey ken,

there were a couple of add'l places in my form where this sort of msgbox
command was needed. i am trying to adapt your rewrite for it and it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

Ken Snell said:
I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


Ted said:
but of course.....
and it looks like a winner; but what's the lesson underlying the code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data, there may be AEs listed as continuing past the preceding one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in this
cycle!
This
may result in the deletion of an AE which continues past the current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data,
there may be AEs listed as continuing past the preceding one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell the
user
that
something really serious is about to happen if they proceed on
their
selected
path.
the system generates a message that's also informative, but i want
to
make
really really sure that the user 'gets it'. in testing my code, i
have
found
that when i click the ok from my message prompt and then click 'No'
from
the
system's generated message that i get a message box telling me 'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i
attempt
to
debug it, the following line in the vba code is highlighted in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same message
twice. when the answer's "No" and i click cancel it gives me the two messages
each two times.

-ted


Ken Snell said:
"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hey ken,

there were a couple of add'l places in my form where this sort of msgbox
command was needed. i am trying to adapt your rewrite for it and it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

Ken Snell said:
I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson underlying the code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data, there may be AEs listed as continuing past the preceding one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in this
cycle!
This
may result in the deletion of an AE which continues past the current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data,
there may be AEs listed as continuing past the preceding one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell the
user
that
something really serious is about to happen if they proceed on
their
selected
path.
the system generates a message that's also informative, but i want
to
make
really really sure that the user 'gets it'. in testing my code, i
have
found
that when i click the ok from my message prompt and then click 'No'
from
the
system's generated message that i get a message box telling me 'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i
attempt
to
debug it, the following line in the vba code is highlighted in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

I kinda figured that was what you meant, but wanted to be sure.

The reason is because you've put both tests in a single If statement, so VBA
runs the MsgBox function regardless of the value in Me.Continuing.Value. You
need to nest these If tests so that the second one is done only if the first
test is valid. Something like this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same message
twice. when the answer's "No" and i click cancel it gives me the two
messages
each two times.

-ted


Ken Snell said:
"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hey ken,

there were a couple of add'l places in my form where this sort of
msgbox
command was needed. i am trying to adapt your rewrite for it and it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU ARE
ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

:

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson underlying the
code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE
ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data, there may be AEs listed as continuing past the preceding one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my
posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in this
cycle!
This
may result in the deletion of an AE which continues past the
current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data,
there may be AEs listed as continuing past the preceding one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell the
user
that
something really serious is about to happen if they proceed on
their
selected
path.
the system generates a message that's also informative, but i
want
to
make
really really sure that the user 'gets it'. in testing my code,
i
have
found
that when i click the ok from my message prompt and then click
'No'
from
the
system's generated message that i get a message box telling me
'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i
attempt
to
debug it, the following line in the vba code is highlighted in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

i'm rareing to try this out, but i reckon it's gotta wait 'till tomorrow :-(
i guess i'll never understand that counterintuitive bit about how vba runs
MsgBox fn running irrespective of what's happening wrt Me.Continuing.Value.
but i won't look a gift horse in the mouth!

many thanks, ken!

-ted


Ken Snell said:
I kinda figured that was what you meant, but wanted to be sure.

The reason is because you've put both tests in a single If statement, so VBA
runs the MsgBox function regardless of the value in Me.Continuing.Value. You
need to nest these If tests so that the second one is done only if the first
test is valid. Something like this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same message
twice. when the answer's "No" and i click cancel it gives me the two
messages
each two times.

-ted


Ken Snell said:
"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

hey ken,

there were a couple of add'l places in my form where this sort of
msgbox
command was needed. i am trying to adapt your rewrite for it and it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU ARE
ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

:

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson underlying the
code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY DELETE
ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" & _
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data, there may be AEs listed as continuing past the preceding one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my
posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in this
cycle!
This
may result in the deletion of an AE which continues past the
current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data,
there may be AEs listed as continuing past the preceding one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell the
user
that
something really serious is about to happen if they proceed on
their
selected
path.
the system generates a message that's also informative, but i
want
to
make
really really sure that the user 'gets it'. in testing my code,
i
have
found
that when i click the ok from my message prompt and then click
'No'
from
the
system's generated message that i get a message box telling me
'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i
attempt
to
debug it, the following line in the vba code is highlighted in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

The reason it runs the msgbox each time is because it needs to evaluate all
the logical tests in the If statement in order to decide whether the final
result if true or false.

I note, by the way, that both conditions for Me.Continuing.Value (yes and
no) are designed in your code to ask the msgbox question.

Just guessing that you only want to do this if the Me.Continuing.Value value
is yes, change the code to this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ted said:
i'm rareing to try this out, but i reckon it's gotta wait 'till tomorrow
:-(
i guess i'll never understand that counterintuitive bit about how vba runs
MsgBox fn running irrespective of what's happening wrt
Me.Continuing.Value.
but i won't look a gift horse in the mouth!

many thanks, ken!

-ted


Ken Snell said:
I kinda figured that was what you meant, but wanted to be sure.

The reason is because you've put both tests in a single If statement, so
VBA
runs the MsgBox function regardless of the value in Me.Continuing.Value.
You
need to nest these If tests so that the second one is done only if the
first
test is valid. Something like this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same message
twice. when the answer's "No" and i click cancel it gives me the two
messages
each two times.

-ted


:

"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

hey ken,

there were a couple of add'l places in my form where this sort of
msgbox
command was needed. i am trying to adapt your rewrite for it and
it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU ARE
ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this
Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

:

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for
the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson underlying the
code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in
the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" &
_
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of
this
cycle's
data, there may be AEs listed as continuing past the preceding
one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my
posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in
this
cycle!
This
may result in the deletion of an AE which continues past the
current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data,
there may be AEs listed as continuing past the preceding one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell
the
user
that
something really serious is about to happen if they proceed
on
their
selected
path.
the system generates a message that's also informative, but i
want
to
make
really really sure that the user 'gets it'. in testing my
code,
i
have
found
that when i click the ok from my message prompt and then
click
'No'
from
the
system's generated message that i get a message box telling
me
'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i
attempt
to
debug it, the following line in the vba code is highlighted
in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

hi ken,

i used the following code you suggested in the subform

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


and when i click 'Cancel' following the system generated message asking
about whether or not i want to continue (following the msgBox message above),
it works.
so i guess i'm not clear as to what the one you last used was intended to
do. sorry about being this dense.



Ken Snell said:
The reason it runs the msgbox each time is because it needs to evaluate all
the logical tests in the If statement in order to decide whether the final
result if true or false.

I note, by the way, that both conditions for Me.Continuing.Value (yes and
no) are designed in your code to ask the msgbox question.

Just guessing that you only want to do this if the Me.Continuing.Value value
is yes, change the code to this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ted said:
i'm rareing to try this out, but i reckon it's gotta wait 'till tomorrow
:-(
i guess i'll never understand that counterintuitive bit about how vba runs
MsgBox fn running irrespective of what's happening wrt
Me.Continuing.Value.
but i won't look a gift horse in the mouth!

many thanks, ken!

-ted


Ken Snell said:
I kinda figured that was what you meant, but wanted to be sure.

The reason is because you've put both tests in a single If statement, so
VBA
runs the MsgBox function regardless of the value in Me.Continuing.Value.
You
need to nest these If tests so that the second one is done only if the
first
test is valid. Something like this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same message
twice. when the answer's "No" and i click cancel it gives me the two
messages
each two times.

-ted


:

"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

hey ken,

there were a couple of add'l places in my form where this sort of
msgbox
command was needed. i am trying to adapt your rewrite for it and
it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU ARE
ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this
Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

:

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow for
the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson underlying the
code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result in
the
deletion
of an AE which continues past the current cycle, i.e. Cycle #" &
_
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of
this
cycle's
data, there may be AEs listed as continuing past the preceding
one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my
posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in
this
cycle!
This
may result in the deletion of an AE which continues past the
current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of this
cycle's
data,
there may be AEs listed as continuing past the preceding one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to tell
the
user
that
something really serious is about to happen if they proceed
on
their
selected
path.
the system generates a message that's also informative, but i
want
to
make
really really sure that the user 'gets it'. in testing my
code,
i
have
found
that when i click the ok from my message prompt and then
click
'No'
from
the
system's generated message that i get a message box telling
me
'The
RunCommand Action Was Canceled', 'Runtime Error 2501'. when i
attempt
to
debug it, the following line in the vba code is highlighted
in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba newbie?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

I misread that the two message boxes were the same. Ignore that last
suggested code, and stick with what's working! < g >

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

i used the following code you suggested in the subform

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


and when i click 'Cancel' following the system generated message asking
about whether or not i want to continue (following the msgBox message
above),
it works.
so i guess i'm not clear as to what the one you last used was intended to
do. sorry about being this dense.



Ken Snell said:
The reason it runs the msgbox each time is because it needs to evaluate
all
the logical tests in the If statement in order to decide whether the
final
result if true or false.

I note, by the way, that both conditions for Me.Continuing.Value (yes and
no) are designed in your code to ask the msgbox question.

Just guessing that you only want to do this if the Me.Continuing.Value
value
is yes, change the code to this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

Ted said:
i'm rareing to try this out, but i reckon it's gotta wait 'till
tomorrow
:-(
i guess i'll never understand that counterintuitive bit about how vba
runs
MsgBox fn running irrespective of what's happening wrt
Me.Continuing.Value.
but i won't look a gift horse in the mouth!

many thanks, ken!

-ted


:

I kinda figured that was what you meant, but wanted to be sure.

The reason is because you've put both tests in a single If statement,
so
VBA
runs the MsgBox function regardless of the value in
Me.Continuing.Value.
You
need to nest these If tests so that the second one is done only if the
first
test is valid. Something like this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#"
&
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same
message
twice. when the answer's "No" and i click cancel it gives me the two
messages
each two times.

-ted


:

"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

hey ken,

there were a couple of add'l places in my form where this sort of
msgbox
command was needed. i am trying to adapt your rewrite for it and
it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU
ARE
ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel
+
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be
advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this
Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel
+
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be
advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

:

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow
for
the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson underlying
the
code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result
in
the
deletion
of an AE which continues past the current cycle, i.e. Cycle
#" &
_
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of
this
cycle's
data, there may be AEs listed as continuing past the
preceding
one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my
posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in
this
cycle!
This
may result in the deletion of an AE which continues past
the
current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of
this
cycle's
data,
there may be AEs listed as continuing past the preceding
one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to
tell
the
user
that
something really serious is about to happen if they
proceed
on
their
selected
path.
the system generates a message that's also informative,
but i
want
to
make
really really sure that the user 'gets it'. in testing my
code,
i
have
found
that when i click the ok from my message prompt and then
click
'No'
from
the
system's generated message that i get a message box
telling
me
'The
RunCommand Action Was Canceled', 'Runtime Error 2501'.
when i
attempt
to
debug it, the following line in the vba code is
highlighted
in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba
newbie?
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

if it's not broken, why fix it :)

thanks a bunch!!

btw, i don't know if i should append my latest little cunundrum to this
posting,

Ken Snell said:
I misread that the two message boxes were the same. Ignore that last
suggested code, and stick with what's working! < g >

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

i used the following code you suggested in the subform

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


and when i click 'Cancel' following the system generated message asking
about whether or not i want to continue (following the msgBox message
above),
it works.
so i guess i'm not clear as to what the one you last used was intended to
do. sorry about being this dense.



Ken Snell said:
The reason it runs the msgbox each time is because it needs to evaluate
all
the logical tests in the If statement in order to decide whether the
final
result if true or false.

I note, by the way, that both conditions for Me.Continuing.Value (yes and
no) are designed in your code to ask the msgbox question.

Just guessing that you only want to do this if the Me.Continuing.Value
value
is yes, change the code to this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

i'm rareing to try this out, but i reckon it's gotta wait 'till
tomorrow
:-(
i guess i'll never understand that counterintuitive bit about how vba
runs
MsgBox fn running irrespective of what's happening wrt
Me.Continuing.Value.
but i won't look a gift horse in the mouth!

many thanks, ken!

-ted


:

I kinda figured that was what you meant, but wanted to be sure.

The reason is because you've put both tests in a single If statement,
so
VBA
runs the MsgBox function regardless of the value in
Me.Continuing.Value.
You
need to nest these If tests so that the second one is done only if the
first
test is valid. Something like this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#"
&
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same
message
twice. when the answer's "No" and i click cancel it gives me the two
messages
each two times.

-ted


:

"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

hey ken,

there were a couple of add'l places in my form where this sort of
msgbox
command was needed. i am trying to adapt your rewrite for it and
it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised YOU
ARE
ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel
+
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be
advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this
Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel
+
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be
advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

:

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow
for
the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson underlying
the
code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may result
in
the
deletion
of an AE which continues past the current cycle, i.e. Cycle
#" &
_
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of
this
cycle's
data, there may be AEs listed as continuing past the
preceding
one
requiring
your attention!", vbCritical + vbOKCancel, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into my
posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ") in
this
cycle!
This
may result in the deletion of an AE which continues past
the
current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of
this
cycle's
data,
there may be AEs listed as continuing past the preceding
one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to
tell
the
user
that
something really serious is about to happen if they
proceed
on
their
selected
path.
the system generates a message that's also informative,
but i
want
to
make
really really sure that the user 'gets it'. in testing my
code,
i
have
found
that when i click the ok from my message prompt and then
click
'No'
from
the
system's generated message that i get a message box
telling
me
'The
RunCommand Action Was Canceled', 'Runtime Error 2501'.
when i
attempt
to
debug it, the following line in the vba code is
highlighted
in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba
newbie?
Click to expand...
Click to expand...
Click to expand...
 
K

Ken Snell [MVP]

Not knowing its relationship to the topic of this thread, my first reaction
would be to start a new thread.

--

Ken Snell
<MS ACCESS MVP>

Ted said:
if it's not broken, why fix it :)

thanks a bunch!!

btw, i don't know if i should append my latest little cunundrum to this
posting,

Ken Snell said:
I misread that the two message boxes were the same. Ignore that last
suggested code, and stick with what's working! < g >

--

Ken Snell
<MS ACCESS MVP>

Ted said:
hi ken,

i used the following code you suggested in the subform

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#" &
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" & Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


and when i click 'Cancel' following the system generated message asking
about whether or not i want to continue (following the msgBox message
above),
it works.
so i guess i'm not clear as to what the one you last used was intended
to
do. sorry about being this dense.



:

The reason it runs the msgbox each time is because it needs to
evaluate
all
the logical tests in the If statement in order to decide whether the
final
result if true or false.

I note, by the way, that both conditions for Me.Continuing.Value (yes
and
no) are designed in your code to ask the msgbox question.

Just guessing that you only want to do this if the Me.Continuing.Value
value
is yes, change the code to this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's (#"
&
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

i'm rareing to try this out, but i reckon it's gotta wait 'till
tomorrow
:-(
i guess i'll never understand that counterintuitive bit about how
vba
runs
MsgBox fn running irrespective of what's happening wrt
Me.Continuing.Value.
but i won't look a gift horse in the mouth!

many thanks, ken!

-ted


:

I kinda figured that was what you meant, but wanted to be sure.

The reason is because you've put both tests in a single If
statement,
so
VBA
runs the MsgBox function regardless of the value in
Me.Continuing.Value.
You
need to nest these If tests so that the second one is done only if
the
first
test is valid. Something like this:


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" Then
If vbOK = MsgBox("Be advised YOU ARE ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this Patient's
(#"
&
Me![Patient Number] & ") current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
ElseIf Me.Continuing.Value = "No" Then
If vbOK = MsgBox("Be advised YOU ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel +
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>

hi ken,

i'll try to decode it for you:

when the answer's "Yes" and i click cancel it gives me the same
message
twice. when the answer's "No" and i click cancel it gives me the
two
messages
each two times.

-ted


:

"coughing"? means what specifically?

--

Ken Snell
<MS ACCESS MVP>

hey ken,

there were a couple of add'l places in my form where this sort
of
msgbox
command was needed. i am trying to adapt your rewrite for it
and
it's
coughing; can you find where the bug is:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If Me.Continuing.Value = "Yes" And vbOK = MsgBox("Be advised
YOU
ARE
ABOUT
TO IRRETRIEVABLY DELETE AN AE which continues past this
Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical +
vbOKCancel
+
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "Yes" And vbCancel = MsgBox("Be
advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE which continues past this
Patient's
(#" &
Me![Patient Number] & ") current cycle.", vbCritical +
vbOKCancel
+
vbDefaultButton2, "Critical") Then
GoTo Continue:
ElseIf Me.Continuing.Value = "No" And vbOK = MsgBox("Be
advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel
+
vbDefaultButton2, "Critical") Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
ElseIf Me.Continuing.Value = "No" And vbCancel = MsgBox("Be
advised
YOU
ARE
ABOUT TO IRRETRIEVABLY DELETE AN AE of this Patient's (#" &
Me![Patient
Number] & ") from the current cycle.", vbCritical + vbOKCancel
+
vbDefaultButton2, "Critical") Then
GoTo Continue:
End If

Continue:
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub

:

I believe it's because of this step in your original code:
ElseIf Response = 2 Then GoTo Start2

To be correct, it should have been two code steps:
ElseIf Response = 2 Then
GoTo Start2

I provided the rewrite to show how to use a more logical flow
for
the
code;
not the old BASIC GoTo actions, but rather the more desired
If..Then...Else
structure.

--

Ken Snell
<MS ACCESS MVP>


but of course.....
and it looks like a winner; but what's the lesson
underlying
the
code
here?
why does it work and mine not (apart from my being newbie)?

-ted


:

May I suggest a slight rewrite of your code?


Private Sub DeleteRecord_DblClick(Cancel As Integer)
On Error GoTo Err_DeleteRecord_Click
If vbOK = MsgBox("Be advised you are about to IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & _
Me![Patient Number] & ") in this cycle! This may
result
in
the
deletion
of an AE which continues past the current cycle, i.e.
Cycle
#" &
_
Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion
of
this
cycle's
data, there may be AEs listed as continuing past the
preceding
one
requiring
your attention!", vbCritical + vbOKCancel, "Critical")
Then
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord
End If
Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
End Sub


--

Ken Snell
<MS ACCESS MVP>


wouldn't you know it, i forgot to copy the vba code into
my
posting:

Private Sub DeleteRecord_DblClick(Cancel As Integer)
Dim Response As Integer
Response = MsgBox("Be advised you are about to
IRREVERSIBLY
DELETE
ALL
records of this patient's (#" & Me![Patient Number] & ")
in
this
cycle!
This
may result in the deletion of an AE which continues past
the
current
cycle,
i.e. Cycle #" & Me.[Current Cycle Number] & " !" _
& " Moreover, be advised that upon succesful deletion of
this
cycle's
data,
there may be AEs listed as continuing past the preceding
one
requiring
your
attention!", 17, "Critical")
If Response = 1 Then
GoTo Start1
ElseIf Response = 2 Then GoTo Start2
End If
On Error GoTo Err_DeleteRecord_Click
Start1:
Me.AllowDeletions = True
RunCommand acCmdDeleteRecord

Exit_DeleteRecord_Click:
Me.AllowDeletions = False
Exit Sub

Err_DeleteRecord_Click:
'Ignore "error" if user cancels delete; otherwise
' show the error message.
If Err.Number <> 2501 Then
MsgBox Err.description
End If
Resume Exit_DeleteRecord_Click
Start2:
End Sub

please excuse..

:

as you can see from my humble vba code below, i want to
tell
the
user
that
something really serious is about to happen if they
proceed
on
their
selected
path.
the system generates a message that's also informative,
but i
want
to
make
really really sure that the user 'gets it'. in testing
my
code,
i
have
found
that when i click the ok from my message prompt and
then
click
'No'
from
the
system's generated message that i get a message box
telling
me
'The
RunCommand Action Was Canceled', 'Runtime Error 2501'.
when i
attempt
to
debug it, the following line in the vba code is
highlighted
in
yellow:

RunCommand acCmdDeleteRecord

anyone got a clue why this is happening to this vba
newbie?
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

Run-Time Error 2501 The OpenForm action was cancelled 1
Still getting error 2501 on DeleteRecord 2
Run Time Error 2501 1
Run-time error '2501' 2
The RunCommand action was cancelled(Run-time error '2501') 1
Run-Time Error 2501 4
I am keep getting error 2501 1
Runtime error 2501 2

Top