If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
field name as a variable
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... |
#2
|
|||
|
|||
field name as a variable
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 "Jim" wrote in message ... 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... |
#3
|
|||
|
|||
field name as a variable
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. "Jim" wrote: 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... |
#4
|
|||
|
|||
field name as a variable
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 -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Jim" wrote in message ... 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... |
#5
|
|||
|
|||
field name as a variable
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 Jim wrote: 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... |
#6
|
|||
|
|||
field name as a variable
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 "Jim" wrote in message news:G9qdnU3jcvnLx6nZnZ2dnUVZ_vOdnZ2d@massilloncab letv.com... 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 Jim wrote: 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... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Form, Subform, Tab key | 2nd_Stage_User | Using Forms | 17 | August 25th, 2006 12:30 AM |
Memo Field | Sam | General Discussion | 12 | November 10th, 2005 09:16 PM |
Syntax needed to get needed reports | Frank Lueder | New Users | 15 | January 6th, 2005 08:39 AM |
ASK Field Behavior | Greg | Mailmerge | 9 | July 2nd, 2004 02:44 PM |
variable in field name | brad | General Discussion | 1 | June 10th, 2004 08:24 PM |