HELP W/ ADO AND SUBFORMS

[ZylaWonder]

In an accounting solution I am writing it is necessary to use Transaction Processing and Rollback. I have created an unbound form and am using ADO to insert data into the table after certain validation rules are met(check amount not blank, etc.). I actually love the amount of control ADO gives to me and it works well. However, the form requires a subform where the accounting distribution is detailed. At present I am using a temp table to accomplish this task. After checking the KBase I have found good articles for using ADO with unbound forms, but all of the info is for forms w/out subforms. Apparently I am not smart enough to figure out this out!! Any and all help, as always, is GREATLY APPRECIATED!!

 

[cmmrfrds]

Can you lay out your requirements more specifically, it is not clear what you question(s) is/are?

Is it an sql server temp table? ADO disconnected recordset?

Is the subform linked to the main form on an id?

What is contained in the logical transaction? For example, a record on the main form and all the detail records on the subform that are linked?

Is the form/subform used only for insert? not update and delete?

What is the database? sql server, access 2000???

There are lots of possiblities, but first the requirements should be exact or the solution may be a false start.

 

[ZylaWonder]

Thank you for your reply. I apologize for the lack of clarity. The exact document being posted is a check. The solution is all coded in A2K, and the database is A2K. The subforms is linked to the main form by the check #. The main form contains base information such as date, payable to, memo, etc. The subform is used to insert the accounting detail. The subform is not used to edit data or delete data, even though a delete may be required in the event of an error(but only while using the current form). I have a relationship of one to many between DocumentDetail (one) and AccountDetail(many). The form is used to a) fill in the blanks to write a check b)"spread" the account distribution c) if the account distribution =0 then print the check d) insert data into the appropriate tables. One cannot delete a check from this form. I chose to use transaction processing because I do not need the tables to be updated until the check is actually printed (as via command button Print). I know that transaction processing is required for this solution. I see many accounting databases, even expensive solutions that have corrupt data because of this over-sight.

Again thanks for your assistance.

 

[cmmrfrds]

Here is a link that has a sample you can download and review.

http://www.rogersaccesslibrary.com/charlottefoust/charlottefoust.htm

There is a fair amount of coding involved. Unfortunately, in Access 2000 a form bound to a recordset is not updateable, so one has to detect and move the updated fields manually. Maybe Access 2002 provides updateable recordsets?

 

[ZylaWonder]

cmmrfrds;
This is a very helpful link with lots of good info. I did download the sample database from Charlotte Foust. However, the example is about the same as I have seen elsewhere. Again the example refers only to one table and not to multiple tables as I have. Should I be using an array here? I have got to figure this out it is driving me crazy!!

Thanks
ZW

 

[cmmrfrds]

It would make it so easy if Access 2000 allowed the Form recordset to be updateable, but that is not the case. In fact, before Access 2000 there was not even a Form recordset. Although in Access 2002 it may be updateable.

I would not use an array, instead ADO disconnected recordsets. Here is the general flow of how I would approach the problem.

Create a disconnected recordset form each table you want to update. How to do this, is to create a query with all the fields you want from each table but bring back an empty recordset - select * from table where id < 0 for example.
These will be used instead of the array to add the records you want to insert.

Next, close the active connection on each recordset, but do not set to nothing so that the connection is still available to be used later - the recordsets are now disconnected from the source. They can still be updated in Access, but will not at this time update the source.

Add the data from each form to the respective recordset until you have a logical grouping of records that will be your transaction.

Next, reconnect each recordset to the source by setting the recordset active connection to your connection.

Next, Do a begin transaction, then an updatebatch on the first recordset, then an updatebatch on the second recordset then do a commit transaction - all of this is done on the same connection object. Of course, there would need to be error checking and backout if an error occurred.

That is the general flow.

ADO has so many ways of doing the same thing. I think you can create an empty recordset by doing this.
rs.CursorLocation = adUseClient
rs.Locktype = adLockOptimistic
rs.Properties(&quot;Append-Only Rowset&quot = true
rs.Open &quot;select * from yourtable&quot;