connecting a form with a repeating subform into access database

Dealing with Databases and LiveCycle documents is a pain. Especially when you have a repeated subform. It's my understanding that there are some fancy ways the document can be setup to automatically do this, but I don't know what they are. I have a different approach that I think is better.

First, database connections are a two way street. Data both goes into and comes out of the DB and the fields the DB is connected to. In order to hide the DB from the users and get better control over the process of what goes where I connect the database to a set of invisible fields. The visible fields on the form are not connected to anything. Tnen I use code to move data from the visible fields to the hidden fields, and from there into the DB. Or in the oposite direction.

Another reason for doing things this way is to handle the repeated fields. Usually the database will have several tables that are related to each other, for example, a customer info table and an order table. On the form there is only one set of customer info, or one row of info in the customer info table. But there might be several order items in repeated fields, which relate to several rows in the order info DB.

We have two different sets of data on the form which are connected to two different DB tables and are written into the the two different tables in different ways. It's much easier to handle this all in code, rather than trying to set up field connections to do it. It also allows you to have much greater control over how the user interacts with the form.

Anyone have any luck with repeatable subform database insertions?

I have an expense report with a reapeatble subform which contains the project name, cost, date etc. Everything goes into my mySQL database fine except any other instance of the subform.

Any help? I'd love to know the fancy way he talks about above 8>)

Thanks to Thomp's suggestion, you can try this:

Let "Sub" = your repeated subform with fields "name", "cost", "date" inside page "YourForm"
Create a hidden subform similar but not repeatable.
Let "hidSub" = your hidden subform with fields "name", "cost", "date" inside page "YourForm"
Link fields to your DB using name "DataConnection"
Create a loop like this on "click" in a button:

// counts instances
var Sub = YourForm.resolveNodes("Sub[*]");
var subLength = Sub.length;

//assigns index to instances and starts with the first instance
for (var i = 0; i < subLength; ++etdhp){
var iSub = YourForm.resolveNode("Sub[" + i + "]");

//creates new row in your DB
xfa.sourceSet.DataConnection.addNew();

//copies first "Sub" fields to "hidSub" fields
hidSub.name.rawValue = iSub.name.rawValue;
hidSub.cost.rawValue = iSub.cost.rawValue;
hidSub.date.rawValue = iSub.date.rawValue;

//moves ""hidSub" field values to your DB
xfa.sourceSet.DataConnection.update();
}
//goes to the next instance and repeat the process up to the last instance

hope this helps