Append records from a set to another table.

[BrianDP]

I'm trying to duplicate the functionality of a routine that I'm
updating into a new database.

The old application assumes you have a filtered set of records behind a
form. The form is open, with the filtered recordset. Next it copies a
clone of the form's recordset into a table with one field (Customer) so
it can use this as a join table to print from.

I was able to re-write the loop and get it to work, but it sure seems
like it'd be more quickly done with a quick sql statement. Here is the
loopy, time consuming code I re-generated.

Set cRst = Forms![1b customer definition].RecordsetClone
Set pRst = db.OpenRecordset("3 print customers")

cRst.MoveFirst

Do While Not cRst.EOF
pRst.AddNew
pRst!customer = cRst!cust_code
pRst.Update
cRst.MoveNext
Loop

Someone tell me there is a faster, easier way to append the cloned
recordset into the target recordset. This seems so brute force. It
pained me to write the do loop!

Thanks in advance.

-BrianDP

[corey lawson]

BrianDP wrote:

I'm trying to duplicate the functionality of a routine that I'm
updating into a new database.

The old application assumes you have a filtered set of records behind a
form. The form is open, with the filtered recordset. Next it copies a
clone of the form's recordset into a table with one field (Customer) so
it can use this as a join table to print from.

I was able to re-write the loop and get it to work, but it sure seems
like it'd be more quickly done with a quick sql statement. Here is the
loopy, time consuming code I re-generated.

Set cRst = Forms![1b customer definition].RecordsetClone
Set pRst = db.OpenRecordset("3 print customers")

cRst.MoveFirst

Do While Not cRst.EOF
pRst.AddNew
pRst!customer = cRst!cust_code
pRst.Update
cRst.MoveNext
Loop

Someone tell me there is a faster, easier way to append the cloned
recordset into the target recordset. This seems so brute force. It
pained me to write the do loop!

Thanks in advance.

-BrianDP

Well, chances are you should be able to build the right WHERE clause
information from the .Filter property. The fastest way is to just do
something like this:

DoCmd.RunSQL "insert into NewTable (field, ..., fieldN) " & _
"select * from MyTable where ...filterinfo"