Help with Save Prompts in Main and Subforms

[aeroach]

Hello,

I have a main form with a subform and am trying to figure out the best way to have users save data that is entered.
I know that access automatically saves data entered but I would like to avoid that for instances in which a user enters partial information into a record, and either doesn't finish or makes a mistake and closes out, thinking that that won't save the data.
I would like a prompt that occurs on close of the form that will save the information that's been entered in both the main and subforms.


I have this code in the main form's on close event:

   
On Error GoTo Close_Error
  
   If Me.Dirty Then
      If MsgBox("The record has changed - do you want to save it?", _
      vbYesNo + vbQuestion, "Save Changes") = vbNo Then
         Me.Undo
      End If
   End If

Close_Exit:
   Exit Sub

Close_Error:
   MsgBox Err.Description
   Resume Close_Exit

And similar code in the subform's before update event.
What's happening now is that even if the user enters partial information only in the main form, the prompt does not occur and the information is saved anyways.

Could someone please offer any advice on how to clean up these saving issues? I'm no programmer, so any help would be greatly appreciated.

[Seth Schrock]

Look at the following link: How to turn off autosave. This is what I use.

[zmbd]

Here's the issue,
Once the user enter's the data in the main-form records and then goes to the subform, that's normally it, the parent record is saved. Consider the logic if it wasnt', then the subform would have no access to the parent record.

However, there is a concept called "transactio ns" and since ACC2000 we have had the option to wrap the form in a transaction status; thus, creating an all or nothing envirnment.

The basics are explained here:
ACC2000: How to Control Bound Form Transactions in Access Databases

Please read thru this article first. Once you understand the underlying concepts we can proceed to provide some more help.

-z

[zmbd]

opps... the article is for ACC2000 if you are using ACC2003 or newer the DAO reference is set by default, you should not need to alter anything in the references.

[Seth Schrock]

I missed the part about the subform. Thats the trouble with jumping to conclusions too fast.

Anyway, you might look into transactions. This allows you to roll back any changes made if you want to. I have messed with it a little bit, but I don't remember much. Here is the link that I had bookmarked for it though: How to Control Bound Form Transactions in Access Databases

Just realized that it was the same link as Z posted. So I'll just add my support to his suggestion and confirm that it does work.

[aeroach]

Thank you for the responses. For reference, I am using Access version 2007-2010.
zmbd, I realize that if move on to the subform it's logical to have the main form information saved... but I want to prevent main form data from being saved if users only enter partial or erroneous information in the main form and don't put anything at all in the subform.
Seth, will transactions work with subforms as well? I'm looking at the link you provided, but don't see anything about subforms.

I'm also wondering if the inclusion of some sort of validation code for the fields in the main form will prevent the main form from being saved if all fields aren't complete?

[zmbd]

The subform makes it more difficult.
I should have asked about the need to add new records to the parent table....

I've done this with P-C before; however, I've not been able to get around the "new record" in the parent.
The Transaction takes a picture of the datatbase (simple here) at the start... so if we add a record to the parent table, I've not been able to have that change recognized in the child.....

Now with current parent records and child records, that's a bit different. One can edit the current parent and then the child... even add records to the child... and the rollback/commit appear to work.

Not sure about useing a nested transaction.

[aeroach]

Okay I think I understand how the transactions work... so it sounds like it may be difficult (for me at least..) to accomplish the transactions with subforms...
Here's another idea I was thinking of... if there's code to prevent both the main and sub forms from saving on dirty and then have a prompt to save both on close?

[Seth Schrock]

You can use the code in the link that I posted in post 2 in both the main and sub forms. The only issue would be that if you make a change in the main form and try to move to the subform, the message will appear. There is no way to make Access see everything as one.

Transactions, however, allow you to commit all the changes (both main and sub form) or roll them all back as one.

Another option would be to hide the subform when you are adding a record. Then the user has to press a Save button which would make sure your required fields were all completed before making the subform visible.

[NeoPa]

There is a Form_BeforeUpda te(Cancel As Integer) event property that you can code and this will allow you to halt further progress of the operator should you exit the function with Cancel set to True. This means that even if they try to click on another area of the screen (such as a subform for instance) then this action will fail and focus will remain with the data that failed to save. Otherwise, any time you move away from the area of a particular record on a form, the data will be saved for you automatically. This is how Access data forms work.

NB. Although it is also possible to use the Macro command :

Call DoCmd.RunCommand acCmdUndo()

It is more direct and logical to handle this by setting Cancel to True. It also ensures that the only process it can refer to is the one that the code is related to - to wit the Form Update.