undo saving subform data

Hi all
I have a main form(company_price_master) in which i have a subform (Company_Price_master subform)as a datasheet. now i want an exit button on main form which when clicked should undo any changes in subform. i have put the code like this on exit button :
Me![Company_Price_master subform].Form.Undo

but it does not work ie it saves rather than undo.

i have searched for this in all forums but have not got solution for the same.

also for your information : there is no link between 2 forms.

pls help its  urgent.

neha
neha7777Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nico5038Commented:
The only solution will be to copy the subform's records into a temporary table.
When cancel is pressed you do nothing, when save is pressed you need to delete the original rows from the original table and append the updated ones.

Clear ?

nIC;O)
neha7777Author Commented:
thanks nic but thats very difficult i guess, isn't there is something easy????
nico5038Commented:
Not too difficult, just a few INSERT and DELETE statements.
When multiple rows can be edited (and/or inserted and deleted) there's no real other option.

What's the recordsource of your subform and why isn't it linked to the main form ?

Nic;o)
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

IainTheVBALearnerCommented:
Try this

DoCmd.RunCommand acCmdUndo

I used this on an old db.  I now have all my forms unbound.  Only when the user clicks an update button, and the data is validated does it update the tables.  So exiting any time before that makes no changes.

Iain
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can undo the save from the Subform, but not the Mainform ... as soon as you move focus off the subform (by clicking the "Undo" button on your subform, for example), Access will save the data on the subform ... you can't get around it, and the only option is as Nico and Iain suggest. You can force the user to accept the changes as they move off the subform by using code in the BeforeUpdate event of the subform, but I suspect this isn't what you're looking for
Gustav BrockCIOCommented:
You could save a backup of the sub at the OnCurrent event of the mainform:

Set rstBackup = Me!subChild.Form.RecordsetClone.Clone

Then, if the user presses Cancel, loop through this and the current RecordsetClone of the subform and reset field values by field. Then delete appended (new) records and finally add deleted old records.
You may, however, get into trouble with AutoNumbers, thus I think I would stick with the temp table and a couple of queries as suggested by Nico.

/gustav
thenelsonCommented:
As soon as you leave the subform and go to the parent form (or visa versa), the record is saved and can't be undone. However if the record is still displayed, you can delete it with:
Me.[Company_Price_master subform].SetFocus
DoCmd.RunCommand acCmdDeleteRecord

Although you may have a problem with the SetFocus command with the underscores and space in the subform name.
neha7777Author Commented:
Actually
company_price master has this structure
starting range   ending_range  amount      change_date
1                        4                  666             2.12.05
5                        6                  8888           2.12.05
7                        10                76868         2.12.05

as u can see against single change_date multiple records can be enterd. so i do not want the user to enter the change_date again and again. HEnce  i want this as a subform (datasheet view) with only starting range ,  ending_range,  amount      and change_date in main form. So that when user choose a change_date , he can enter multiple records in datasheet of subform which has (starting range ,  ending_range,  amount only). but the problem as i wrote above is that incase user do not want to save this and cllicks Exit button , then the recors should not be saved. but currently it is saving. pls help..its very urgent.
Gustav BrockCIOCommented:
Well, saved is saved and those records _are_ saved when you exit the subform.

However, if initially no records have been entered in the subform datasheet and you user types in one or more records but then wish to cancel these (all) entries, all you need is to delete those records when clicking the Exit button:

Private Sub btnExit_Click()

  Dim rst As DAO.RecordSet

  Set rst = Me![Company_Price_master subform].Form.RecordsetClone

  While Not rst.EOF
    rst.Delete
    rst.MoveNext
  Wend
  rst.Close

  Set rst = Nothing

End Sub

A reference (Tools, References) to "Microsoft DAO 3.xx Object Library" is needed.

If records may exist in the subform when you users makes some changes or add or delete some records, then you will have somehow to store the initial records so you later can throw away the new/edited records and replace them with the original (saved) records. You can save them as a temp table, a recordset or in an array but I guess for you the simplest method is to use a temp table as suggested by Nico.

/gustav
nico5038Commented:
OK, the temp table solution:
1) Create a copy of table Company_Price_master named Company_Price_master_Temp
2) Bind your subform to this temp table
3) In the mainform's OnOpen event code:
    currentdb.execute ("delete * from Company_Price_master_Temp;")
    currentdb.execute ("INSERT INTO Company_Price_master_Temp SELECT * FROM Company_Price_master;")
4) Add [Cancel] and [Save] button with code:
    Cancel:
     docmd.close
     Save:
     ' start with testing rows when needed
    currentdb.execute ("delete * from Company_Price_master;")
    currentdb.execute ("INSERT INTO Company_Price_master SELECT * FROM Company_Price_master_Temp;")
    docmd.close

Nic;o)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
neha7777Author Commented:
Thanks Nic..that was perfect..and thanks anyways gustav..
nico5038Commented:
Glad I could help, success with the application!

Nic;o)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.