Solved acSaveNo has no effect

[HarryNeil]

I have a couple of forms based on the same item of the same table. When I close these forms I choose acSaveNo, since otherwise there'd be duplicate saves. But there ARE duplicate saves anyway. even producing multiple records. How can I FORCE form closure without saving the data record?

 

[The_Doc_Man]

First, you shouldn't EVER have to save something explicitly from a bound form, because the form itself does the save totally automatically. If you have a form and don't want to save its contents, you issue an Undo (see link below) to make the form "not dirty." A "not dirty" form has nothing to save and Access knows that, so it doesn't try to save anything. Closing a "dirty" form, on the other hand, WILL save form contents.

Form.Undo method (Access)

Office VBA reference topic

learn.microsoft.com

For you to be using acSaveNo, you must be doing a DoCmd.Close or something like that to close the form. Normally, you would use that option when trying to save a design change or structural change to the object being closed. Like, for example, if you moved controls around on a form - which is legal even in FormView mode - and wanted to save the new arrangement thereof.

In other Office utilities, acSaveNo would prevent you from changing a Word document that had been programatically "diddled" when you didn't want to save those changes. But in Access, that's not what acSaveNo means.

 

[GPGeorge]

I think The_Doc_Man probably addressed your problem, but being persnickety, I feel the need to be more explicit.

acSaveNo, used with the DoCmd.Close command, means "Save any changes made to this object (form or report itself), not to any data currently in the form".

Apparently, you are making and saving Data changes while the form is open. Those are not impacted either way in the DoCmd.Close ,, acSaveNo command.

 

[The_Doc_Man]

@GPGeorge - being persnickety is perfectly fine. I can't fault you for having one of my own personal failings.

 

[HarryNeil]

Thank you both, The_Doc_Man and GP_George! Your comments are VERY illuminating, mostly reminding me that I have no business dealing with ACCESS here in my dotage. (I turn 80 on Thursday.)
Yes, I'm using DoCmd.Close, because I haven't found another way to make a form go away when its job is done. Perhaps using a CLOSE button and a macro is the right way?
But if that's the case, how do I handle this situation? The user has done something that runs a procedure that does various things to the data source and then closes the form. I DON"T want to return control to the user and give him the opportunity to do something ELSE before closing the form.
Or I just want to write something to my log and then close the form. What do I call after doing the logging?
Thanks for putting up with my newfound confusion. I think I used to be good at this...

 

[The_Doc_Man]

Your last paragraph is a bit jumbled, but I'll do what I can to illuminate at least a bit of the darkness.

First, if a procedure DIRECTLY alters the data source (rather than altering a control on the form), that change is committed and done, no "Undo" is available. The normal automation of the form has nothing to do with ANYTHING that makes programmatic changes to the underlying data and - using code - saves the changes. If manual changes were ALSO made to the form's controls, the form has become "dirty" and a close with no other action would have the effect of saving the data again - potentially overwriting the programmed changes.

Second, the normal data and logistics flow of a form is that IF you are going to navigate to another record and IF the form was dirty with respect to the record before navigation, a data save will also occur before the navigation occurs.

Third, normally, if you close a form, I don't believe your user would have time to do anything else before it actually closes. So I wouldn't worry too much about users getting into a closing form. This is usually a very fast process - milliseconds fast or faster depending on what you use for a hard drive or system volume.

Fourth, what you log is your business, but WHEN you log something depends on what you are doing, and HOW you log also depends on what resources you have opened at the time. Your description maybe confuse the issue slightly but USUALLY if you are going to log something that was already done, the logging code could run in the AfterUpdate event.

I think a little bit more of an explanation is in order if we are going to give you best advice on how to proceed.

 

[HarryNeil]

The_Doc_Man: Thank you for even trying to deal with my jumbled comments. All the information you provided will be useful for me.
HOWEVER, comparing this "beta" app with a "production" version that works just fine, the evidence is that I am seeing a seriously contaminated database. For instance, if I try to import its components into a fresh database, only a few of them show up at all. The standard "repair" menu item doesn't help.
SO, the practical thing is to not pursue this thread at all until I can solve THAT problem. The others (and there are lots of others) may then solve themselves. I hesitate to mark this "solved," but that may be the only option. If you say so, that's what I'll do.

 

[Pat Hartman]

Was the original database created with a newer version of Access? There may be objects that the Access version you are currently using doesn't recognize.

And to help a little more with bound forms - Access takes it as a personal mission to never lose data so it ALWAYS saves data if the form is dirty when it closes - Access also saves data at other times and if you understand a little about how bound forms work, you will come to grips with this easily enough. The problem most people have is not with making Access save a dirty record, it is with stopping Access from saving bad data. I created two videos and also included the sample database I used in the videos that explains the right way to ensure that only good data gets saved if that is what you are trying to do.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

 

[The_Doc_Man]

I hesitate to mark this "solved," but that may be the only option. If you say so, that's what I'll do.

Not at all suggesting it is solved. Just not sure what advice to give on the problem. But perhaps some advice on the approach?

When you are perplexed about how/when/why something happens, it is time to get out paper and pencil and get back to the drawing board. What I am about to suggest works big-scale or small-scale. (Side query: Where did folks go before drawing boards were invented? But I digress...)

Ask yourself several questions, some of which can be helped by Access tools on the ribbon. The database tools include a documenter that can print out details of your tables' fields so you know those resources. The documenter can also show you queries and extant relationships. And you have to decide what is the goal you want to achieve. Then think about how you would REACH that goal with those resources. This is where the pencil and paper come in handy. Once you have done that, you can compare what you thought about to what you see in the DB.

You will probably see differences. Maybe you forgot a regulation. Maybe you forgot a tax amount. Maybe you forgot your wife's birthday. (This method won't help you with EVERYTHING, you know.) But the point is that you are organizing your thoughts and improving your understanding of the code so that when you DO see what it is doing, you might have greater clarity. You might be better able to answer the "Now why did it do THAT?" question.

"Shooting from the hip" (or overly hasty transition from design to coding, for our foreign members who don't understand USA idioms) OFTEN misses your target completely, and is often indicative of incomplete understanding of your goal. A mental ability that not everyone has, but one that CAN be nurtured, and is a truly important ability in designing computer apps, is the ability to alternate your focus between the forest and the trees - to see the way the parts work individually and to see the way the parts fit together to make the overall project work. The more formal way to say that is - the ability to choose between reductionist and holistic viewpoints.

 

[HarryNeil]

THANKS TO YOU ALL! All your information and suggestions are applicable and valuable. I need to stop and do some ground-up analysis and redesign. This started as such a simple app that it didn't need design or careful programming, but it grew like topsy, and now it's out of control. I need to remodel it from the ground up, knowing all the things I've relearned from experience and from your great responses.
This time I'll name all my controls carefully and lay out information flow from form to form. I think with proper practices (and I did once know those) I'll be able to make it all work.
Incidentally, I did import all my objects into a new blank database, and this time they all came over. I only lost some font sizes, I think. I can't explain what changed, but again, I'll understand it all better with a more careful design.
SO THANK YOU ALL! AND LET'S MARK THIS SOLVED. When I'm all back up and running, I may need you again, but let's not waste your time. THANK YOU.

 

[Pat Hartman]

Don't forget to make frequent backups. I just close the app and zip it with a date and a suffix if I back up more than once per day. I also use more sophisticated code but this is the simplest way without code to just make backups. Compact after you do the backup before you start working again to keep the size under control Then I move to a folder labeled OLD

 

[Kamayo Ako]

I have a couple of forms based on the same item of the same table. When I close these forms I choose acSaveNo, since otherwise there'd be duplicate saves. But there ARE duplicate saves anyway. even producing multiple records. How can I FORCE form closure without saving the data record?

If found this from Mr. Alessandro Grimaldi answer with someone asking same concern of yours sir.
Maybe this is not specific solutions but this helps me to avoid saving record without clicking save button and closing form without saving entries.

Private isOK As Boolean

Private Sub cmdSave_Click()
isOK=True
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not isOK Then Me. Undo
isOK=False

End Sub

 

[Pat Hartman]

That is pretty Draconian code. I would not use the code as presented. If your user forgets to press the save button and just closes the form, you are undoing all his changes. That is just wrong. You have to at least ask to give the user a chance to correct his error.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim Reply As Long
If Not isOK Then
    Reply = Msgbox("Press Yes to save data chengs.  Press No to discard them.  Press Cancel to return to editing.", vbYesNoCancel)
    Select Case Reply
        Case vbNo
            Me. Undo
            Cancel = True
            Exit Sub
        Case vbCancel
            Cancel = True
            Exit Sub
    End Select

'''''' Put your validation code here  - that is FAR more important than insisting that the user press a button.


isOK=False

End Sub