• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 84
  • Last Modified:

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

0
Murray Brown
Asked:
Murray Brown
  • 3
  • 3
  • 2
  • +3
6 Solutions
 
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
 
Dale FyeCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Dale FyeCommented:
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 FyeCommented:
@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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

  • 3
  • 3
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now