Form with subform in transaction (1 Viewer)

[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.