Undo button fails

[Jan Il]

Hi All Access 2003 - Windows XP Pro SP2

I have a data entry form on which I have an Undo. The problem is that, if
during an entry the User changes their mind and changes an entry in control,
then decides to cancel the entry altogether, when they click the Cancel
(Undo) button, it give the error message: "Undo is not available at this
time.", then the only way to kill the entry is to exit out of the form, they
reopen. I have not been able to pinpoint the exact problem cause, however,
I am wondering if it may be connected to the change in the control, then
later wanting to undo the entire entry. The Undo button code is the simple
standard:

Private Sub cmdCancelRec_Click()
On Error GoTo Err_cmdCancelRec_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub

I'd truly appreciate it if someone would give me a bit of insight on what
might be the cause of the failure of the Undo to cancel the entry and why.
At least I'd have an idea of what may be happening and where.


Jan
MS MVP - IE/OE
Smiles are meant to be shared,
that's why they're so contagious.

 

[Dirk Goldgar]

Hi All Access 2003 - Windows XP Pro SP2

I have a data entry form on which I have an Undo. The problem is
that, if during an entry the User changes their mind and changes an
entry in control, then decides to cancel the entry altogether, when
they click the Cancel (Undo) button, it give the error message: "Undo
is not available at this time.", then the only way to kill the entry
is to exit out of the form, they reopen. I have not been able to
pinpoint the exact problem cause, however, I am wondering if it may
be connected to the change in the control, then later wanting to undo
the entire entry. The Undo button code is the simple standard:

Private Sub cmdCancelRec_Click()
On Error GoTo Err_cmdCancelRec_Click


DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub

I'd truly appreciate it if someone would give me a bit of insight on
what might be the cause of the failure of the Undo to cancel the
entry and why. At least I'd have an idea of what may be happening and
where.

Jan, does this happen only when they've made an entry in a particular
control? Does it matter what control they've dirtied?

Is this a single, simple form, or is there a subform involved? On what
section of the form is the "cmdCancelRec" button? Does the form still
show as "dirty" -- with the little pencil displayed in the record
selector -- when the user clicks the cmdCancelRec button? After
clicking the button?

If they exit the form after being unable to Undo, when they come back
does the record they were editing still show the changes they were
trying to get rid of, or did those changes vanish?

 

[Allen Browne]

In general, an undo button on a form is not very useful.

If the active control is bound to a field that is Required in its table, and
the user starts typing something and backspaces the answer out, the Access
engine won't let you out of the box until you enter something. That means
when the user clicks Cancel, Access processes the control's events first,
and the command button's Click event never does get executed because you are
stuck there. It is therefore better to teach the user to press the Esc key,
or the Undo button on the toolbar rather than the worse-than-useless command
button.

If the active control is unbound, undo won't be available. In certain
situations the menu may not be available either.

To make it worse, the undo is ambiguous: are you expecting to undo the
control (the field) or the form (the record)? If the intention is to undo
the record, unambiguous code would be:
If Me.Dirty Then Me.Undo
and that also takes care of the cases where Undo is not appropriate
(assuming a bound form.)

But even that doesn't take care of the fact that the command button's Click
event won't fire if the user is stuck in a required field.

 

[Jan Il]

Hi Dirk!

Jan, does this happen only when they've made an entry in a particular
control? Does it matter what control they've dirtied?

It normally happens at this point in one Control. The SuggestedLocation
Control is to enter the location of the part in the their office area, WR S5
1 = Work Room Shelf 5 Row 1. The Control (combo box) is bound to a table
field named SuggestedLocation.

Is this a single, simple form, or is there a subform involved? On what
section of the form is the "cmdCancelRec" button? Does the form still
show as "dirty" -- with the little pencil displayed in the record
selector -- when the user clicks the cmdCancelRec button? After
clicking the button?

There is a subform. The cmdCancelRec button is located in the detail
section of the form.
The form is in Form View and I don't see a little pencil either time, but,
the record has not been saved at the time, although, they tabbed to another
control and then click back to the SuggestedLocation control to make the
change.

If they exit the form after being unable to Undo, when they come back
does the record they were editing still show the changes they were
trying to get rid of, or did those changes vanish?

All prior entries are gone, all controls are blank, and there are no records
saved to the table.

From what I have been able to figure out thus far, the User enters a
location in this Control, then changes their mind about where they think it
should be located, and change the location information, either by selecting
an existing location from the dropdown, or entering a new location, when
gets entered. Then, they decided to the entire record is not as they really
want it, so they click the Cancel button. This then appears to Undo the
last entry only, not the entire record, thus, when they click the Cancel
button again, the error is fired. They did not explain too clearly at what
point in the process the error was being fired.

It does appear that the Cancel button is only undoing the last record
entered on first click, not the entire record, and when they click the
Cancel button again, it does not know what else to do. I've not had this
happen before, as usually when you click the Cancel (Undo) button, the
entire record is cancelled, or at least that has been my experience, even
after making a change in a Contro. That is what confuses me in this case.

Thank you for your time and help, I really appreciate it.

Jan

 

[Jan Il]

Hi Allen!

In general, an undo button on a form is not very useful.

If the active control is bound to a field that is Required in its table,
and the user starts typing something and backspaces the answer out, the
Access engine won't let you out of the box until you enter something. That
means when the user clicks Cancel, Access processes the control's events
first, and the command button's Click event never does get executed
because you are stuck there. It is therefore better to teach the user to
press the Esc key, or the Undo button on the toolbar rather than the
worse-than-useless command button.

If the active control is unbound, undo won't be available. In certain
situations the menu may not be available either.

To make it worse, the undo is ambiguous: are you expecting to undo the
control (the field) or the form (the record)? If the intention is to undo
the record, unambiguous code would be:
If Me.Dirty Then Me.Undo
and that also takes care of the cases where Undo is not appropriate
(assuming a bound form.)

But even that doesn't take care of the fact that the command button's
Click event won't fire if the user is stuck in a required field.

The field is not Required, it can be left empty if they are not sure of a
location. The Control is bound to a field of the same name in the table.
The Undo bottom is meant to undo the entire record, not just a field entry.
What appears to be happening, is that when the change is made to the field
entry, then after moving to another control or at a point before the record
is saved they decide to just cancel the whole record and start over, when
they click the Undo button, it only undoes the last field entry, and then
fires the error when they click it again to try and cancel the entire
record.

Thank you for your time and help, it is very much appreciated.

Jan

 

[Dirk Goldgar]

It normally happens at this point in one Control. The
SuggestedLocation Control is to enter the location of the part in the
their office area, WR S5 1 = Work Room Shelf 5 Row 1. The Control
(combo box) is bound to a table field named SuggestedLocation.

There is a subform. The cmdCancelRec button is located in the detail
section of the form.
The form is in Form View and I don't see a little pencil either time,
but, the record has not been saved at the time, although, they tabbed
to another control and then click back to the SuggestedLocation
control to make the change.

All prior entries are gone, all controls are blank, and there are no
records saved to the table.

From what I have been able to figure out thus far, the User enters a
location in this Control, then changes their mind about where they
think it should be located, and change the location information,
either by selecting an existing location from the dropdown, or
entering a new location, when gets entered. Then, they decided to the
entire record is not as they really want it, so they click the Cancel
button. This then appears to Undo the last entry only, not the
entire record, thus, when they click the Cancel button again, the
error is fired. They did not explain too clearly at what point in
the process the error was being fired.

It does appear that the Cancel button is only undoing the last record
entered on first click, not the entire record, and when they click the
Cancel button again, it does not know what else to do. I've not had
this happen before, as usually when you click the Cancel (Undo)
button, the entire record is cancelled, or at least that has been my
experience, even after making a change in a Contro. That is what
confuses me in this case.

Thank you for your time and help, I really appreciate it.

I'm not sure exactly what's going on, but one thing to try is changing
the code in the Undo button's Click event procedure from:

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

to:

If Me.Dirty Then Me.Undo

I'm not sure whether that's going to make a difference or not, as I
don't have a very clear picture of exactly what's going on. If it
doesn't work, you can send me (if you like) a cut-down copy of the
database (compacted and zipped, please), and I'll have a look at it.

 

[Jan Il]

Hi Dirk

I'm not sure exactly what's going on, but one thing to try is changing
the code in the Undo button's Click event procedure from:


to:

If Me.Dirty Then Me.Undo

I'm not sure whether that's going to make a difference or not, as I
don't have a very clear picture of exactly what's going on. If it
doesn't work, you can send me (if you like) a cut-down copy of the
database (compacted and zipped, please), and I'll have a look at it.

I'll give this a go and see if that helps. As they are new to the form, and
the process, and in the process of rearranging their shop as well, I know
they will likely be making some changes on and off as they go along on this
first session of entering the parts information into the table. Thus, I want
to be able to allow them some flexibility to make such necessary changes
without causing such a situation should they just need to cancel the whole
thing and start over.

I'll give you a heads up if I need to send you the cutdown. <g>

Thank you!

Jan

 

[Jan Il]

Hi Dirk

I'm not sure exactly what's going on, but one thing to try is changing
the code in the Undo button's Click event procedure from:


to:

If Me.Dirty Then Me.Undo

I'm not sure whether that's going to make a difference or not, as I
don't have a very clear picture of exactly what's going on. If it
doesn't work, you can send me (if you like) a cut-down copy of the
database (compacted and zipped, please), and I'll have a look at it.

Here is what I have in the OnClick Event of the Cancel button now, having
followed your replacement instructions above. But, it is still only undoing
the last control entry, not the entire record, and I made no changes in any
of the controls. Although now, when you click the Cancel button the first
time it undoes the last control entry, but, when you click it again, there
is no error message now. It just does nothing, and you still have to X out
of the record to clear it.

Private Sub cmdCancelRec_Click()
On Error GoTo Err_cmdCancelRec_Click

If Me.Dirty Then Me.Undo

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub

*****************************************

I knows the drill, Cap'n <s>....so I'll send you a cut-down of the original
db and a step-by-step explanation of what the Users are doing that seems to
be setting this problem in motion so that, hopefully, you can repro the
event and help make the problem a bit clearer.

Thank you!

Jan

 

[Dirk Goldgar]

Here is what I have in the OnClick Event of the Cancel button now,
having followed your replacement instructions above. But, it is
still only undoing the last control entry, not the entire record, and
I made no changes in any of the controls. Although now, when you
click the Cancel button the first time it undoes the last control
entry, but, when you click it again, there is no error message now.
It just does nothing, and you still have to X out of the record to
clear it.

Private Sub cmdCancelRec_Click()
On Error GoTo Err_cmdCancelRec_Click

If Me.Dirty Then Me.Undo

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub

*****************************************

I knows the drill, Cap'n <s>....so I'll send you a cut-down of the
original db and a step-by-step explanation of what the Users are
doing that seems to be setting this problem in motion so that,
hopefully, you can repro the event and help make the problem a bit
clearer.

I've looked at the database, and I see what the problem is. As I
suspected, the problem is due to the subform. For reference, and for
the benefit of others who maybe reading this thread, I'll quote the
sequence of actions that cause the problem, as you detailed in your
private e-mail to me:

<quote>
1. The User enters information up through the Category entry and tab to
the
MfgRetailPrice control.
2. User then decides that perhaps the part should be in another
location.
3. User goes back to the SuggestedLocation control and changes the
location
code.
4. User goes back to the MfgRetailPrice to continue entry, but,
discovers
that they have entered inforamtion for the wrong part.
5. User clicks on Cancel button to delete the record entry to start
over.
6. The first click undoes the last changes in the SuggestedLocation
control
7. The User click the Cancel button again to delete the entire record
and
the error message that the Undo is not available is fired, thus, the
only
way to undo the record is to exit the form.
8. User clicks the X in upper right hand corner and form closes. When
the
form is opened again, all previous records are cleared.
</quote>

On this form, the category entry is made on a subform, sfmCategory, and
the main form's record becomes "un-undoable" after the user has entered
a category on, or merely tabbed through, the sfmCategory subform. This
is because Access automatically saves the main form's record when the
focus goes to the subform. It does this to ensure that the main record
has been created before any related records are created, and there is
absolutely no way to keep it from doing it.

But once the main form's record has been saved, it can't be undone.
Only unsaved records can be undone. Your only choice at this point is
to delete the record, which isn't quite the same thing as undoing it.
You might extend the logic behind your Undo button to detect this
situation and offer a choice, like this:

'----- start of revised code -----
Private Sub cmdCancelRec_Click()

On Error GoTo Err_cmdCancelRec_Click

If Me.Dirty Then
' Just undo the changes that have been made to this record.
Me.Undo
Else
' If this is a new record, there's nothing to undo,
' and nothing to delete, either.
If Me.NewRecord Then
' do nothing
Else
' This record has already been saved.
' Does the user want to delete it?
If MsgBox( _
"Do you want to delete this record?", _
vbYesNo, _
"Confirm Delete") _
= vbYes _
Then
RunCommand acCmdDeleteRecord
End If
End If
End If

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub

'----- end of revised code -----

That may not be what you want to do, of course, but it's one way of
handling it. And since the form is a data entry form, it makes some
sense to treat it that way. You could even take out the confirmation
prompt, if you think it isn't necessary.

I was perplexed at first by your statements that, after the undo failed,
"the only way to undo the record is to exit the form. [...] User clicks
the X in upper right hand corner and form closes. When the form is
opened again, all previous records are cleared." If the record couldn't
be undone, what happened to it? But it turns out that the form has its
Data Entry property set to Yes, so it will never show any existing
records when you open it. My tests have confirmed that the records do
in fact remain in the table, once the form has automatically saved them,
unless you delete them. It's just that the form isn't showing them.

 

[Jan Il]

Hi Dirk

I've looked at the database, and I see what the problem is. As I
suspected, the problem is due to the subform. For reference, and for
the benefit of others who maybe reading this thread, I'll quote the
sequence of actions that cause the problem, as you detailed in your
private e-mail to me:

<quote>
1. The User enters information up through the Category entry and tab to
the
MfgRetailPrice control.
2. User then decides that perhaps the part should be in another
location.
3. User goes back to the SuggestedLocation control and changes the
location
code.
4. User goes back to the MfgRetailPrice to continue entry, but,
discovers
that they have entered information for the wrong part.
5. User clicks on Cancel button to delete the record entry to start
over.
6. The first click undoes the last changes in the SuggestedLocation
control
7. The User click the Cancel button again to delete the entire record
and
the error message that the Undo is not available is fired, thus, the
only
way to undo the record is to exit the form.
8. User clicks the X in upper right hand corner and form closes. When
the
form is opened again, all previous records are cleared.
</quote>

On this form, the category entry is made on a subform, sfmCategory, and
the main form's record becomes "un-undoable" after the user has entered
a category on, or merely tabbed through, the sfmCategory subform. This
is because Access automatically saves the main form's record when the
focus goes to the subform. It does this to ensure that the main record
has been created before any related records are created, and there is
absolutely no way to keep it from doing it.

But once the main form's record has been saved, it can't be undone.
Only unsaved records can be undone. Your only choice at this point is
to delete the record, which isn't quite the same thing as undoing it.
You might extend the logic behind your Undo button to detect this
situation and offer a choice, like this:

'----- start of revised code -----
Private Sub cmdCancelRec_Click()

On Error GoTo Err_cmdCancelRec_Click

If Me.Dirty Then
' Just undo the changes that have been made to this record.
Me.Undo
Else
' If this is a new record, there's nothing to undo,
' and nothing to delete, either.
If Me.NewRecord Then
' do nothing
Else
' This record has already been saved.
' Does the user want to delete it?
If MsgBox( _
"Do you want to delete this record?", _
vbYesNo, _
"Confirm Delete") _
= vbYes _
Then
RunCommand acCmdDeleteRecord
End If
End If
End If

Exit_cmdCancelRec_Click:
Exit Sub

Err_cmdCancelRec_Click:
MsgBox Err.Description
Resume Exit_cmdCancelRec_Click

End Sub

'----- end of revised code -----

Ok...I see now what is happening and where the problem involves the
subform..

That may not be what you want to do, of course, but it's one way of
handling it. And since the form is a data entry form, it makes some
sense to treat it that way. You could even take out the confirmation
prompt, if you think it isn't necessary.

Yes, that would make sense with the structure of the form. I think at
present it would be prudent to leave the confirmation in, given that all
Users are new to using a database entry program, and new to the entire
process of how to enter data. Thus, at this point, I feel that the prompt
before they make any other changes would give them a chance to review the
information as it is, and decide what they really want to do. The entire
process of entering the part data is going to be totally new, and I am sure
they will need to make some revisions as they go along in regards to storage
locations and various categories. I want to insure the integrity of the
data, but, also allow some flexibility to make necessary adjustments during
the entry process.

I was perplexed at first by your statements that, after the undo failed,
"the only way to undo the record is to exit the form. [...] User clicks
the X in upper right hand corner and form closes. When the form is
opened again, all previous records are cleared." If the record couldn't
be undone, what happened to it? But it turns out that the form has its
Data Entry property set to Yes, so it will never show any existing
records when you open it. My tests have confirmed that the records do
in fact remain in the table, once the form has automatically saved them,
unless you delete them. It's just that the form isn't showing them.

Ahh...ok...that explains why the fields appear to be empty.

I have tested the new code using the User steps as described above, and it
seems to be working as expected. I will have a better idea once it is in
actual practice and see how the Users will work with it.

Thank you very much for your time and help, I really do appreciate it.
Looking forward to seeing you at the Summit next week. )

Jan

MS MVP - Internet Explorer
Smiles are meant to be shared,
that's why they're so contagious.