Adding bulk parent-child records 1

[AppDev76]

Hi,
I have a Orders and OrderDetails form for data entry.
I know how to design parent child forms, but the problem here is that I want the user to add all the child records and then hit a "done" button. Then I will add the parent record and loop through the child records and add them to the child table.
My question:
With Access 2000 adp, is there any way to add records to a recordset? somthing like a temp table or a memory table on the client?

I've read about ado.net and datasets, is there anything in ADO that can do the same.



I've got around similar problems with bounding controls to a temp table on sql server, but that is very dificult and gives lots of error if there is a problem with the connection.

Thanks for your help

 

[lincschris]

You will be pleased to know that the answer is yes, it can be done, but it is not straight forward.

You will need a form, set up for form view for the order head details with relevant buttons (such as "Done") on and a sub-form in datasheet view for the order lines.

As part of the form load action, generate a unique number and store this in a hidden field on the order head form.

When the order head details are filled in, this record is saved to a temp order head table with the unique number in the hidden field as its index. The focus should then be moved to a field in the header section of the sub-form, which is by default numbered as line 1 (this may or may not be in a hidden field). The user enters the details of the line item and adds it to the list, which in effect saves the line item into a temporary order details table, using the unique number on the order head form and the line numbers as the ideces (the order head number will be the referential link anyway). The code then clears the header fields, changing the line nuber to "2". The line that has just been added is then displayed in the body section of the sub-form.

User then carries on to their hearts content.

When the order is complete, the user clicks on the "Done" button and the details are copied from the temporary table to the "real" table.

To keep tabs on the unique number, in each of my databases, I keep a "System" table which has several columns, but only one row of data. In here I store my various unique numbers, dates (useful if you have to keep tabs of when the last time you exported or reported on data was), etc.

As an alternative, I presume that you don't want to allocate an order number until the user is certain that they have the order filled correctly. What you could do is assign the unique number with say a "Z" prefix to ensure that the number does not correspond to an existing order number, saving this in your order number field. As part of your "Done" code you could replace this number with a proper order number and if you have update amend on your tables link, the order lines will be automatically updated. If the user decides that they dont want this order after all, code associated with the close form could run a simple SQL command to delete the head record (again, if the link is udate delete the children will go too).

 

[AppDev76]

Thanks for your reply.
You have mentioned a temporary table. My question is how to I bind a form to a temp table with a SQL Server backend.
I've tried that before and its kind of dificult.

I actually took another appraoch for the same problem for a web based application.

I add all the fields of the Order table to a session variable and carry it on.
For the Order details I add each new item to an array.
At the end I have all the fields of the Order table in the session variable and an array of all the details.
Then I would insert the Order using a stored procedure and after insert in the same stored procedure I select the Ident_current which returns the ID of the just inserted record. Then I use the ID and loop through the array and add it to the OrderDetails.

The array works just like a temp table that I need. But I though there might be an easier way to do it in Access

 

[cmmrfrds]

ADO allows batch updating.
Open the recordset using BatchOptimistic with client side cursor and static recordset.
Set the recordset connection to nothing to disconnect the recordset.
Loop through an insert and update records (recordset is fully functional only not connected to source).
Reconnect the recordset with
Set rs.activeconnection = cn.connection.
rs.UpdateBatch

Hopefully all the records are clean (pass all constraints). It is probably easiest to CancelBatch and find the record that erred. There are method, properties in ADO to do this.

 

[AppDev76]

Thanks alot cmmrfrds
This is what I was looking for.
Do you know any example on the web so I can look at it.

 

[cmmrfrds]

I have a book which is a couple of years old by Rob Macdonald called "Serious ADO". He does a good job of walking through the process with code examples. I picked it up at Barnes and Noble, and recommend it as a resource.