Hello all and thank you in advance yor the support!

For a small database project I'm currently designing, I have the typical setup of a table for employees and a one-to-many table that holds the trainings these individuals have undergone.

For editing I have setup a parent form with the employee data and within in a continuous subform, linked to the parent, that shows the respective trainings.

Now what I would like to have is a saving process, that only occurs when a button on the parent form is pressed. For the 'parent' data that's manageable. But I would also like to include the subform (relational) data in this process.

Is this possible?

I was experimenting with filling the subform by manually setting recordsets via VBA, basically using memory as a buffer, but since they always stay linked to the underlying table data was immediately saved, whenever fields were changed.

Then I tried to buffer the data in a separate table and forward it on the the original source, once the user "saves". However my DB is split up into Frontend and remote Backend files and I couldn't figure out, how to have the buffer locally instead of on the BE (which I assumed would be much slower).

So does anybody have an idea on how to do this or improve on the previous attempts?


To save the subform record:

If Me!NameOfSubformControl.Form.Dirty = True Then
    Me!NameOfSubformControl.Form.Dirty = False
End If

To save the main form record:

If Me.Dirty = True Then
    Me.Dirty = False
End If
  • FIrst of all thank you for the answer, although I'm afraid I'll need a bit more context than just your short snippet of code. Do I paste this into the Dirty-Event Subs? Which of my scenarios does you answer address? ... – M.Toppin Jun 9 at 8:02
  • You have a button on the main form, so I imagined you would use its Click event. If you only wish to save the subform, omit the second part. – Gustav Jun 9 at 10:14

Bound forms usually saves changes immediately. If you would like to prevent immediate changes, you could:

  1. Have the subform as non editable /locked form: (prevents editing at all)
  2. Intercept the Form_BeforeUpdate(Cancel As Integer) event to determine whether save is allowed or not.

There are several ways of doing 2. Here is a pseudo/logic In your subForm's

Form_BeforeUpdate(Cancel As Integer)
    If (not savingAllowed) then
        Cancel = true ' Prevents save
        Me.Undo       ' Undos all changes
    End If
End Sub;

obviously you need to expand this savingAllowed where you determine if the save action was authorised or not.

  • Thank you. I tried something like this also. However I got stuck on the input fields and wasn't able to leave them without losing the changed content. I will try again tomorrow in the office and get back to you. – M.Toppin Jun 9 at 8:03

To be able to save the sub-form data upon command and not automatically as soon as the user edits it you need to use a temporary table (probably what you call "buffer"). That table can very easily be local. Some details may vary depending on how your training table is set up but basically open both the front and back end, navigate to tables on the back end, copy and paste the training table to the front end making sure to give it a different name and copy only structure, not data.

Now that you have your local temporary table you should set up the following: On the main form's Load/Current events, optionally ask if you want the existing data to be saved, wipe the temporary table and copy all entries from the main table for the displayed employee only.

Upon Save delete all records from the main training table for the displayed employee and copy the records from the temporary table.

When the form is closed, wipe the temporary table.

Alternatively, if your training table is properly set up with a primary key and you prefer not to delete and reinsert records all the time, set up your temporary table so that the primary key is not auto-increment. That will allow you to update records instead of deleting and re-inserting them at the cost of manually managing the primary key in the temporary table.

Things get much trickier if your front end is shared rather than each user having their own copy, so you should probably avoid that. You will still have potential issues if multiple users are trying to edit the same employee training records, but that will probably not happen in the course of business as usual.

  • Thank you. This for the largest part covers my idea as well, but I was too unspecific. I was trying to figure out how to do this with VBA code alone, so to speak a stand-alone and transferable option. Meaning I wanted to create the local table via VBA and scrap it afterwards. I seems I wanted to overdue it, since for this immediate problem I can simply create the table manually and just use it. But do you have an idea of how to make the process transferable to other form-subform and table-subtable situations? – M.Toppin Jun 9 at 8:04
  • Since you need the subform to be bound to the temporary table, it is not a good idea to create/destroy the table every time. It is much more efficient to use static temporary tables. – SunKnight0 Jun 10 at 12:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.