How to use variable to reference rst!Item?

[deko]

Here's how the loop would look *without* variables - I've hardcoded
FirstName, LastName and Company

Do While Not rstSource.EOF
strSql = "INSERT INTO [tblTest] SELECT rst!FirstName " & _
"AS FirstName, rst!LastName AS LastName, rst!Company " & _
"As Company FROM tblSource"
db.Execute strSql
Loop

The problem is, I don't know what the rst items will be (not always
FirstName, etc.) I have other code that tests for the items and builds the
rst, but I don't know what they are. So I want to use variables like this:

'pseudo code
Do While Not rstSource.EOF
strSql = "INSERT INTO [tblTest] SELECT rst!strVar1 " & _
"AS " & strVar1 & ", rst!strVar2 AS " & strVar2 & ", rst!strVar3 " &
_
"As " & strVar3 & " FROM tblSource"
db.Execute strSql
Loop

But how do I get rst!strVar when strVar is a variable?

Thanks in advance.

[pietlinden]

Maybe it's just me, but this post is really confusing. If you're
inserting records from one table to another, then why are you aliasing
the fields using AS?

If you don't know what the field names are, then you can do something
like

rst.Fields(intCounter)

It might help if you backed up a little bit and gave an example of your
table structure (tblTest) and some sample data (just a single record
should suffice).

Can what you are attempting be done? I think so, but it's very hard to
tell exactly what you want from your description.

[Steve Jorgensen]

On Sun, 31 Jul 2005 17:29:15 -0700, "deko" <de**@nospam.com> wrote:

Here's how the loop would look *without* variables - I've hardcoded
FirstName, LastName and Company

Do While Not rstSource.EOF
strSql = "INSERT INTO [tblTest] SELECT rst!FirstName " & _
"AS FirstName, rst!LastName AS LastName, rst!Company " & _
"As Company FROM tblSource"
db.Execute strSql
Loop

The problem is, I don't know what the rst items will be (not always
FirstName, etc.) I have other code that tests for the items and builds the
rst, but I don't know what they are. So I want to use variables like this:

'pseudo code
Do While Not rstSource.EOF
strSql = "INSERT INTO [tblTest] SELECT rst!strVar1 " & _
"AS " & strVar1 & ", rst!strVar2 AS " & strVar2 & ", rst!strVar3 " &
_
"As " & strVar3 & " FROM tblSource"
db.Execute strSql
Loop

But how do I get rst!strVar when strVar is a variable?

Thanks in advance.

This'll get you pointed in the right direction...

Dim fld As DAO.Field
For Each fld in rstSource.Fields
Debug.Print fld.Name
Next

[Salad]

deko wrote:

Here's how the loop would look *without* variables - I've hardcoded
FirstName, LastName and Company

Do While Not rstSource.EOF
strSql = "INSERT INTO [tblTest] SELECT rst!FirstName " & _
"AS FirstName, rst!LastName AS LastName, rst!Company " & _
"As Company FROM tblSource"
db.Execute strSql
Loop

The problem is, I don't know what the rst items will be (not always
FirstName, etc.) I have other code that tests for the items and builds the
rst, but I don't know what they are. So I want to use variables like this:

'pseudo code
Do While Not rstSource.EOF
strSql = "INSERT INTO [tblTest] SELECT rst!strVar1 " & _
"AS " & strVar1 & ", rst!strVar2 AS " & strVar2 & ", rst!strVar3 " &
_
"As " & strVar3 & " FROM tblSource"
db.Execute strSql
Loop

But how do I get rst!strVar when strVar is a variable?

Thanks in advance.

You can use variables
Dim strField As String
strField = "SomeField"
rst(strField) = "SomeData"

[deko]

> You can use variables

Dim strField As String
strField = "SomeField"
rst(strField) = "SomeData"

That was easy.

What I'm trying to do is create an SQL statement based on fields in a
particular table. But I never know what fields are in the table. So I loop
through the Fields collection to get a string of comma-separated field
names.

Here's what I came up with (not tested yet)

Dim astrFld() As String
astrFld = Split(strFields, ",")
Set rstSource = db.OpenRecordset(strSql)
Do While Not rstSource.EOF 'loop through each row in source table
For Each varFld In astrFld
strFld = Trim(varFld)
If Not IsNull(rstSource(strFld)) Then
strValue = Chr(34) & rstSource(strFld) & Chr(34)
Else
strValue = Chr(34) & Chr(34)
End If
strValues = strValues & IIf(Len(strValues) <> 0, ", " & strValue,
strValue)
Next varFld
strSql = "INSERT INTO tblOrders ( " & strFields & " ) VALUES ( " &
strValues & " );"