how do I convert a string to an argument.

[Guest]

I'm writing a VB script to read multiple fields from 1 table to INSERT INTO
each into separate records of another. There are many fields and I don't want
to have lots of separate insert statements. However I have a list of the
field names which I can read in from another table as strings. I want to wrap
a single INSERT INTO statement in a loop where I successively give it a
different fieldname to use as a SELECT fieldname argument. But when I try to
pass these field names to the INSERT INTO statement it writes the ascii
string and not the value of the field.

How can I make the docmd.sql(INSERT INTO TableName SELECT fieldnames)
statement see these fieldnames as arguments and not ascii strings.

 

[Douglas J. Steele]

It would be something like

DoCmd.RunSQL("INSERT INTO TableName SELECT " & field1 & ", " & field2 & ", "
& ...)

 

[John Vinson]

I'm writing a VB script to read multiple fields from 1 table to INSERT INTO
each into separate records of another. There are many fields and I don't want
to have lots of separate insert statements. However I have a list of the
field names which I can read in from another table as strings. I want to wrap
a single INSERT INTO statement in a loop where I successively give it a
different fieldname to use as a SELECT fieldname argument. But when I try to
pass these field names to the INSERT INTO statement it writes the ascii
string and not the value of the field.

How can I make the docmd.sql(INSERT INTO TableName SELECT fieldnames)
statement see these fieldnames as arguments and not ascii strings.

Put them in square brackets, with the SELECT clause in parentheses.
The SQL string should resemble

INSERT INTO TableName
(SELECT [Thisfield], [Thatfield] FROM sourcetable);


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps