Microsoft Access subform as a sandbox before actual updating of the data

Question

I have an Access 2013 DB with 2 subforms: The first one provides current data and the second one I'm trying to set up as a sandbox space for users to make changes and updates at their leisure. When they are ready to submit those changes and updates they can hit submit and their results will update the current data and table info behind it.

Been a while since I've messed with Access so I can't recall if this can be done so thought I would ask.

Answer 1

It can’t be done without a rather messy approach. Forms in access ALWAYS edit ONE row.

What I do in these cases is allow the user to “edit” as much as they please.

However, say we are doing a “classic” check or donation amount enter. So you have a donation amount (say $50), but you THEN have to distributed the amounts to various donation accounts.

With the above, you don’t want to save the data until such time that the amounts distributed to accounts match the donation amount.

Because we can’t contain the form + sub form as a “single” update, then what I do is add a posting flag to the parent form (the one record in the main form).

The user can then edit/add, go away for coffee, and come back and edit some more. You can even come back the next day and continue editing.

Once the user is happy, then you have a post button on the form.

If the user hits post, and the balance amounts don’t match, then you don’t set the postOK flag = true.

The above will thus NOT provide a means for the user to bail out, or un-do their edits to the “whole thing”, but it does allow you to determine that the data entered has been “balanced” and verified. And if the “balance” did not occur, then you can skip that record, or introduce a process in which the data is not posted or marked as done until such time that the data balances.

Here is a screen shot in Access of a “classic” money distribution to several accounts. You can see the donation amount on the left side, and on the right side is the account distribution of that amount.

However, at the bottom of the screen, for the “job task” the user is working on also has a post button. When they hit the post button, if the amounts don’t balance out, then the posting is NOT yet done. In fact, I let the user enter "many" donations, and then they can "post" all at once for ease and speed of working. The "post" does NOT actually post, but checks the balance. So users "think" they are posting, but it only really ever sets one value (a flag POSTOK).

So to run a report, it can’t be run until such time that the post button was hit.

As noted, this approach will NOT give you a “bail out” option to exit your data entry, nor does it allow you to “undo” the data entry, but it DOES allow you to verify the posting is “ok”, and “valid”. In many cases this approach will suffice.

However, if you looking for some kind of “undo” for the form + sub form edit, then Access quite much is not the correct tool for that type of business model and edit.