Access VBA. Buttons to save data in form with embedded form

Hi

I am using the following code to stop data entered in an Access form being saved to a table
unless the user clicks a 'OK" button. I now want to use this in a form that has a sub form embedded
in it. There are two joined tables "Invoices" and "Invoice Detail". I created the form using the Access wizard
so can't even see it in the list of tables and forms on the left. How do I go about this?

 Private blnSaved As Boolean

Private Sub btnCancel_Click()
    DoCmd.Close
    MsgBox "Changes discarded!"
End Sub

Private Sub btnOK_Click()
    blnSaved = True
    MsgBox "Changes saved!"
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

    If blnSaved = False Then
        Cancel = True
    End If

End Sub

Private Sub Form_Current()
    blnSaved = False
End Sub

Open in new window

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Anytime you move from the main form to the subform, Access will save the data, so the only way to do this would be to perform your checks on the parent data prior to moving into that subform. You could then do the same thing on your subform data, using the same events.

The only way to do it "all at once" - i.e. check the Parent data and Child data at the same time, before saving ANYTHING to the database - is to use temporary tables. The Temporary Table solution is one where you, basically, create temporary tables that are exact duplicates of the schema of your tables, and you fill those tables with the data your form will be working with, and base your form on those temporary tables. when the user clicks the Save button, you write the data from the Temporary tables back to the Live tables.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Murray,

  This one area where Access falls flat on its face.

  The problem is that when entering a subform control, Access will automatically save the main form record, and there is nothing you can do to stop it.    Of course this flies in the face of what users expect; when they click "save", the whole invoice is saved (header and detail) or discarded.

There are a two basic ways to handle this:

1. Let the header save, and if the user cancels at a later time, delete it.
2. Use temp tables for both header and detail.   when the user starts an edit, copy the records to the temp table.   Then if they decide to "cancel", you do nothing.   If they save, you delete the original records and copy from the temp table to the original tables.

 a third is to use transactions, but that doesn't work well.

 Jim.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
Another way to do this is to disable the subform until the user saves the record on the main form.  Then, in your cmd_Save_Click event you check to make sure fields required in the main form are filled in.  If not, you display a message and return to the main form.  If so, you save the record on the main form and enable the subform.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Dale FyeOwner, Developing Solutions LLCCommented:
Personally, I wish the Access team would create a Cancel argument  on the subform Enter event, like they have on the subform Exit event.
0
ste5anSenior DeveloperCommented:
@Dale, this would mean a completely different paradigm.

Access works on the save data when you can and are not otherwise instructed to. So using a Cancel button to abort editing and discard changes is okay. So let Access save when it can. But when a user want to abort this he must explicitly cancel.

Private Sub btnAbbrechen_Click()

  If Me.Dirty Then
    Me.Undo
  End If

  FormClose Me.Name
  
End Sub

Open in new window


Requiring an explicit Save button is imho problematic, cause then you will have some forms requiring an explicit Save click, but others will save their data when you press [x]. Same for datagrid views of tables or views.

When their is a use-case, which requires explicit Save or Abort, then you need imho a unbound form. Or really fool proof code behind (aka. error handling).
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Personally, I wish the Access team would create a Cancel argument  on the subform Enter event, like they have on the subform Exit event.>>

   Actually, I wish they would do what was done in the VFP data environment; allow you to control the buffering mode.

   With VFP, you just tell it to hold all updates until you issue an explicit save (an automatic transaction).   Working with main/subforms in VFP was drop dead easy.

  This is one area where Access has always been lacking.  

Jim.
0
Dale FyeOwner, Developing Solutions LLCCommented:
@ste5an,

"Requiring an explicit Save button is imho problematic, cause then you will have some forms requiring an explicit Save click, but others will save their data when you press [x]. Same for datagrid views of tables or views."

I have learned, over the past 24 years, that most of my clients prefer explicit Save and Cancel buttons, on all forms, so that they know when they are saving a record.  Since almost all of my forms contain code in the Form_BeforeUpdate event to ensure required fields have been filled in, it is easy to prevent the user clicking on the (x) as a way to close and save the data in the form.
0
ste5anSenior DeveloperCommented:
Yup, me too. And where it was an absolute requirement, the only way are unbound forms. Cause I have seen code corruption, where the code wasn't anymore executed on pressing [x], thus data got saved instead of discarded with bound forms...

Just my 2¢.
0
PatHartmanCommented:
And where it was an absolute requirement, the only way are unbound forms. Cause I have seen code corruption, where the code wasn't anymore executed on pressing [x], thus data got saved instead of discarded with bound forms...
The form's BeforeUpdate event is your friend.  It is the ONLY event where you can truly ensure that your validation code will cancel saving a record.  When you put your validation code in random "lost focus" or other events, you get what you get.

Murray, the questions are:
1. Is saving the main form independent from saving the subform?  I.e. can the parent record exist logically if it doesn't have child records?
2. Are you trying to control the save of ALL subform records so that either all or none are saved?

To solve the all or nothing, you have two reasonable options, you can add a CompleteFlg to the main form's table.  Throughout your app, every process, except this maintenance form, would only select records where CompleteFlg = True.  The alternative was suggested by Scott where this form is bound to shadow tables.  The data is created in the shadow tables and when the user is happy, he pushes the post button.  Your code then does whatever validation is necessary and if the transaction is valid, copies the data from both tables and then deletes the shadow data. This move process must be performed  inside a transaction because this is an all or nothing operation.
The third option which I consider unreasonable due to the extra work involved is to use unbound forms.  Have fun with that with a continuous subform.
0
ste5anSenior DeveloperCommented:
I know, but as I said, I have seen corrupted databases where the code wasn't executed and the data was saved. Thus bound forms are not 100% reliable, when the requirement is "only the save button is allowed to save data".
0
PatHartmanCommented:
The possibility of potential corruption isn't a reason for using unbound forms. Any object in the database can corrupt.  Are you going to avoid all of them?   All you need to have complete control over saving is an understanding of what event to use.
0
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
Thank you all very much. I abandoned the idea of trying to control Access with an OK and Cancel button because of your advice and because of all the errors that cropped up
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.