How do you Control Bound SubForm Transactions? (1 Viewer)

[sneuberg]

Background: I have a database in which I need to ensure that if a record exists in a certain parent table that a certain set of records exist in the child table. If a record is added to the parent table then certain related child records must be added. It's an all or nothing situation.

The article at https://support.microsoft.com/en-us/kb/248011 shows how to use transactions with a bound form. I created a small test database which is attached. It has a main form name frmParent which has a subform named sfrmChild. It has buttons to begin, commit or rollback a transaction. If you begin a transaction and then change something on the frmParent it doesn't get updated in the bound table ParentTable until you commit the transaction. So this work as it should. But this doesn't extend to the subform sfrmChild. Changes there update the table ChildTable even though there is a uncommitted transaction in progress.

One thing that I have found is key to this working is setting the forms record set to a DAO record set as it shows in the article, but I have done this in the sfrmChild on open event with

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM ChildTable", dbOpenDynaset)
Set Me.Recordset = rs

As an aside this wipes out the links which I restore in the frmParent's on open event.

So can anyone tell me why this isn't working? How do you get a transaction to work with both the form and subform?

 

[Uncle Gizmo]

I think we need to clarify what's happening because from what you have said I don't think I would go about it in the way you are going about it.

What is it about the parent record that dictates the type of records that should be added to the child table?

These child records that are to be added, where do they come from? how are they calculated, constructed, stored?

 

[sneuberg]

These are rental rates of six different types of apartments which all have the same effective dates. Usually the rent is increased once a year, but in every case the new effective dates for all of the rates are the same. A history of all these rates is required. A report might be to list the tenants who were in the apartments in 1956 and what they were paying. So the parent records in this case have the effective dates and the child records have the rental rates. When new rates are added the parent record with the date and ALL the child records rates must be entered in the same transaction. If the user is allowed to do only part of this transaction the reports will be messed up.

But even if you would do this differently I want to know how to do this with a transaction so I know how to do it.

 

[Uncle Gizmo]

There isn't a right or wrong way to do it, you have to do it the way that seems best to you. However my issue is not whether the way to do it is right or wrong, it's working out what you have, how it relates, and where you want it to end up. I can't see an answer any of those questions with the information you have currently provided.

 

[Galaxiom]

spikepl was trying to use transacted bound forms with a subform a while back.

http://www.access-programmers.co.uk/forums/showthread.php?t=280933

There is a link to an example of a transacted bound form on that page. I probably should have taken more interest in it at the time. The example could be used as is with the subform alone. However I don't see and reason why it couldn't be implemented with both the main form and subform included.

Don't finalise the transaction until the subform records meet the business rules.

 

[jdraw]

I'd like to hear a little more about the business rules involved also. As I read the requirements, you have a parent record(1 side of relation) and a known number of child records (many side of relation). So the transaction would cover the addition of parent and the addition of X children. And all of this must happen en masse successfully, or none of it.
The issue it seems is that the parent must exist before any child can be added.

Perhaps I have misunderstood/confused the business requirement.

 

[sneuberg]

I'd like to hear a little more about the business rules involved also. As I read the requirements, you have a parent record(1 side of relation) and a known number of child records (many side of relation). So the transaction would cover the addition of parent and the addition of X children. And all of this must happen en masse successfully, or none of it.
The issue it seems is that the parent must exist before any child can be added.

Perhaps I have misunderstood/confused the business requirement.

No, I think you understand it exactly. For example. Lets say there are only two types of apartments to simplify this; one bedroom and two bedroom which currently rent out at $100 and $200 respectively. Those rents have been in effect since Apr 1, 2015. So there's a parent record with the date Apr 1, 2015 and the child records having $100 and $200. On Apr 1 2016 the rates will go up to $110 and $220. So the user will add a new parent record with Apr 1, 2016. The user can't be allowed to stop there and not add the the child records as the queries in this system will expect them to be there. We could put in code to detect their absence and display an error message, but I'd rather avoid it in the first place. Adding the parent record and just one of the rates would be just as bad. They have to be added together or not at all.

I'm making progress on this. It seems to be the linking between the form and subform that interrupts the transaction. If I don't restore the links between the form and subform the transactions extend to both of them.

 

[jdraw]

I have attached an ERD based on my understanding of the things involved to help with business context.