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

Clear form on exit without table updates

P: 19
Hi, I had this on a prior post and still have some difficulty trying to make it work and the issue might just be very simple. I have a form with multiple tabs on it and each one contains a subform that will update fields in a different table. I have the cycle property on each subform on each tab set to "Current Page" so that there is no more than one record generated per tab and that works fine. The problem I have is if the user will "x" off or closes the form the incomplete record is written to the tables and I only want an update to occur if the user clicks the save button. I coded the on close event in the main and subform properties with the following but it does not stop the update:

Private Sub Form_Close()
Call Me.Undo
End Sub

Any help would be much appreciated.

Thanks Mark.
Feb 27 '07 #1
Share this Question
Share on Google+
3 Replies


ADezii
Expert 5K+
P: 8,597
Hi, I had this on a prior post and still have some difficulty trying to make it work and the issue might just be very simple. I have a form with multiple tabs on it and each one contains a subform that will update fields in a different table. I have the cycle property on each subform on each tab set to "Current Page" so that there is no more than one record generated per tab and that works fine. The problem I have is if the user will "x" off or closes the form the incomplete record is written to the tables and I only want an update to occur if the user clicks the save button. I coded the on close event in the main and subform properties with the following but it does not stop the update:

Private Sub Form_Close()
Call Me.Undo
End Sub

Any help would be much appreciated.

Thanks Mark.
Declare a Private Variable in your Sub-Form's Declarations. This Variable will only allow the Record to be saved in the Save Command Button:
Expand|Select|Wrap|Line Numbers
  1. Private blnRecordSaved As Boolean
In your Form's BeforeUpdate() Event:
Expand|Select|Wrap|Line Numbers
  1. If blnRecordSaved = False Then Cancel = True
In the Click() Event of the Save Record Button:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSaveRecord_Click()
  2. On Error GoTo Err_cmdSaveRecord_Click
  3.  
  4.   blnRecordSaved = True     'needed to Save Record
  5.   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  6.   blnRecordSaved = False    'Reset
  7.  
  8. Exit_cmdSaveRecord_Click:
  9.     Exit Sub
  10.  
  11. Err_cmdSaveRecord_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_cmdSaveRecord_Click
  14. End Sub
Since the Click() Event of the Save Record Button is the only location where blnRecordSaved = True, it is the only means by which the Record can be saved.
Mar 1 '07 #2

P: 19
Declare a Private Variable in your Sub-Form's Declarations. This Variable will only allow the Record to be saved in the Save Command Button:
Expand|Select|Wrap|Line Numbers
  1. Private blnRecordSaved As Boolean
In your Form's BeforeUpdate() Event:
Expand|Select|Wrap|Line Numbers
  1. If blnRecordSaved = False Then Cancel = True
In the Click() Event of the Save Record Button:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSaveRecord_Click()
  2. On Error GoTo Err_cmdSaveRecord_Click
  3.  
  4.   blnRecordSaved = True     'needed to Save Record
  5.   DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
  6.   blnRecordSaved = False    'Reset
  7.  
  8. Exit_cmdSaveRecord_Click:
  9.     Exit Sub
  10.  
  11. Err_cmdSaveRecord_Click:
  12.     MsgBox Err.Description
  13.     Resume Exit_cmdSaveRecord_Click
  14. End Sub
Since the Click() Event of the Save Record Button is the only location where blnRecordSaved = True, it is the only means by which the Record can be saved.

Many thanks, I will give it a try and let you know the outcome.
Mar 1 '07 #3

NeoPa
Expert Mod 15k+
P: 31,186
Alternatively, if there are easily tested criteria already in your data (controls that must be filled in), you could do that check in your BeforeUpdate event procedure and get away without creating the extra control (the button).
If that's not clear, I'm talking about testing a control (or multiple controls) to see if it's (they're) set before allowing the update to progress.
Mar 1 '07 #4

Post your reply

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