Form with subform in transaction

[Azaverte]

Hello everyone,

I am weighing my options to make a "New record" or "Edit record" single-record pop-up form including a subform for related records, that would prompt the user to save or discard the changes. As you most certainly know, the problem here lies with the subform, since on a bound form changing the focus from the form to the subform automatically commits the changes. The options I've found for bypassing this limitation are listed below:

• Use temp tables for the main record and related records, with action queries to commit the changes to the main tables. I know I can make it work, and the drawbacks of temp tables are tolerable in my case as there will never be many concurrent users trying to edit the same record at the same time. But it still feels like a hack at the end of the day.
• Use a transacted bound form with a subform, as documented by Kodecabinett in their article about transactions, and several forum posts from some time ago. Code-wise, this seems much cleaner and less prone to implementation errors than temp tables. The obvious drawback here is the risk of corruption as pointed out by Kodecabinett. So I wanted to ask, do any of you have experience with this approach in a production environment?

Thank you in advance for your help.

 

[GPGeorge]

What problem are you trying to avoid or resolve?
Thinking through the process:

1. A record is committed to the one-side table via the main form to which it is bound when you leave that main form. Whatever fields have been populated in that table are saved, but if there are no entries in controls for other fields, they are left null. Standard Access behavior.
2. Focus moves to the Subform which is bound to the many-side table. The user either does or does not commit one or more records to that many-side table via the sub form.

It is possible to create the parent record via the main form, with some fields populated and others not populated. It is also possible not to add child records in the related table at that moment, by navigating away from the subform before adding any records.
As I imagine it, if there are required fields in the parent table, you can add validation to the main form's BeforeUpdate event, preventing the user from saving the record before entering the subform. The same would be true for required fields in the child table.

The only hole I can see here is that the user could complete a parent record and not add any child records. Is that the problem you want to wrap in a transaction? What, within that process, is the undesirable part? What are you trying to avoid? Have I overlooked another possibility?

 

[Ken Sheridan]

The attached little demo file illustrates three methods of forcing at least one row to be inserted into a referencing table when a row is inserted into a referenced table.

1. A conventional bound form and subform is used. As it is necessary fro a new row inserted via the parent form to be saved before a row is inserted into the referencing table via the subform, code in the parent form's Current and Unload event procedures checks to see if a row has been inserted into the referencing table. If not the code navigates back to the record in the parent form. This process will be repeated until a row has been inserted into the referencing table via the subform.

2. The second method uses an unbound form, and calls a function in which INSERT INTO statements are executed within a Transaction.

3. This method is similar to 2 above, but rather than a Transaction a CHECK CONSTRAINT is applied to the referenced table.

I'd recommend the first option as this maintains the simplicity and other advantages of using bound forms

 

[RonPaii]

Hello everyone,

I am weighing my options to make a "New record" or "Edit record" single-record pop-up form including a subform for related records, that would prompt the user to save or discard the changes. As you most certainly know, the problem here lies with the subform, since on a bound form changing the focus from the form to the subform automatically commits the changes. The options I've found for bypassing this limitation are listed below:

• Use temp tables for the main record and related records, with action queries to commit the changes to the main tables. I know I can make it work, and the drawbacks of temp tables are tolerable in my case as there will never be many concurrent users trying to edit the same record at the same time. But it still feels like a hack at the end of the day.
• Use a transacted bound form with a subform, as documented by Kodecabinett in their article about transactions, and several forum posts from some time ago. Code-wise, this seems much cleaner and less prone to implementation errors than temp tables. The obvious drawback here is the risk of corruption as pointed out by Kodecabinett. So I wanted to ask, do any of you have experience with this approach in a production environment?

Thank you in advance for your help.

So you have a form where the parent record is not valid until a child record is complete? You may have a problem in your table normalization.

I would add a "add new" dialog box to collect the required information and then use a transaction with add new queries to add the data to the tables when OK is clicked in the dialog box.

 

[mike60smart]

Hello everyone,

I am weighing my options to make a "New record" or "Edit record" single-record pop-up form including a subform for related records, that would prompt the user to save or discard the changes. As you most certainly know, the problem here lies with the subform, since on a bound form changing the focus from the form to the subform automatically commits the changes. The options I've found for bypassing this limitation are listed below:

• Use temp tables for the main record and related records, with action queries to commit the changes to the main tables. I know I can make it work, and the drawbacks of temp tables are tolerable in my case as there will never be many concurrent users trying to edit the same record at the same time. But it still feels like a hack at the end of the day.
• Use a transacted bound form with a subform, as documented by Kodecabinett in their article about transactions, and several forum posts from some time ago. Code-wise, this seems much cleaner and less prone to implementation errors than temp tables. The obvious drawback here is the risk of corruption as pointed out by Kodecabinett. So I wanted to ask, do any of you have experience with this approach in a production environment?

Thank you in advance for your help.

1 way would be to have a Form for New records.
A search form that lists all existing record.
Then a Click event on a specific record to allow edits.

 

[MarkK]

You might also implement a concept like RowState. Add a field with that name to any table and you have complete control regardless of what Access does. When the user clicks Save, modify RowState as required. Then you can make your own rules, like if a child row is committed, commit the parent.

Then during OnCurrent you can easily alter your UI to only allow actions consistent with the current state.

This is a great application for a small class module.
• Pass it any Recordset in its constructor, and if it can find a RowState field, then it can expose state methods and properties and remain totally agnostic about the rest of the schema for that row.
• Pass in an optional ParentTableName and ParentForeignKeyFieldName, and now this class can manage row state for the current row, and for the parent row, and remain completely agnostic about any schema, so it would just work for any recordset.

 

[Albert D. Kallal]

Ok, I'm going to give my 2 cents on this one.

It would be REALLY cool and really nice if we could wrap (with ease) a main form + sub form editing into one simple transaction.

After all, from a user's point of view, when editing say a invoice (and the classic sub form of invoice details)?
To them, it's just a "thing", and a simple save button to "save" and perhaps exit the "whole deal" they are looking at?

Yes sir, sign me up - from a user "UI" point of view, this is a great UI.

However, from the access "roots" of a bound form model?

It's just oh so messy. There are some examples - (using ADO recordsets), but really, at the end of the day, I suggest to "try" or at least "consider" some workarounds.

The problem is say vb.net or other windows platforms? They were from day one "set up" to work with a un-bound data source. As a result, they have wizards and all kinds of things to "help" with such an approach. In Access, if you dump/drop the data bound forms development model? Well, then you not only lack a bunch of wizards and "helper tools"?

But you also THEN lose many of the forms events, such as before up, after update - and a good many more. As noted, one can try to cobble together using dis-connected recordsets, but it really becomes a lot of work. And if you commit to doing this for one form + sub form, then you probably have to commit to doing this for all form + sub forms. So, it really goes against the RAD and simple approach that Access has - and that's a big reason for it's ease of use.

So, what have I done in the past to "get around" this problem?

A really effective work around is to introduce the concept of a "batch", or better stated a approved, or close batch process here.

Say for example we are doing that classic "quick books" like screen in which we receive a check/payment, and you THEN have to distribute that payment among several accounts. And of course as you split out the one check payment value into multiple accounts, then "eventually" the total check/payment value MUST = the distributed amounts to all the accounts you split up that payment into.

Now, with a un-bound setup, then the main form, payment amount, and then the distributed payments in the sub form?

Well, quite easy to have a single save button, and of course you can't exit until such time that the total amounts distributed to accounts matches the one single donation payment.

Well, the simple approach is to allow the user to edit, distribute, and EVEN if the totals don't match? Let them keep working!

What you THEN do is have a close/posting routine, and it simply does not let you "post" the run of payments until such time the sub form values match. (and by sub form, I actually mean the child table here).

So, what you do is have a new table, it is "higher" up, or simply one record that the "posting run" records are attached to.

And this approach works very well for workflow. Since the user can work, enter payments (and then distribute those payments). They can close down Access, keep working the next day.

THEN they can post the batch. That's when you test/check and ensure that the check/donation amount matches the distributed amount. And since you have a "batch" record, then that record has a simple posting flag, who posted, and what the posting run was for.

So, the form in question looks like this:

So, on left side, the user enterd $50. Now, on the right side, they have to keep entering (distrubting) amounts untill the total box goes "green".
However, I don't lock them out - they can stop entering anytime they want.

However, as above shows, that batch run is now already posted - and the form is now read only....

So, we have a top most record - the batch run......

So, from the user's point of view? They enter records, and when done, they post and close that batch run. This bodes well for such types of tasks, since with accounting packages, they have the same concept.

And I dare say, this simple approach not only allowed to ensure that payments (distributed) amounts match, but it eliminated the need for some kind of "single" save button for a form overall. And it even eliminated a bunch of complex code to "prevent" the user from entering a un-balanced set of distributued amounts for that payments. (the user is even free to exit that data entry form - come back later). However, until the batch is posted, then we don't include those records in reports etc. And we not really posting (despite the users thinking they are). In fact only ONE flag in the record shows that this batch been posted -- along with date and time and who did the data entry.

So, in place of trying to re-code and fight how Access works? Just introduce the above workflow - I found this approach works VERY well for such types of form + sub forms in which the data entry has to meet some special requirements (in this case, the distributed amounts have to match the payment amounts). So, don't lock the user out, don't prevent them from entering data that does not (yet) meet the requirements. Let them play, enter data - and then introduce that final posting and verification step.....

Now, to be fair, I can't say the above approach will work for all such cases (in which a single save wrapped in a transaction would be ideal), but in a lot of cases, the above re-design works well. And since it's building a "list" of work done, this bodes well from a user workflow point of view.

And the big bonus? Well, then we keep the familiar access bound forms model. So, it's a developer + coding friendly approach.

The lesson here?
Don't try and fight and re-purpose how Access works. Think of the overall final goal here, and then provide a UI and workflow that solves this problem - not trying to re-design how Access works. So, just let users enter data, and it's saved. And then have a separate "verification" run, which I called "posting" for this narrative.

I have found the above workflow works so well, that even if I had form + sub form transactions and a single save? I'm not convinced I would go down that road anyway.....


R
Albert

 

[Azaverte]

Thank you very much for your replies.

To clarify, what I'm trying to do is replicate the standard behavior of most programs outside of Access, that have a dialog to edit an item and its children items, and the changes made in that dialog are only saved when the user clicks the OK / Save button, or discarded if the user clicks Cancel. And this should work not only for item creation, but also existing item modification. It is not a validation issue as in my case a parent item may or may not have children items, and there is no other validation rule to speak of.

My app is meant to replace poorly designed Excel worksheets. Since there is no Ctrl-Z with Access, I must provide an alternative that still feels familiar to end users, otherwise they will stick with Excel or use dedicated commercial software if the project is big enough.

If I understand MarkK and Albert's answers correctly, a proposed workaround is to keep the usual bound form with subform but have a "state" field in the underlying tables to signal that a parent or child record has not yet been committed. I'm seeing two problems with that:

• While this system seems very fitting for item creation, I find it harder to see how it would work for modifying existing items with the option to discard changes.
• My backend database will be accessed not only from the Access frontend over which I have control, but also from Excel with the Power Query utility. Because of this I'm reluctant to include "system" fields that make no sense to the end user, or to have validated records alongside "draft" records. One way around this is to use temp "system tables" that are invisible to Power Query, thus coming back to my initial post.

Nevertheless I must say I fully support Albert's suggestion to stay within the "guardrails" of Access bound forms as much as possible in the general case.

 

[Gasman]

Since there is no Ctrl-Z with Access

Really?, works fine for me on each control. Do you expect it to work for the whole form?

 

[Azaverte]

@Gasman A user who knows nothing about Access or databases would expect it to work for the whole form just like it works for an entire Excel spreadsheet, so yes that's what I want to find an alternative to.

 

[Albert D. Kallal]

Ok, and for my "narrative" here?
My narrative was not so much about having a single "save" button for the form + subform, but ONLY when you have or want a transaction type of save, or NOT to allow the save.

So, my example was the sub form in which several rows are added but the total MUST match the total donation amount (in the parent form). So, my narrative was ONLY how to handle such cases when you want to prevent the save, or the user MUST have some type of "totals" amount that matches before they are done. So, keep in mind my narrative was NOT about a single save button, but only that of allowing the user to continue when they meet certain criteria. Because I didn't (don't') want to lock up the users and get them stick in a form that they can't save?

Then my suggestion is to move out this "requirement" to a batch like process. That way, the users don't care, don't get locked in a form/dialog system in which they are stuck. You then have a 2nd step AFTER data entry that checks their work, and if they failed to meet the correct criteria, then they can't post successfully.

Now, back to wanting a "save" button? My experience is don't bother, and don't go down this road.

While one can make a case for the save button? There are many cases in which it just causes more pain for the end user.

A great example? Do (or will) you have any forms in which you allow record navigation. If you do, then why does a users have to hit save?

This is really not different then if software developers wrote code for a car door. When the user goes to close the door, why popup a dialog saying:

Do you want to close the door?

Why? The fact that user trying to close the door, means they want to close the door, and tossing in an extra dialog just makes their work flow rather uncomfortable.

so, if you ALWAYS 100% popped up a form to edit a single record? Then ok, the case for that save button is stronger. However, if you allow record navigation in a form, then if they made changes, and then hit next record? Why bother them with a save? And then what you going to do if they save no? Do you stay on the current record, and not navigate to the next record? or do you navigate to the next record and not save.

And I can 100% ensure you? You going to get all kinds of support calls that the record was not saved!!! - it's just the way users are!!!

Also, why "some" or quite a bit software does have a save button?
Actually, the major trends in our industry is to not nag the users. In fact, look at tablet software - or phone software?

They now VERY rare pop up save dialogs. It just gets in the way of the users work flow.

And for editing multiple rows? Once again, in a continues form view? You don't want to prompt for each row changed.

And I should point out that if the user is in a main form, then un-do, or ctrl-z does work.

FoxPro, Access, dBase and more?
For over 25 years, they never did prompt for a save - it not a new issue here!

Go into settings on your Android phone - again, toggle features, change some things? You hit back button, no prompts occur.

So, the main issue and challenge here?
You have to try and write boatloads and boatloads of code, and boatloads of additional UI here - and all for what?

A nagging all day long for some save prompt?

Now, of course as you note, users are often used to this type of UI, but the trends are to move away from such a UI - and phones + tablets are driving this trend (and then we wonder why people like phone and tablet software so much! answer: it nags them less!!! - that's why!).

Access has never worked with, nor required save prompts - and its been that way for over a 1/4 of a century.

There are two work arounds:

If the goal is to prevent accidental editing, then we "reverse" this, and place a "edit" button on the form. And in such cases, if any user(s) hit the edit button? We actually write out to a log table that user "such and such on workstation unlocked that record for editing". That way, if changes are made, then we know who, and user's can't edit by accident. And hitting "edit" means we then simply "enable" all controls, including the sub-form control. Without hitting the edit button, then accidental changes can't occur.

But, the reverse? Having a save prompt? Nope, you really don't need them.

The 2nd workaround?
You need to use a different product then Access.......

So, be it FoxPro, Ms-Access and a slew of PC based database systems for the last 25+ years? They all by default don't prompt for saving of data....

Your users will not care, and after a short leaning curve? They will wonder why all that other software nags them to death all day long....

You really don't have a practical solution here with Access - you just don't, and the product does not work with, nor require save prompts.

R
Albert

 

[Gasman]

To be fair, I seem to recall Foxpro was very different to Access.
In Foxpro, one scattered to memvar and then gathered from memvar.

So in effect, equivalent to an unbound form in Access?

 

[Albert D. Kallal]

To be fair, I seem to recall Foxpro was very different to Access.
In Foxpro, one scattered to memvar and then gathered from memvar.

So in effect, equivalent to an unbound form in Access?

Sure, you could do this, but you talking about editing "one" record. If we were to limit this discussion to not a form + sub form (which by nature mean repeating reocrds in the sub form)? Sure, then FoxPro could be aruged to handle this issue. But, on the other hand, EVEN in Accesss, and EVEN with a bound form to a table? If you are only editing one record, then you can very well provide a save button, and if the user bails out, a me.undo will suffice. In other words, you can WELL build a design in access in which a save button is used for saving data in the form.

The problem here is of course mutliple reocrds. And in fact, even in FoxPro when you used the "browse" command to display data in a table/grid/repeating data?

It did not prompt for save when you made changes to a record.

So, much of this issue comes down to allowing edits against repeating rows of data - and then providing a single save button.

Now, to be fair? How does one do this in say vb.net?

Why of course the answer is to use in-memory tables, and what we call dis-connected recordsets.

So the BIG question then?

Can we do this in Access?

And in fact the answer is yes, we can do this. The answer lies in adopting dis-connected reocrdsets, and that means using ADO ones, and not DAO ones!

So, while I hinted and suggested that Access by nature not really the correct product for a single save button (again, in the context of a form + sub form with repeating rows)?

Well, ok, yes, one could do this. In other words, it's often tempting to wrap the reocrdets in a transaction, and go down that road. Some exmaples "kind of" work, but they tend to be problematic, and not worth the efforts.

However, having stated all of the above?

I would suggest that using dis-connected recordsets in Access is quite much the way to go here.

So, design approach:

Pull, load required data for main form into a reocrdset. (dis-connected recordset)

Pull, load required (repeating) data for sub form into a recordset. (dis-connected recordset).

You then bind these recordsets to the form (and sub form).

At this point, we enjoy the RAD and bound forms model.

However, edits of data is now going to "in memory" reocrdsets - the user can add, edit, delete rows. But NONE of these operations are occuring against the live table data, but in memory reocrdsets.

The single save button can then simply write back the 2 recordsets.

And in theory, those 2 write back to the database are two operations (save commands).

And, in theory, if those tables have edits, additions, deletes?

They should all go back to the database - and do so with "one operation" command per table to save.....

So, what this means?

If the goal and need of a single save button REALLY was the deal breaker here?

Then one has a few practical choices:

1) Don't use Access

2) or consider the above approach (disconnected recordsets).

3) - a choice I taken off the table is wrapping the reocrdsets in a transaction

Now, I taken choice 3 off the table, since if one going to wrap the recordsets in transactions?
Well, you quite much have to build separate reocrdsets for the main form, and sub form. And this becomes messy, and since one is ALREADY committed to building (and writing) code for the recordsets?

Then one might as well go with option #2 - since one has to build the recordsets anyway!
In other words, the code cost is simular, and it's easier to load up multiple recordsets, and you don't have to worry about transactions, and we STILL get use of bound forms. It means that edits and changes are going from UI to the dis-connected recordsets, and that part is automatic.

In other words, I would not consider giving up bound forms - it's just a question of what you going to bind the form + sub form to?

Now, of course, I not done a proof of concept on this idea, but since I do a lot of .net work?
The dis-connected reocrdsets work really well, I just not attempted this in Access.

So, while I have "discouraged" the poster from going down this road?

I MOST certainly think using dis-connected reocrdsets (without a transaction) is the way to go - it's just in all these years of my Access experiance, I never attempted such a design. So, I would suggest to the poster, a proof of concept is a great idea. And I think this dis-connected reocrdset option will also not increase development costs by too much......

So, anyone here worked with dis-connected recordsets in Access? That's the Rosetta stone answer here if one going to use Access, and MUST have a single save button.....

Some years ago, I attempted the transaction approach - it was too messy - too much pain.

But, dis-connected recordsets? That's the best bet here IMHO....

Edit:
Unfortunately, while dis-connected ADO recordsets can be used in Access VBA?
You can NOT bind such recordsets to a forms data source! - Sorry folks, this option is off the table:

Edit#2:
I guess the other possible option is to use temp tables. But then again, this means a process to shuffle data from those table(s) back into the database - and with PK/FK values - this is too much pain....

Edit#3:
After giving all the above some thoughts?
I think the best choice now is to wrap the reocrdsets in a transaction. There are "some" issues to fix/deal with, but all in all, the "least" number of addition moving parts suggests that building the main form recordset, and the child form reocrdset in VBA, (wrapped in a transaction), and then binding these two recordsets to the form and sub form probably is the least pain full approach.....




R
Albert

 

[Edgar_]

Save yourself from a lot of headaches and:
1. Create a continuous form that displays all the parents. This form is where you will add parent records. Do not add child records in this form.
2. Create a separate continuous form for adding child records. You can only access this form by selecting a parent from the list of parents.

You get no confused users like this. The reason is simple: You keep concerns separated, users understand the hierarchy, they know where to modify children, and where to modify parents. No need for a parent? delete it from the parents list. No need for a children? delete it from the list of children, that can only be open from the parent.

Simple and standard. Your users most likely use this pattern already, it's widely used in web.