field name as a variable

[Jim]

I have a form that reads a table and using some of that data, writes to
another table. Works fine.

Table Y has field names are x_1, x_2 through x_10. The following snips
of code work fine.

sqlCode = "SELECT Y.x_1, Y.x_2, Y.x_3, etc...;"

set dynSet = dbs.OpenRecordset(sqlCode, dbOpenDynaset)
set dynOut = dbs("outPutTable", dbOpenDynaset)

With dynSet
.moveFirst
while not .OEF
.AddNew
dynOut!r = !x_1
.Update

.AddNew
dynOut!r = !x_2
.Update

etc...
============================
What I want is:

With dynSet
.moveFirst
while not .OEF
for x = 1 to 10
temp_field_name = "!x_" & trim(x)
.AddNew
dynOut!r = temp_field_name
.Update
next x
.moveNext
loop
End With

<<<<<<<<<<<<<>>>>>>>>>>>>
The problem I'm having is that as coded above Access thinks
temp_field_name is a literal. If I take out the "!" and code
dynOut!r = !temp_field_name then it can't find the item in the collection.

Is there any way around this? I know I can change the query to use the
variable as the field name but that doesn't sound like a workable
solution either.

TIA
Jim

P.S. since I have code that works, this is really an academic exercise...

 

[Jeff Boyce]

Jim

From your description, it sounds like you are trying to create "repeating"
fieldnames. Examples of this might be "Tariff1, Tariff2, Tariff3, ..." or
"January, February, March, ..."

If this seems related to what you're doing, you may be building a
spreadsheet, not a relational database.

More info, please...

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

It is just a syntax problem. Here is the fix:

With dynSet
.moveFirst
while not .EOF
for x = 1 to 10
.AddNew
dynOut!r = .Fields("x_" & trim(x))
.Update
next x
.moveNext
loop
End With

You could really stand to improve your naming conventions.

 

[Douglas J. Steele]

Jeff's correct that what you're trying to do doesn't sound appropriate for a
relational database system. However, if you must do it, try:

Dim temp_field_name As String

With dynSet
.moveFirst
while not .EOF
for x = 1 to 10
temp_field_name = "x_" & trim(x)
.AddNew
dynOut!r = .Controls(temp_field_name)
.Update
next x
.moveNext
loop
End With

 

[Jim]

Klatuu's solution worked fine.

To justify my existence, I just report off the database, I don't design
them. More to the point this is really an ODBC connection to a COBOL
ISAM file. This particular file has a occurs clause with 24 elements.
The field name is ppbcnnb_bnov_1 through ppcnnb_bnov_24. The field
names I used below were for simplicity sake.

==================================
It is just a syntax problem. Here is the fix:

With dynSet
.moveFirst
while not .EOF
for x = 1 to 10
.AddNew
dynOut!r = .Fields("x_" & trim(x))
.Update
next x
.moveNext
loop
End With

 

[Jeff Boyce]

Jim

Without a more detailed description, the following may not be relevant:

No matter what degree of normalization exists in the underlying data, you
are not limited to copying that structure in your Access database.

You could keep your link to the ISAM file, and create a normalized table
structure in Access. You would then create a series of queries that "load"
the normalized structure from the linked source.

Just one person's opinion

Jeff Boyce
Microsoft Office/Access MVP