By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,773 Members | 911 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,773 IT Pros & Developers. It's quick & easy.

Can you stop a form from updating the database when exiting?

P: 52
How can I stop a form from updating the database when a user closes the form? I have a form which is used for referencing, additions, and modifications but every time the form is closed with no modifications the entry which is being displayed is then stored in the database causing duplicates. My hope is to only allow additions and or modifications if they select the proper command button. If they just close the form I would like nothing stored to the database. Because the form is used for reference I have the Data Entry set to NO. Is there an easy way to do this?

Please help
Birky
May 24 '07 #1
Share this Question
Share on Google+
12 Replies

NeoPa
Expert Mod 15k+
P: 31,770
In the OnClose event procedure for your form, include something like :
Expand|Select|Wrap|Line Numbers
  1. Me.Dirty = False
I believe the following works too :
Expand|Select|Wrap|Line Numbers
  1. Call Me.Undo
May 25 '07 #2

P: 52
This works if the code is behind a control object which is calling the DoCmd.Close function but if the Forms CloseBox (upper X on the forms border) is used the record is saved before the form is closed. Any idea how I can get this to stop updating and just close the form straight away? Note I have tried the both the Undo and Dirty options within the forms OnClose event and neither seem to stop the saving if this X is selected.

Expand|Select|Wrap|Line Numbers
  1. Private Sub CmdExit_Click()
  2. On Error GoTo Err_CmdExit_Click
  3.  
  4.     Call Me.Undo
  5.     DoCmd.Close
  6.  
  7. Exit_CmdExit_Click:
  8.     Exit Sub
  9.  
  10. Err_CmdExit_Click:
  11.     MsgBox Err.Description
  12.     Resume Exit_CmdExit_Click
  13.  
  14. End Sub
  15.  
May 31 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Try putting this in the On Close event.
Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub Form_Close()
  3.     ' Undo the current record
  4.     DoCmd.DoMenuItem acFormBar, acEditMenu, acUndo, , acMenuVer70
  5. End Sub
  6.  
For some reason Dirty doesn't read True in the On Close event.

Mary
Jun 1 '07 #4

NeoPa
Expert Mod 15k+
P: 31,770
Mary,
Do you know why Me.Undo (or even Call Me.Undo) doesn't work?
Jun 1 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Mary,
Do you know why Me.Undo (or even Call Me.Undo) doesn't work?
Something to do with the form losing focus in the close event. Same problem with Me.Dirty.
Jun 1 '07 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Something to do with the form losing focus in the close event. Same problem with Me.Dirty.
For the sake of experimentation probably should be tried as

Expand|Select|Wrap|Line Numbers
  1.  Forms!frmName.Undo
Jun 1 '07 #7

NeoPa
Expert Mod 15k+
P: 31,770
Thanks.
So, when you use the DoCmd form it passes control out of it's own module to do the job. That makes a sort of sense I suppose.
Jun 2 '07 #8

Expert 100+
P: 344
This works if the code is behind a control object which is calling the DoCmd.Close function but if the Forms CloseBox (upper X on the forms border) is used the record is saved before the form is closed. Any idea how I can get this to stop updating and just close the form straight away? Note I have tried the both the Undo and Dirty options within the forms OnClose event and neither seem to stop the saving if this X is selected.
I would disable the Forms CloseBox using forms properties and add my own Close button. That way, I can make sure the form is closed the way I want it to be closed. Having said that, I had a similar problem problem, with records being saved before I was ready to save them and spent ages putting all sorts of fixes and traps in. Worked fine in London on a PC, but when system went live in Africa running on laptops, certain ways of pressing those rotten touchpads would save the record. Only by disabling the touchpad and using mice on the laptop would the problem go away.
Jun 2 '07 #9

MSeda
Expert 100+
P: 159
Birky,
aside from the discussion on how to undo the changes made on the form. I'm curious as to how it is that the duplicate record is being created in the first place. How does the user select which record to view/edit or select to add a new record. do you have a combo box or record selectors on the form or have you employeed some other method of navigation?
It seems to me, the fact that a duplicate record is being created indicates that some how the existing record the user is accessing is erroneously being populated into a new record rather than the user viewing the existing record.
Jun 2 '07 #10

P: 52
I am actually allowing the user to drill down to specific data. I have three combo boxes which are dependant on each other. Parent combo populates a child combo which then populated a grandchild. These three combo boxes are unbound but once the selections are made the form is then reloaded using the selected criteria which then populated the rest of the form. (Not to mention I have a sub form within that is showing information from yet another table.) I pretty much have everything working except when, not all the options were select and the user exits, and if they were to view an existing record and then exit the freaky thing was creating a duplicate and or new entry. I believe I have fixed this by creating an exit cmd button with the undo command behind it as well as changing the query that pulls the info for the three values to use the primary key instead of a select that uses all three objects. (Had to add some additional code to fetch the PK but I feel better using it instead). Im sure my approach is not the best one, but with my limited knowledge of Access and VB it is the best I can do. Im sure once I get a better grasp of what I am doing Ill find a much better way to program this. As I am just learning this I must state that I am truly grateful for all of you; taking the time to help a novice like myself. You all have been extremely patient and more than willing to help me understand the issues I have ran into. Again I would like to thank you all!

Birky
Jun 4 '07 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
You're welcome Birky.

We all learned by trial and error. It just takes time. You'll get there.

Mary
Jun 4 '07 #12

NeoPa
Expert Mod 15k+
P: 31,770
You're welcome Birky.

We all learned by trial and error. It just takes time. You'll get there.

Mary
Hear hear!

As a tip.
Rather than disabling the Close button, simply code your handling in the Form_Close event procedure. You can still have your own Close (Cancel / Exit / whatever) button, but all it would do is call Close for the form like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExit_Click()
  2.     Call DoCmd.Close(ObjectType:=acForm, ObjectName:=Me.Name)
  3. End Sub
Jun 4 '07 #13

Post your reply

Sign in to post your reply or Sign up for a free account.