• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 418
  • Last Modified:

Is there a way to prevent Access (2003) from triggering a save (i.e. BeforeUpdate event if the form is dirty) on a main form when the user moves to a subform?

I have code in the BeforeUpdate to ask for confirmation that the record should be saved, however, I don't wish this to happen in the user is only moving to the subform.  The data in the subform cannot be updated..
0
SFBruce
Asked:
SFBruce
  • 5
  • 5
  • 4
  • +1
1 Solution
 
BadotzCommented:
Rather than implicitly updating, make updating the main form controlled by a button click?
0
 
SFBruceAuthor Commented:
Sort of.  Updating of the main form is triggered by a button, closing the form or when the Deactivate event is triggered because the user is going to another form.  In these cases a message is triggered in the BeforeUpdate event.  I dont wish to have the main form updated simply because the user goes into the subform which is lonely displaying related information.  They need to enter the subform to scroll down.
Thanks
0
 
Jeffrey CoachmanCommented:
SFBruce,

Records in Access get added to the table ("Saved") automatically, there is no need for a "Save" button.
(Why would you enter a record and not want it saved?)


    "I dont wish to have the main form updated simply because the user goes into the subform which is lonely displaying related information. "
Why not?
This is the way main/subforms have worked in Access for as long as I can remember.

Finally, can you define "Saved" and "Updated" as they pertain to your system?
They are two different operations, but you seem to be using them interchangeably.

In other words,  ...Can you explain clearly how your forms/subforms are related, and what you are trying to accomplish with this "save/no save" system?

Thanks

JeffCoachman
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SFBruceAuthor Commented:
The subform is only being used to display information related to the record being displayed on the main form.  I dont wish to have the data on the main form saved (i.e., updated, written to the database, whatever) just because the user has gone to the subform to see the related data. Nor, do I wish them to deal with a unsaved data warning message about the main form, which I do wish them to see when they are leaving (i.e., closing the form, moving to another, etc,) the main form record having made changes (intentionally or unintentionally) to the main form record .
I would be happy if I could trap that the user is in the subform before access triggers the BeforeUpdate event on its own.  
0
 
Jeffrey CoachmanCommented:
SFBruce,

1. Thanks, but I am still at a loss as to *WHY* you do not want the data Saved/Updated?
There is no perceptible delay associated with "Updating" the main form.
(Or any other detrimental side effects that I know of, for that matter.)
Why not let Access do what it does normally?
Again, this is the way a Main/subform is designed to work.
;-)

2. The main form will "Always" update when you exit it.
About the only thing you can do is to not let it be changed in the first place.
You can do this by Setting the Locked property of the Main Form Controls to Yes and setting the Locked Property of the same controls to No.
Note: Setting the form Properties (AllowEdits, AllowAdditions, AllowDeletions) to No, will force these settings on the subform as well. So doing so will not give you the effect you are looking for.

3. Lastly, Over-riding Access' default functionality is always tricky.
You may end up writing code to one thing, and discovering that it affects something else.
Then you may discover that the last fix caused an unexpected quirk in the original issue.
...Ad infinitum....
;-)

That all being said...
Here is a sample of locking the controls.
Just test it for the functionality you require.
Is something like this acceptable?

;-)

JeffCoachman

Access-EEQ24330188-UpdateMainFor.mdb
0
 
SFBruceAuthor Commented:
I do appreciate the help here, but am not clear on why I need justify why I wish this form to work this way.
This has to do with: 1) wanting to prevent the saving of unintentional changes made on the main form, and 2) a particular user experience I wish to provide.  For what its worth, I consider myself to be pretty advanced with Access programming, but have not before confronted this particular challenge. Locking the controls, which I already know how to do, isnt appropriate or an option here.  Perhaps Im just out of luck.
Might you have any ideas on how to determine prior to Access firing the BeforeUpdate event on the mainform that the user has clicked into the subform?
Thanks,
0
 
Jeffrey CoachmanCommented:
"but am not clear on why I need justify why I wish this form to work this way. "

You don't.
;-)

But it is always a good idea to state your reasons, in case there is a better approach.
;-)

Again, this is functionality that I have not seen requested in 5 years of answering questions here.
This is why I asked.
;-)

The default behavior is to "Save" the edited record in the main form before "Leaving" it.
And as I have stated, this is may prove tricky to bypass.

There may very well be a straightforward workaround for this, but I have never had the need.

You could very well do what you are asking if you unbound the Main Form and used a recordset technology to manipulate the records.

But to address your question directly:
 "Is there a way to prevent Access (2003) from triggering a save (i.e. BeforeUpdate event if the form is dirty) on a main form when the user moves to a subform?"

It may prove difficult to literally "Prevent" the the Save, becaues:
1. The controls thenselves have a "BeforeUpdate" event as well as the Form itself.
2. There is no explict "Save" event of the form to hook into, or Cancel.
About all you can do is "Try" to cancel the BeforeUpdate event.
But again, you can't "Prevent" it, only cancel it once it occurs.

;-)

Jeff
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
As Jeff said, you cannot prevent the save. Prior to Access 2000, the only way "around" this problem was to use temp tables for the main/subform combination. With a control panel at the top, when the user clicked:
"Edit" - the records would be copied to the temp tables. If they then clicked "Save", you copied the records with the changes back to the main tables. If they clicked "Cancel", you just did nothing.
With Access 2000 and up however, you can now bind a form to a recordset created in code. That means you can wrap the Edit in a transaction, so it's possible to rollback the main form's changes even though it's already been saved when moving to the subform.
I've never tried that myself, prefering the temp table apporach (some times it's hard to teach an old dog new tricks and I'm always leary of new features<g>).
But it is impossible to stop the save.
FWIW,
JimD.
0
 
Jeffrey CoachmanCommented:
Thanks Jim.
;-)
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
SFBruce,
  What is it that you don't feel that's viable?  You were given several solutions to work around the problem.  If you found a solution that was better, then post it and accept your own comment as answer.
  But the fact that you may have not like the answer (you can't change that) is not a reason to delete the question.
JimD.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
I'd like to see him come back and post his solution.  If he's not willing to do so, then a delete with no refund.  Not that that means anything with unlimited points.
JimD.
0
 
SFBruceAuthor Commented:
Ok, this is getting such a bit out hand.  Im new to this site and I deleted the question so that it would go away.  I really didnt feel that any of the solutions offered really answered my question, and didnt feel comfortable accepting them.  If deleting wasnt appropriate, Im sorry.  Im not interested in a refund (whatever that means) just to close the issue.  

My need was pretty specific and Im guessing that what I would ideally like to do may not be possible.  Im sorry, but I didnt find the suggestions at that helpful, because they wouldnt help me achieve what I was trying to accomplish.  

My solution was pretty simple.  Since I know under what circumstances the Before_Update event can be triggered by my form, and that all but the triggering by entering the sub-form can be trapped, I can assume when the event is triggered by entering the sub-form, and can, therefore, cancel it.  Ive not had time to test this theory, but think it should work.
Thanks
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<Ok, this is getting such a bit out hand.>>
 Please understand that I'm not trying to create a problem here, but you asked a question and efforts on everyone's part (including yours) has gone into this and there is valuable information here.  I don't think it should be just tossed.
 Going forward, when your participating in a question, as an asker or as an expert, it's important to continue a dialog until all agree that there is no point in continuing.  Then and only then if nothing has really been accomplished should a question be deleted.  The only other time a question should be deleted is if experts stop responding to you and you don't have a solution.
  But if you didn't feel that anything said so far had helped, you should have come back and said so with a comment rather then simply asking for a delete.  
    If you had come up with some method to get around this, then that would be great and I would love to hear it.  I write apps for a living and I'm always open to new ideas.  I often learn not only from other experts but sometimes the asker as well.
 As far as the solution that you suggested, it will not work because if you cancel the main form's BeforeUpdate event, the move to the subform control will be canceled as well and you'll stay on the main form.  
 If a change has been made on the main form and you move to the subform, there is no way to avoid the save of the main record.  That's simply the way bound forms work in Access and you cannot change that.  Your only choices are:
1. Use an unbound main form and do all the CRUD (Create, Read, Update, and Delete) operations yourself.
2. You can use temp tables as I suggested.
3. You can open the main forms recordset in code and use a transaction, which I suggested.
4. You can undo the main form changes before moving to the sub form (which would not make sense to a user as their changes would just dis-appear).
 Had you asked for more info, either Jeff or I could have pointed you to MSKB aritcles that would veirfy what we said and outlined the techniques that were suggested, but neithe rof us were ready to give up on this.
JimD.
 
 
 
 
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
modus_operandi,
  Please tag Jeff's comment 24179866  as the answer unless SF Bruce wishes to continue.
JimD.
0
 
Jeffrey CoachmanCommented:
To all.
Let me first start off with a smile.
:-D
I wil address each of you, (Including any Admins) specifically in this post.

SFBruce,
I am with Jim, nobody has an issue with anything here, and no one is upset.
The thing is that if none of the solutions worked, then as a courtesy, state just that, and explain why.
;-)

The goal of this site is to get your question anwered.
Deleting the question should only be a last resort if no solution works.

This appears to be the case in your situation.

<I can assume when the event is triggered by entering the sub-form, and can, therefore, cancel it.>
The "Enter" event of the subform cannot be cancelled either, but perhaps you can find a workaround.
Keep us posted.

Jim,
Thanks,
I would like to think, that I share your tenacity in trying to solve a problem.
So you are correct, I was willing to spend as much time on this as was needed.
;-)
But also like you, my feeling is that if my post does not help, then I have no problem with a delete with explanation.
Even if the asker is really uncomfortable with accpeting a: "It can't be done" post, I won't force the issue.
;-)

Admins,
I have no issue leaving the question open until the asker informs us of their findings from post: 24227609.

;-)

Jeff
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

  • 5
  • 5
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now