Writing subform data to another table

[stanger1]

I have a form with two subforms. Both subforms are based on queries. There are two tables that I need to write the information to. There are no relationships between the two tables. I can use DAO to write unbound fields to my main table, but, I need to find a way to write subform data to my second table. Does anyone have experience with this. Any help would be greatly appreciated.

 

[casey]

I'm probably missing something here, but...

You can declare two recordsets rs and rs2. rs being set to your main table and rs2 being set to your second table. If you can handle the DAO operations from the first rs, then simply create another recordset and update whichever recordset when needed.

I would set the recordsets to your subforms' recordsets. This will allow you to view the data the way it is in your subform.

 

[stanger1]

Right On

Casey,

Your suggestions worked great! I have one item however, that is not working out. My form that is bound to the invoice table has two fields that are unbound. (Total and Invoice_no) Each item on the invoice form gets written to the invoice table. I need to get the Invoice_no field and Total field to write to the invoice table for each item number. I have used your suggestions to get the subforms to write but, the unbound fields want to write a new record without the item number. I used the following to try to write unbound fields to the invoice table:

Dim Db As DAO.Database
Dim rs As DAO.Recordset

Set Db = CurrentDb
Set rs = Db.OpenRecordset("Invoice")

rs.AddNew
rs("Total").Value = me.Total
rs("Invoice_no").Value = me.Invoice_no
rs.update
set db = nothing
set rs = nothing

Thanks!

 

[casey]

Try replacing the...
rs("Total").Value = me.Total
rs("Invoice_no").Value = me.Invoice_no

with...
rs![Total]=Me.[Total]
rs![Invoice_no]=Me.[Invoice_no]


Whether the fields are bound or unbound should have no bearing on the recordset (As long as the preceding fields actually exist in your table!)

The recordset should probably be set to your table. Because if these fields don't exist in the subform's recordset, you will run into problems.

 

[stanger1]

Thanks for your help! I think this will work.