Aborting form entry

[Peter Hallett]

Access seems to suffer from a major defect (correct me if I am wrong). If a
form is bound to a table then there appears to be no simple way of abandoning
any subsequent data entry. Whatever method of exit is chosen, the contents
of the form are saved to the bound table when the form is closed. (It was
pointed out to me by one of this newsgroup’s MVPs that ‘No Save’ does not
mean what I, and many others, I gather, assumed it to mean, until disabused
of the misapprehension.)

This can be a serious limitation. If, for example, extensive amendments are
made to an employee’s personal record, only to discover, at a late stage,
that the changes have been carried out on the wrong record, then the logical
way to rectify the situation would be to abandon the form and start again but
that does not seem to be possible. The alternative is to return all the
fields to their original values before closing the form but that is often
impractical, or at best very inconvenient – even for those with a good memory.

I have frequently overcome the problem by copying selected records to a
buffer table, using an append query, then binding the form to that buffer
table. Aborting data entry is then easy. On closing the form, its fields
are saved to the buffer table which can then be deleted without affecting the
source table. If the values are to be saved, then the source table is
updated from the buffer table using one or more update queries but this is
not always the most appropriate solution. With a continuous form bound to a
whole table, for example, rollback might be a more satisfactory technique,
starting a transaction, based on the bound table, when the form is opened,
and presenting the options of either committing the transaction or rolling it
back when data entry is complete – but I cannot get this to work. I have
studied the example accompanying the Help topic ‘BeginTrans, CommitTrans,
Rollback Methods’ but although the code I have written, based on this
example, compiles and runs without complaint, it does nothing. Whatever I
do, all fields modified on a form are always saved back to its bound table.
Can some kind soul please help, ideally with a few lines of VBA?

 

[Lynn Trapp]

Have you tried putting something like the following code in the BeforeUpdate
event of your form?

If MsgBox("Do You Wish To Save", vbYesNo) = vbNo Then
Cancel = True
End If

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Rick Brandt]

Access seems to suffer from a major defect (correct me if I am
wrong). If a form is bound to a table then there appears to be no
simple way of abandoning any subsequent data entry. Whatever method
of exit is chosen, the contents of the form are saved to the bound
table when the form is closed. (It was pointed out to me by one of
this newsgroup's MVPs that 'No Save' does not mean what I, and many
others, I gather, assumed it to mean, until disabused of the
misapprehension.)

This can be a serious limitation. If, for example, extensive
amendments are made to an employee's personal record, only to
discover, at a late stage, that the changes have been carried out on
the wrong record, then the logical way to rectify the situation would
be to abandon the form and start again but that does not seem to be
possible. The alternative is to return all the fields to their
original values before closing the form but that is often
impractical, or at best very inconvenient - even for those with a
good memory.

Pressing <Escape> twice in a row will cancel all unsaved changes made in a
bound form.

If that is not acceptable then you need to use unbound forms.

 

[Peter-d]

You could also create a "Save" button on your form with code such as
Private Sub cmdSave_Click()
DoCmd.RunCommand acCmdSaveRecord
End Sub

And a "Close Form" button with code such as

Private Sub cmdClose_Click()
If Me.Dirty = True Then
Me.Undo
End If
End Sub

Alternatively, you can put a user prompt to save or not.
Private Sub cmdClose_Click()
If Me.Dirty = True Then
lret = MsgBox("Do you want to save the current changes?",
vbYesNo)
If lret = vbYes Then
DoCmd.RunCommand acCmdSaveRecord
ElseIf lret = vbno then
Me.Undo
ElseIf lret = vbcancel then
Exit Sub
End If
End If
DoCmd.Close acForm , me.Name
End Sub

You will need to disable the close button for that form. I don't know
however how you can prevent the users from simply closing the access window
and bypassing this.
One way I can see if to have a module level boolean flag that you set to
true when you click the save button (before calling the saverecord
command)(or set it to yes when you choose save in the close button procedure
above) and the "before update" event checks that this flag is true, otherwise
it cancels the event.

 

[Peter Hallett]

Thank you for your suggestion but it appears not to work as required.

I have a continuous form bound to a table. It comprises about six records
relating to the same number of individuals. Each record has a cash deposit
field and a balance field. When a cash deposit is made, the balance is
automatically updated. Being a continuous form, account servicing can be
done for any number of individuals while the form is on display. The problem
with the method you suggested is that the Before Update event code actually
runs as each individual deposit field is updated, rather than on form
closure. It is clearly impractical to have to accept or cancel each
individual entry at the point of submission when what is needed is the
ability to accept or reject the whole form at the point of closure. I cannot
make your code perform this task.

 

[Peter Hallett]

I have tried your suggestion but the result, at least on my system, is
nothing whatever. My form just sits looking at me and when I close and
reopen it, all the changes I have made have been saved.

 

[Peter Hallett]

I have tried using Undo commands in a manner very similar to that which you
suggest but I have as yet failed to achieve any result. Nothing whatever
seems to happen.

 

[Albert D.Kallal]

Thank you for your suggestion but it appears not to work as required.

I have a continuous form bound to a table.

Ok, that is a big detail you left out!!!

You can most certainly cancel forms update to a record. So, lets try and be
clear about the problem.

The fact that you have "many" records that might be (or not be) updated,
and you THEN want to bail out is the issue here (I always stunned when I see
a rather large post of yours..but fail to mention the obvious!!! You start
talking about the in-ability of ms-access to bail out a record update, and
of
course go on talking about continues forms. Now you got about 5+ answers
that don't address your question because you where NOT clear on this
issue from the start

So, we can replace your whole question with:

I have a form that can update many records. After the user updates MANY
records, is there a way to un-do these updates?

(now, that was not too long and hard...was it???).

If you are saying that this is shortcoming of ms-access, I most certainly
agree
with you. There are few things you can try:

Use Temp tables. You already mentioned this one.

Another solution is to use begintrans/end trans.

You should note that "begin trans" / "end trans" / "roll back" ONLY works
with
code, and NOT forms. In other words, data processing routines that you write
in VBA (open a reocrdset...process...close) is what transactions apply to.

Again, transactions do NOT apply to forms.

However, (since a2000 and later), what you can do is create the recordset in
code..and then bind that reocrdset to a form. That way, you can in fact use
roll back on your continues form.....

There is an example of how to do there here:

http://support.microsoft.com/?kbid=248011

However, for a main form, and a child form with master/child links set, the
above will not work.

However, for you simple continuous form..it will...

(and, lets hope you did not leave out that you are dealing with a one to
many relationship, and main form + sub form....).

Patient: I took a rope..and it is hard to pull the rope.
Doctor: Gee, why... a rope is easy to pull...
Patient: sorry, I forgot to mention that a cow was attached to the rope....

 

[Peter Hallett]

Well, I am, of course, very grateful and not a little humbled to have a
dressing down from a real expert but before you get too irate could I point
out, in my defence, that the situation is not quite as you portray it. My
problem arose with ALL forms, not just continuous ones. It was not until I
tried the solutions I had been offered that it became clear that continuous
forms were going to be the stumbling block. I would like to have the power
of foresight but, unlike MVPs, I fear that it is something I lack.

The reasons that I am prepared to risk the wrath of the experts is that (a)
I just want to solve my problems and write better Access code and (b) since
you admit that the inabilitiy to quit forms and abandon changes is a problem
with Access, there must be a lot of other non-experts, like me, who have
found themselves in similar difficulties. So let me restate this problem in
terms that I trust will not raise your blood pressure:-

"I have a table on which I will carry out a series of operations which will
change the data in that table. I then wish to have the option of either
retaining the amended data or restoring the table to its original state."
That is neither too prolix nor too vague, I hope. Now we don't have to make
distinctions between continuous forms and other types.

From all the advice I have received, for which I am very grateful, the
answer seems to be that there is no easy one-size-fits-all solution and that
the simplest technique is probably to continue to use temporary buffer
tables, as I have been doing. The problem with that approach, of course, (and
I state this in detail for the possible benefit of anyone else with similar
problems, who may be reading this post) is that, if the original table is
involved in a relationship, it cannot be deleted or replaced with the buffer
table. Its records can, however, be erased with a delete query, because this
leaves the table structure in place. Replacement is then effected from the
buffer table with update or append queries. That is not exactly a trivial
procedure but it is the best I can do with my current knowledge and skills.

Would you disagree with that restatement of the situation? I'll risk
another lecture if you would like to give me more of your advice.

PS I have not done a word count but the size of your 'rather large reply'
is one of the few that I have had which rivals the size of my 'rather large
posts' - but I am not complaining, far from it. The bigger the response, the
more information I get. I might even find a use for the rope, if I can get
the cow off it.

 

[Albert D.Kallal]

not a little humbled to have a
dressing down from a real expert but before you get too irate could I
point
out, in my defence, that the situation is not quite as you portray it.

Yikes...I hope my post was not viewed as a dressing down.

My
problem arose with ALL forms, not just continuous ones.

Ah..then I stand corrected!!! I was very much under the impression that when
you tried some of suggest (using the before update "cancel"), the reason why
those answers were of no use to you because you failed to mention that you
were using a continues form. (it looks like I am dead WRONG on this
assumption!!!). My apologies to you!

It was not until I
tried the solutions I had been offered that it became clear that
continuous
forms were going to be the stumbling block.

Ok...that really fair (nice) on your part to point out the above. Really,
when reading your post, I had assumed that the advice given what ONLY no use
to use since you had left out the fact of a continues form. Those examples
posted should allow you to bail out on a record on a form with ease. The
fact
that those suggestions did not work for you really threw me off. (they
should work). The fact that you are being gentleman, and come back and
say..hey, you know what..those continues forms are really going to be a
problem!!
Your quest for a solution, and questions are good. Don't let some person who
snapped at you (me) take anything away here.

I would like to have the power
of foresight but, unlike MVPs, I fear that it is something I lack.

hey, that is ok. I think the fact that I spent time mention that
transactions CAN in fact be used for forms does help.

The reasons that I am prepared to risk the wrath of the experts is that
(a)
I just want to solve my problems and write better Access code and (b)
since
you admit that the inabilitiy to quit forms and abandon changes is a
problem
with Access, there must be a lot of other non-experts, like me, who have
found themselves in similar difficulties.

Yikes...wrath seems to hard!! I was just trying to point out that you got a
whole truck load of answers....and none really were of use to you! (and, you
point out that the issue was NOT *just* continues forms here!!).

"I have a table on which I will carry out a series of operations which
will
change the data in that table. I then wish to have the option of either
retaining the amended data or restoring the table to its original state."
That is neither too prolix nor too vague, I hope. Now we don't have to
make
distinctions between continuous forms and other types.

Ok, I going to have clear one thing on the above:

If those "series of operations" is a bunch of code that updates reocrdssets,
then
a simple rollback, or commit will do the trick.

However, if those series of operations is done by a user forms, then commit,
and roll back will not work and is useless to you.

So, if you got some code that processes data, and commit/rollback is a snap.

Would you disagree with that restatement of the situation? I'll risk
another lecture if you would like to give me more of your advice.

Hum, again, I feel bad that my post seemed like a lecture. And, I freely
admit I did miss understand the reasons for you responding and telling
people
the solutions given were of no use.

however, at the end of day, no problem here. I going to work a bit harder
and try not to misunderstand posts like this.

the bigger the response, the
more information I get. I might even find a use for the rope, if I can get
the cow off it.

Right on..that is funny! You know, I think I am going to STOP trying to use
humor
in my posts.....I don't think it comes off the right way!!

So, as mentioned, if you forms do NOT have a sub-form and maintain a
parent/child relationship, then binding your own reocdsets to code is VERY
easy here.

is that, if the original table is
involved in a relationship, it cannot be deleted or replaced with the
buffer
table.

Very good point.

I will say that the binding of a form to a recordrset and using transactions
*WILL* work even when the tables are involved with relations. (you just
can't use the built in master/child links for a form when you do this).

I going to post a continues form example ASAP that shows you how to
bail out..and I guessing it should take less then 10 lines of code.

 

[Peter Hallett]

Thankyou. That really was a most gracious response. I am sorry that I have
not replied before but an overloaded lorry, or truck, as you may prefer to
call it, brought down my overhead telephone line six days ago and it has only
just been repaired. I have been more or less incommunicado during the
interval.

As a result of everyone's help, I think I now understand the ramifications
of form closure and data saving more clearly. As a result, any hope that I
had overlooked the 'magic button' has evaporated. To misquote a well-known
saying, it would appear that you can discard some data in all circumstances
and all data in some circumstances but what you can't do is discard all data
in all circumstances. I will therefore have to stick to the use of temporary
tables. Even though the procedure is rather involved, it is the nearest I am
likely to get to a 'one size fits all' solution.

As to the cow, I am afraid it had to go. It was eating all the flowers in
the garden and my wife demanded that I take it to market. However, on the
way, I met a man who offered to exchange it for a bag of magic beans. I
accepted. (Apart from anything else it saved me a long walk.) Do you think
that was wise?

 

[SIsfeld]

Solution listed in the example work perfectally for my difficulty with a
simple continuous form.

 

[alaskahawk]

This worked great except my code in another form which "calls" this form upon
a double-click no longer works, it just goes to the first record. Here is
the code:

Private Sub Form_DblClick(Cancel As Integer)

DoCmd.OpenForm "F_Master_List_Form", WhereCondition:="[Identity_Number] = "
& Me![Identity_Number]

End Sub

Any ideas on that??