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