export subform's datasheet to temp table with VBA

[BrockWade]

Thanks in advance for any help. I am trying to export my subform's 'datasheet view' data to 'temp table' (it's a real table and I have code to clear it out to receive new data). I'm trying to loop through the below. This code works fine for exporting the first row of the datasheet, but I need it to do all the rows (between 50 and 100 rows usually display). Can someone coach me along with looping this?:

Dim strInsertBillingIntoTemp As String

strInsertBillingIntoTemp = "INSERT INTO Billing_Temp " & _
"(peopleId, audited,billYear,charge,pastDue," & _
"totalDue, peopleName, fee ) " & _
"VALUES( '" & _
Forms![reviewBilling]![reviewBillingSub].Form![People ID].value & "','" & _
Forms![reviewBilling]![reviewBillingSub].Form![cbxAudited].value & "','" & _
Forms![reviewBilling]![reviewBillingSub].Form![Bill Year].value & "','" & _
Forms![reviewBilling]![reviewBillingSub].Form![Charge].value & "','" & _
Forms![reviewBilling]![reviewBillingSub].Form![Past Due].value & "','" & _
Forms![reviewBilling]![reviewBillingSub].Form![Total Due].value & "','" & _
Forms![reviewBilling]![reviewBillingSub].Form![Name].value & "','" & _
Forms![reviewBilling]![reviewBillingSub].Form![Fee Type].value & "');"



DoCmd.RunSQL strInsertBillingIntoTemp

[pbaldy]

Far more efficient if feasible would be an SQL statement like:

INSERT INTO...
SELECT...
FROM...
WHERE...

That assumes you can select from the subform's source table and apply a criteria that would return the same records displayed on the subform. That would get you all the records in one pass, rather than one-by-one.

[June7]

This code is behind main form? I always name subform container different from the object it holds, like ctrDetails.

Aside from what Paul suggests, your options are to physically loop through the records of the subform (you will see cursor move to each record on the form) or to use the form's RecordsetClone to loop records (user won't see anything).

Why does code reference cbxAudited and not a field name like the other lines?

[BrockWade]

The database was inherited by me so I've not had a chance to convert the existing names of fields to something better and granted the temp table I put together doesn't have the best naming either. The data that feeds the subform is a complex multi-table query that of course creates a one way street so I can't use that to update so I needed a temporary table to hold the current records basically I just need to allow the user to check and uncheck these boxes and because the subform cannot be set to "data entry" that has to be done to a clone of the displayed data placed into a temporary table to allow updates using a separate independent form/subform. So I just need a way to loop through the data displayed and write it to a temp table. Like I said I can use the basic code I have which is working but it only writes the first row to the temp table.

[pbaldy]

If the subform is based on a query, you can use the append query syntax I posted with the query in the FROM clause, and you don't even need a WHERE clause.

INSERT INTO...
SELECT...
FROM QueryName

[June7]

Paul's approach could be feasible. Maybe could use the subform query for an UPDATE or INSERT action.

CurrentDb.Execute "INSERT INTO Billing_Temp(peopleId, audited, billYear, charge, pastDue, totalDue, peopleName, fee) SELECT [people Id], audited, [bill Year], charge, [past Due], [total Due], [Name], [fee type] FROM queryname"

CurrentDb.Execute "UPDATE tablename Set somefield=something WHERE ID IN queryname"

Otherwise, looping RecordsetClone:

With Me.ctrDetails.Form.RecordsetClone
While Not .EOF
...
.MoveNext
Wend
End With

Still don't know why your posted code references combobox name instead of field. Referencing the control name complicates.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Nor reserved words as names - Name is a reserved word.