Access03/WinXP

I've looked through many samples and searches and *think* I have figured out
how to programmatically create a temporary table and create the necessary
fields, but I have yet to see how to add data to that table from a recordset.

If I have rs as a recordset with at least ContactID, DateCanceled as
selected fields, and have created tdfNew, appended to the database, and added
the fields ContactID and DateCanceled, how do I now add records to that table.

Typically, I have manually created the temporary table in the database
window in design view and then used a Do While Not rs.EOF loop to add records.

Do While Not rs.EOF
with tdfNew
.AddNew

The problem is that as soon as I type the period where the AddNew should be
inserted, AddNew is not one of the options.

Can anyone give me an example, or point me to a resource, where there is a
sample to programmatically create a table, add fields, and then populate said
table? Much appreciated!

Hi,
how did you declare rs?
if this is recordset, ADO or DAO, addnew should work. do you get an error
when you run your code?

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

"Pendragon" <(E-Mail Removed)> wrote in message
news:73C9DE08-2C4E-40C3-922E-(E-Mail Removed)...
> Access03/WinXP
>
> I've looked through many samples and searches and *think* I have figured
> out
> how to programmatically create a temporary table and create the necessary
> fields, but I have yet to see how to add data to that table from a
> recordset.
>
> If I have rs as a recordset with at least ContactID, DateCanceled as
> selected fields, and have created tdfNew, appended to the database, and
> added
> the fields ContactID and DateCanceled, how do I now add records to that
> table.
>
> Typically, I have manually created the temporary table in the database
> window in design view and then used a Do While Not rs.EOF loop to add
> records.
>
> Do While Not rs.EOF
> with tdfNew
> .AddNew
>
> The problem is that as soon as I type the period where the AddNew should
> be
> inserted, AddNew is not one of the options.
>
> Can anyone give me an example, or point me to a resource, where there is a
> sample to programmatically create a table, add fields, and then populate
> said
> table? Much appreciated!

Hi there,

You need to open a recordset on your new table to add records to it.

Set rsTarget = db.OpenRecordset("NewTableName")
Do While Not rs.EOF
With rsTarget
.AddNew
!ContactID = rs!ContactID
!DateCanceled = rs!DateCanceled
.Update
End With
rs.MoveNext
Loop

Jon.

"Pendragon" wrote:

> Access03/WinXP
>
> I've looked through many samples and searches and *think* I have figured out
> how to programmatically create a temporary table and create the necessary
> fields, but I have yet to see how to add data to that table from a recordset.
>
> If I have rs as a recordset with at least ContactID, DateCanceled as
> selected fields, and have created tdfNew, appended to the database, and added
> the fields ContactID and DateCanceled, how do I now add records to that table.
>
> Typically, I have manually created the temporary table in the database
> window in design view and then used a Do While Not rs.EOF loop to add records.
>
> Do While Not rs.EOF
> with tdfNew
> .AddNew
>
> The problem is that as soon as I type the period where the AddNew should be
> inserted, AddNew is not one of the options.
>
> Can anyone give me an example, or point me to a resource, where there is a
> sample to programmatically create a table, add fields, and then populate said
> table? Much appreciated!