Save button in ms access

You use an Unbound form...so if you don't implement a save strategy on first record change everything is gone .

So usual scenario...

You start by designing your form as usual...(bound)..you have everything in place.

So everything is working as it should but on the  final step..you delete the Recordset...

Your form is now Unbound but it has the correct design

Now either you implement a button or you can work with events (for warning the user that forgotten to save(

The user clicks the button..you create a Recordset and rst.AddNew.. . iterate all the controls..map values to fields.. rst.Update.

For a master/sub form combination, the rub is that the main form record is automatically saved when you enter the subform control and you can't stop it.

So if you are going to stay with a bound form, then in some way, shape, or form, you need to cache a copy of the main form record before you allow the user to edit it.   Typically that takes the form of copying all the records to temp table(s), and if the user cancels, you don't update the original record(s).   If they save, then you do update.

 That's one way to handle it.

If it was just a form, then the answer is just providing your own buttons and making sure the user can't move off the record (i.e. with page up/down, Ctrl/S, navigation buttons, etc).

Jim.

I would add that you usually cache the subform records as well, which allows the user to cancel and roll back all changes.

 That might not have been clear.

Jim.

No points please.  

I do it the way Jim mentioned, with temp tables on the local machine.

If I'm editing a record, I delete all the records from my two temp tables (main and sub), then populate them with existing data from the back-end (BE).  Then, when the user hits Save, I write the data back to the BE.  This gives all of the benefits of bound forms with the added benefit of being able to cancel the changes made to either the main form or subforms.

And this way, I can warn users when they attempt to close the form without hitting either Save or Cancel.  I actually prevent them from using the Red X by another technique, but that's for another post.

Dale

The issue I find with working with temporal tables is that, let's say I have a consecutive Invoice Number.   Let's say I have 5 different users generating Invoices at the same time.    It will create a conflict of numbering the Invoice Numbers.   Also, the user cannot easily see, let's say, previous invoice with a single previous button command on the form.

Any other alternative?

Any other alternative?                 

You can use different methods to create your Invoice Numbers, like using a table to provide those Invoice Numbers, so when UserA adds a new record you'd grab the next available number from your table, and then increment that table immediately so that UserB gets the "next" Invoice number. If UserA then decides to not save their Invoice, the Invoice number assigned to UserA would not be used, but generally that's not a bad thing. Most invoicing systems allow that. You could also use a push-pop stack, but that's generally more than you really need to do.

If you want full control over your process, the only way to really do that is move to unbound forms (i.e. the form has no Recordsource, and all controls on the form have no ControlSource). This takes away a LOT of the reason to use Access, however, and you have to handle all data manipulation behind the scenes.

And to be clear: A "temporal" table is not the same as a temporary table. A temporal table is a SQL Server object. We're referring to a temporary tables, which are simply tables that are structured exactly like your live tables, but that "temporarily" hold your data.

You could have a kind of dual personality form
1 Form that is bound normally with AllowAdditions/AllowEdits/AllowDeletions.....disabled...so its kind of read only
1 Form Unbound only for Inserting of new Records..
The above scenario can be in the same form...
Another scenario based on the above...you can work with the Properties AllowAdditions/AllowEdits/AllowDeletions and have the form as i said kind of read only...when a new record is needed then you can have a toggle button to toggle Normal/Restricted mode.

<< Let's say I have 5 different users generating Invoices at the same time.    It will create a conflict of numbering the Invoice Numbers. >>

 You can assign the invoice number when the user goes to save.  Field does not need to be an Autonumber.   You can assign a value yourself.

<<  Also, the user cannot easily see, let's say, previous invoice with a single previous button command on the form.>>

 Include "Entered By" and Entered On" in your invoice table.   You can then pull invoices done by a specific user.

Jim.

Also, the user cannot easily see, let's say, previous invoice with a single previous button command on the form.

Which is one of the reasons you should rethink the method you're suggesting. Generally speaking, it's best to work within the confines of Access and not try to "go against the grain", so to speak. If you have a typical data entry form, your user can easily move back and forth in the recordset.

If you want to have an explict "Insert New Record" form, you can do as John suggests and have two forms, one for data review and a second for data entry.

Start by clarifying your objective.  Why do you feel the need to deviate from standard procedure in  a relational database which is to save the parent record and then save the child records, one at a time?  You can do whatever you want but when you deviate, you are essentially on your own and YOU will be coding all the stuff that Access would have done for you had you been willing to do things the Access way.

ok.   I what about if I want to save the information just on the Child form?   Let's say having the Save button on the Head form, but the Head form will be saved by default.  The Saved button will be only for controlling (save) the information on the Child form.   What code can I use to save from the Head form the Information entered in the Child form?

Well, as soon as you click on a button, any button, or click into any control on the main form, the data in the child form will be saved, which is why Scott and I recommended working with temp tables as the tables that are bound to your forms.  This way, you can control what actually gets saved back to the server.  It's really not that difficult, far easier than dealing with unbound forms.

The same caveats apply when moving from the Child form to the Parent (or "Head") form: As soon as you move off that Child form, Access will save your edits in the Child form (and if you have more than one record in the child form, Access will save each record as you move off that record).

You could, of course, just Delete the records if the user elects not to save. You can run an SQL statement to do that:

Currentdb.Execute "DELETE FROM YourChildTable WHERE YourParentIDField=" & Me.YourParentIDField

Then refresh your subform:

Me.YourSubformControl.Form.Refresh

Of course, you'll have a tough time determining if the user made Edits to the Child records, or added New Child Records. You can introduce logic to store this information, but if the user has made Edits and wants to revert those edits, you'll have to enable some sort of logging to roll back those changes. That can be tough to do (and Form.Undo won't work, since the changes will have already been applied when the user moves off the subform).

In the end, if you want to manage user saves, the only safe ways to do it are to (a) use temporary tables or (b) use unbound forms. Both of those methods have challenges, and both are contrary to the way Access is intended to work.

What code can I use to save from the Head form the Information entered in the Child form?

As the others have all pointed out, this is not possible without using an unbound form and when you use an unbound form, you are on your own.  YOU will be writing all the code that Access generates for bound forms.

You asked a technical question and got an answer.  You might not like it but the answer is definitive.   We still don't have any idea what your objective is.  Perhaps you just didn't understand how Access worked and you were afraid some data wouldn't get saved. Without knowing what problem you are trying to solve, it is hard to give you advice but.nothing you've said makes me think that unbound forms and temp tables are needed.

Perhaps you don't understand how to do validation and so some records are getting saved that shouldn't.  That's an easy one - use the form or subform's BeforeUpdate event to validate a record.  This event can be cancelled so you can prevent a bad or incomplete record from being saved.  The BeforeUpdate event is probably the most important event of a bound form.  The FORM's BeforeUpdate event is always the LAST event fired before a record is saved.  Think of it as the flapper at the end of a funnel.  You are in control of the flapper.  Cancel the event and the flapper remains firmly closed preventing the record from being saved.  Don't cancel the event and the record flows through the funnel to the table.

Hi.mr. John Tsioumpris.....how do I delete the Recorset you mentioned...?

Ok.....now...lets say I am working with unbound forms....when it is a new record the user can add it by clicking on a Save button.    But what about the scenario when the user is editing existing records?  How to promt on the changes to save?

Tony,

You are going down a rabbit hole.  Access is a Rapid Application Development (RAD) tool.  If you are not going to use the RAD features of Access, you should probably not even be using Access.  You have never told us what problem you are trying to solve.  "Stopping Access from saving records" isn't a problem.  That is what Access does.  What business problem does this cause?  

If you want to edit the record just load the form with the data...still Unbound...just iterate the controls and set their value...the user makes the changes and if pushes the button you perform an .Edit instead of .AddNew

when it is a new record the user can add it by clicking on a Save button

Understand that YOU must add the record when the user clicks that button. If you work in unbound mode, Access niceties like automatic save, delete, etc don't work any more. You have to save the data, or update it, or delete it.

As John mentioned you can just pull the data from the database, load it into your form, and then take action when the user modifies it.

If you want to create new records, you would just clear the form controls and let the user enter data, and then add that data to the database. You could do that with a Recordset or straight SQL.

The "right" way to work in unbound mode is to create classes that model your objects (like a Customer, or Invoice) and then create methods in that class that save your data. But that's an entirely different discussion ...