How do I exit a form without saving the entered data in a table?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I created a form that takes data entered by users and saves it to a table. I
added a "Cancel" button. If a user begins to enter information in the form,
but decides not so save it to the table, I want this button to close the form
and not save the entered data to the table. Are there any suggestions?
 
Sharon said:
I created a form that takes data entered by users and saves it to a table. I
added a "Cancel" button. If a user begins to enter information in the form,
but decides not so save it to the table, I want this button to close the form
and not save the entered data to the table. Are there any suggestions?
Click to expand...

For 'canceling' a new record, you'll need the macro to build a delete query
that deletes the record with the id number that you grab from the control
before you close the form.

So something like this (off top of head, no guarantees!):

drec=me.ctlID
sstring="delete * from tblMyTable where [id]= " & drec
docmd.closeform "MyForm"
docmd.runsql sstring

That way, assuming that drec=57, the string ends up looking like "delete *
from tblMyTable where [id]=57", which is what we want. (for future use,
remember that if [field] is text, then the WHERE clause has to have quote
delimiters, like [workername]='Smith', and if [field] is a date it has to
have # delimiters, like [StartDate]=#5/21/06#)

Hope that helps

JK
 
in your cancel button just add

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

This will undo the recent record addition to the table

Hope this helps.

james_keegan via AccessMonster.com said:
Sharon said:
I created a form that takes data entered by users and saves it to a table. I
added a "Cancel" button. If a user begins to enter information in the form,
but decides not so save it to the table, I want this button to close the form
and not save the entered data to the table. Are there any suggestions?
Click to expand...

For 'canceling' a new record, you'll need the macro to build a delete query
that deletes the record with the id number that you grab from the control
before you close the form.

So something like this (off top of head, no guarantees!):

drec=me.ctlID
sstring="delete * from tblMyTable where [id]= " & drec
docmd.closeform "MyForm"
docmd.runsql sstring

That way, assuming that drec=57, the string ends up looking like "delete *
from tblMyTable where [id]=57", which is what we want. (for future use,
remember that if [field] is text, then the WHERE clause has to have quote
delimiters, like [workername]='Smith', and if [field] is a date it has to
have # delimiters, like [StartDate]=#5/21/06#)

Hope that helps

JK
Click to expand...
 
I would think that

Me.Undo

is all you would need (other than error handling) in the Click event for the
Cancel button.

D' Polygon said:
in your cancel button just add

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

This will undo the recent record addition to the table

Hope this helps.

james_keegan via AccessMonster.com said:
Sharon said:
I created a form that takes data entered by users and saves it to a
table. I
added a "Cancel" button. If a user begins to enter information in the
form,
but decides not so save it to the table, I want this button to close the
form
and not save the entered data to the table. Are there any suggestions?
Click to expand...

For 'canceling' a new record, you'll need the macro to build a delete
query
that deletes the record with the id number that you grab from the control
before you close the form.

So something like this (off top of head, no guarantees!):

drec=me.ctlID
sstring="delete * from tblMyTable where [id]= " & drec
docmd.closeform "MyForm"
docmd.runsql sstring

That way, assuming that drec=57, the string ends up looking like "delete
*
from tblMyTable where [id]=57", which is what we want. (for future use,
remember that if [field] is text, then the WHERE clause has to have quote
delimiters, like [workername]='Smith', and if [field] is a date it has to
have # delimiters, like [StartDate]=#5/21/06#)

Hope that helps

JK
Click to expand...
Click to expand...
 
You are correct. The other solutions offered are making it a lot harder than
it has to be.
If there is any code in VBA that drives me up the wall it is the DoMenuItem.
It is clumsy and impossible to read. It is also one of the features
Microsoft included in Access for those who are not really programmers.
Macros fall into that category as well.

BruceM said:
I would think that

Me.Undo

is all you would need (other than error handling) in the Click event for the
Cancel button.

D' Polygon said:
in your cancel button just add

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

This will undo the recent record addition to the table

Hope this helps.

james_keegan via AccessMonster.com said:
Sharon Greene wrote:
I created a form that takes data entered by users and saves it to a
table. I
added a "Cancel" button. If a user begins to enter information in the
form,
but decides not so save it to the table, I want this button to close the
form
and not save the entered data to the table. Are there any suggestions?

For 'canceling' a new record, you'll need the macro to build a delete
query
that deletes the record with the id number that you grab from the control
before you close the form.

So something like this (off top of head, no guarantees!):

drec=me.ctlID
sstring="delete * from tblMyTable where [id]= " & drec
docmd.closeform "MyForm"
docmd.runsql sstring

That way, assuming that drec=57, the string ends up looking like "delete
*
from tblMyTable where [id]=57", which is what we want. (for future use,
remember that if [field] is text, then the WHERE clause has to have quote
delimiters, like [workername]='Smith', and if [field] is a date it has to
have # delimiters, like [StartDate]=#5/21/06#)

Hope that helps

JK
Click to expand...
Click to expand...
Click to expand...
 
Thank you for the information.

-Sharon

BruceM said:
I would think that

Me.Undo

is all you would need (other than error handling) in the Click event for the
Cancel button.

D' Polygon said:
in your cancel button just add

DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70

This will undo the recent record addition to the table

Hope this helps.

james_keegan via AccessMonster.com said:
Sharon Greene wrote:
I created a form that takes data entered by users and saves it to a
table. I
added a "Cancel" button. If a user begins to enter information in the
form,
but decides not so save it to the table, I want this button to close the
form
and not save the entered data to the table. Are there any suggestions?

For 'canceling' a new record, you'll need the macro to build a delete
query
that deletes the record with the id number that you grab from the control
before you close the form.

So something like this (off top of head, no guarantees!):

drec=me.ctlID
sstring="delete * from tblMyTable where [id]= " & drec
docmd.closeform "MyForm"
docmd.runsql sstring

That way, assuming that drec=57, the string ends up looking like "delete
*
from tblMyTable where [id]=57", which is what we want. (for future use,
remember that if [field] is text, then the WHERE clause has to have quote
delimiters, like [workername]='Smith', and if [field] is a date it has to
have # delimiters, like [StartDate]=#5/21/06#)

Hope that helps

JK
Click to expand...
Click to expand...
Click to expand...
 
BruceM said:
I would think that

Me.Undo

is all you would need (other than error handling) in the Click event for the
Cancel button.
Click to expand...

Me.undo is great for undoing changes to an existing record, and leaving the
record as it was.

What it's not good at is DELETING that record, if the form was an 'addnew'
form which the user half-filled out and then decided to cancel.

Once the user starts filling in the form, the record, with it's new
autonumber, exists, and needs to be removed if the user says 'cancel'. In my
experience, me.undo doesn't do that.

jk
 
james_keegan via AccessMonster.com said:
Me.undo is great for undoing changes to an existing record, and
leaving the record as it was.

What it's not good at is DELETING that record, if the form was an
'addnew' form which the user half-filled out and then decided to
cancel.

Once the user starts filling in the form, the record, with it's new
autonumber, exists, and needs to be removed if the user says
'cancel'. In my experience, me.undo doesn't do that.
Click to expand...

You are mistaken. Me.Undo discards all unsaved changes. If those unsaved
changes are on a new record then the record is discarded.
 
Rick said:
Me.undo is great for undoing changes to an existing record, and
leaving the record as it was.
Click to expand...
[quoted text clipped - 6 lines]
autonumber, exists, and needs to be removed if the user says
'cancel'. In my experience, me.undo doesn't do that.
Click to expand...

You are mistaken. Me.Undo discards all unsaved changes. If those unsaved
changes are on a new record then the record is discarded.
Click to expand...

Ahh, I went back to the application I was remembering, and discovered that
the new record is being created by a proceedure that then calls the form to
open up to that new record. Since the record already exists in the table at
the time the form opens, me.undo doesn't delete that record, which is where I
was getting that from, and why I had to created a delete query to get rid of
that record if the user clicked 'cancel'.

So If the FORM is what is creating the new record, then me.undo will undo the
creation of that record. But if the form is updating an existing record,
then me.undo will undo any changes made to that record, but won't delete the
record itself.

I couldn't figure out exactly *why* I had the proceedure create a new record,
but that's what I did! My guess is that if I were to try to solve the same
problem today, I'd have a better solution!

JK
 
Back
Top