Solved Stop automatic save

[mloucel]

Hey guys

I have a form that contains 1 sub-form
[sub-form adds just some notes I add to each order using a separate table that contains 3 fields Key/Date/Note]
the key as you can figure is my FK on my main table.

Problem is that everytime I click on the sub-form to add note or notes, all the values on the main form are saved, auto magically.

is there a way to stop this behavior and force the end user to use my SAVE button, and only at that time save ALL data to both tables ?

Thanks.

 

[CJ_London]

Investigate using begintrans/committrans. Plenty of examples if you google it - here is an example

Transaction in Access

I need to execute several sql clauses, inserts, updates and delete for example. How to use a transaction in Access to bind them together? DAO is preferred. like: BeginTrans Excute SQL_1 Excute ...

stackoverflow.com

note this requires the use of recordsets so you will have much work to do. You will probably want to use temporary tables as your forms record sources. Or might work with ado disconnected recordsets

 

[mloucel]

Investigate using begintrans/committrans. Plenty of examples if you google it - here is an example

Transaction in Access

I need to execute several sql clauses, inserts, updates and delete for example. How to use a transaction in Access to bind them together? DAO is preferred. like: BeginTrans Excute SQL_1 Excute ...

stackoverflow.com

note this requires the use of recordsets so you will have much work to do. You will probably want to use temporary tables as your forms record sources. Or might work with ado disconnected recordsets

thanks for the tip, honestly I am lost is probably above my pay grade and the knowledge I have, I was hoping for a most simplistic solution.

 

[MajP]

You can come up with all kinds of convoluted solutions. Unbound forms or transactions and likely beyond your skillset. And 99 times out of 100 when people ask to do this, it is for no good reason. Your case looks the same.
For what good reason would you not save the main record until a note is saved? Are you suggesting a record without a note cannot exist. If that is the case then it is likely there is a table design problem. Can you explain the reason not to save the main record until a child record saves?

 

[mloucel]

You can come up with all kinds of convoluted solutions. Unbound forms or transactions and likely beyond your skillset. And 99 times out of 100 when people ask to do this, it is for no good reason. Your case looks the same.
For what good reason would you not save the main record until a note is saved? Are you suggesting a record without a note cannot exist. If that is the case then it is likely there is a table design problem. Can you explain the reason not to save the main record until a child record saves?

100% Absolutely CORRECT, but you do not know my End Users, I've seen things that will scare the last hair in your head.

But I found a solution and it works pretty good, with some minor changes, good old Richard Rost provide me another fresh idea:

How To Stop Auto Record

 

[CJ_London]

Looks like that is for a single form, not a form/subform so don’t see how it meets your requirement of

Problem is that everytime I click on the sub-form to add note or notes

so suspect user will instead have a very irritating message when they click in the subform

 

[Pat Hartman]

The best solution (If you aren't going to go the unbound form route) is to actually validate the data in your form using the form's BeforeUpdate event. The BeforeUpdate event is the last event that run before the record is saved and you can stop the save if you find a problem but simply using "Cancel = True" Let me know if you need more details. I've written extensively about the BeforeUpdate event and even created videos and a useful database to explain how form events work.

 

[MajP]

But I found a solution and it works pretty good, with some minor changes, good old Richard Rost provide me another fresh idea:

That is the approach is what @Pat Hartman is referring to but as @CJ_London said that would not allow you to enter main form information and sub form information without saving the main form record. All that is going to do is allow you to cancel the main form. If that is sufficient it is good, but very different from what was stated. That is trivial but what was stated is not.

and only at that time save ALL data to both tables ?

If you really want to save the information from the main and child at once, or cancel all of it, it has to be an unbound solution or a transaction.
With a transaction approach you could enter several notes and then decide you do not want any of the notes or the main record saved. It is so rare to really need this. This question gets asked a lot and I have never seen an example where this was needed or of any real benefit. I always wonder who is the person who is entering a bunch of notes and then decides they want to cancel the whole thing.

Maybe, I could see something like taking samples for an experiment and after logging 5 samples you realize all the data is bad making an invalid experiment. So you could cancel the whole thing. You could do this with a bunch of code or simply delete.

If you want to see an example where you can change multiple records and then cancel

Using Transaction on single table

the db can also be found here: Generic Form with "Save", "Cancel" buttons | Page 2 | Access World Forums (access-programmers.co.uk) (post #34). you can delete, edit as many records you want. to revert what you have done just press the Cancel button. to permanently save your work, press the Save...

www.access-programmers.co.uk

 

[Pat Hartman]

MajP had it right the first time. Your request is almost always not necessary. Please think about WHY you can't save the parent record until AFTER the notes are saved and we'll help you to solve the problem using a complicated unbound form. Once you understand that saving the parent record first is almost certainly fine, then we'll help you with code to ensure that the parent record is complete before control moves to the subform where you can work on the notes.

It is possible that your design needs to be flipped and the note needs to be the parent record.

 

[mloucel]

MajP had it right the first time. Your request is almost always not necessary. Please think about WHY you can't save the parent record until AFTER the notes are saved and we'll help you to solve the problem using a complicated unbound form. Once you understand that saving the parent record first is almost certainly fine, then we'll help you with code to ensure that the parent record is complete before control moves to the subform where you can work on the notes.

It is possible that your design needs to be flipped and the note needs to be the parent record.

I'll work on yours and @Pat Hartman ideas, I do understand the prompt will be annoying, I just wish I knew 1/3 of 1/3 of what you guys know.
I'll work on that on monday in the office.

Thank you all.

 

[mloucel]

That is the approach is what @Pat Hartman is referring to but as @CJ_London said that would not allow you to enter main form information and sub form information without saving the main form record. All that is going to do is allow you to cancel the main form. If that is sufficient it is good, but very different from what was stated. That is trivial but what was stated is not.

If you really want to save the information from the main and child at once, or cancel all of it, it has to be an unbound solution or a transaction.
With a transaction approach you could enter several notes and then decide you do not want any of the notes or the main record saved. It is so rare to really need this. This question gets asked a lot and I have never seen an example where this was needed or of any real benefit. I always wonder who is the person who is entering a bunch of notes and then decides they want to cancel the whole thing.

Maybe, I could see something like taking samples for an experiment and after logging 5 samples you realize all the data is bad making an invalid experiment. So you could cancel the whole thing. You could do this with a bunch of code or simply delete.

If you want to see an example where you can change multiple records and then cancel

Using Transaction on single table

the db can also be found here: Generic Form with "Save", "Cancel" buttons | Page 2 | Access World Forums (access-programmers.co.uk) (post #34). you can delete, edit as many records you want. to revert what you have done just press the Cancel button. to permanently save your work, press the Save...

www.access-programmers.co.uk

I'll check the example, thanks.

 

[Pat Hartman]

You are very welcome. We all have to start somewhere and you're doing great

 

[mloucel]

You are very welcome. We all have to start somewhere and you're doing great

Thanks for your understanding, Pat, sometimes we forgot that we start somewhere, and we all have stupid questions, I am not afraid to ask them, stupid questions got Edison to invent the light bulb, stupid questions got him start the telegraph, so I understand when someone gets a bit disturb with me not explaining my issues correctly or not understanding the solution correctly, I do understand that I am talking to the gurus and I am just an underdog, but hey, little by little I will get my way.
Thanks again for your patience with my questions, it is really appreciated.

Maurice.
and by the way, after a long discussion with my boss, we decided to stop the annoying message and let the user save the record as is, one way or another if they make a mistake they can go back and fix the error(s), She's happy and I am happy as well, I have to polish the form find the bugs and go to the next task to finish the application.

Thanks a million.

 

[Pat Hartman]

we decided to stop the annoying message and let the user save the record as is,

That isn't good either. ALWAYS asking first is very annoying and doesn't do anything except train the users to ignore your prompts, but validation is not annoying. Most users can manage to complete a form without error as long as they understand the rules but you really do need to validate to prevent saving records with missing or invalid information. A silly example --- 6/17/204 is a valid date according to Access but probably not rational. It is clearly a typo. These are the kinds of things your validation protects against as well as ensuring that required fields are entered. Things like SSN are required to save an employee record but you should never allow duplicates so that is two validation rules. It must be entered and it must be unique.

I always check dates for sanity, even if I check nothing else.