Use Variable to Increment Field Name and Assign Value

I need to create a report that shows the first six codes that apply to each
company horizontally. The codes are stored in individual records in the real
data but I am writing them out to another table with six fields labeled
code1, code2,code3, etc.

To do that I would like to treat the field name as a variable such as
strCurrentField which would be assigned the value "Code" & cstr(intNextCode)
which would resolve to "Code1", "Code2" etc as it went through a loop and
progressed through the companies records. What I want to do is assign a
value to the expression contained in the variable and have it inset that
value in the field of the same name.
In the old dbase/clipper days you would have preceeded the variable name
with an & (macro substitution)and the program would have treated the content
of the variable as a literal expression. Any idea how to do that in VBA?

Re: Use Variable to Increment Field Name and Assign Value

On Sun, 30 Dec 2007 10:55:12 -0500, "KC-Mass"
wrote:

If I understand you correctly, the Eval function is what you need.
-Tom.


>I need to create a report that shows the first six codes that apply to each
>company horizontally. The codes are stored in individual records in the real
>data but I am writing them out to another table with six fields labeled
>code1, code2,code3, etc.
>
>To do that I would like to treat the field name as a variable such as
>strCurrentField which would be assigned the value "Code" & cstr(intNextCode)
>which would resolve to "Code1", "Code2" etc as it went through a loop and
>progressed through the companies records. What I want to do is assign a
>value to the expression contained in the variable and have it inset that
>value in the field of the same name.
> In the old dbase/clipper days you would have preceeded the variable name
>with an & (macro substitution)and the program would have treated the content
>of the variable as a literal expression. Any idea how to do that in VBA?
>

Re: Use Variable to Increment Field Name and Assign Value

Thanks Tom

I think you are on to something. I will have to look into the syntax. What
I tried is below
but I get a 2482 error that reports that it "can't find name 'RSOutPut'".
The "RSOutPut"
is the name of the outbound recordset which works fine for all the other
data.
Thx Again
snipet of code follows

intNextCode = 1
strCompanyID = RSSource!CompanyID
Do While RSSource!CompanyID = strCompanyID And intNextCode < 7
strCodeMask = "RSOutPut!Code" & CStr(intNextCode)
'This is where we want to stick the Source tables "Code"
'value into the OutPut tables "Code1" field
Eval(strCodeMask) = RSSource!Code
intNextCode = intNextCode + 1
RSSource.MoveNext
Loop



"Tom van Stiphout" wrote in message
news:1sgfn3hno9rndpt1r8p5te1v5avbd9ud16@4ax.com...
> On Sun, 30 Dec 2007 10:55:12 -0500, "KC-Mass"
> wrote:
>
> If I understand you correctly, the Eval function is what you need.
> -Tom.
>
>
>>I need to create a report that shows the first six codes that apply to
>>each
>>company horizontally. The codes are stored in individual records in the
>>real
>>data but I am writing them out to another table with six fields labeled
>>code1, code2,code3, etc.
>>
>>To do that I would like to treat the field name as a variable such as
>>strCurrentField which would be assigned the value "Code" &
>>cstr(intNextCode)
>>which would resolve to "Code1", "Code2" etc as it went through a loop and
>>progressed through the companies records. What I want to do is assign a
>>value to the expression contained in the variable and have it inset that
>>value in the field of the same name.
>> In the old dbase/clipper days you would have preceeded the variable name
>>with an & (macro substitution)and the program would have treated the
>>content
>>of the variable as a literal expression. Any idea how to do that in VBA?
>>

Re: Use Variable to Increment Field Name and Assign Value

On Sun, 30 Dec 2007 12:03:03 -0500, "KC-Mass"
wrote:

I'm still a bit fuzzy on what you're trying to accomplish. Perhaps
this helps?
s = s & rsOutPut("Code" & intNextCode)
This would get the value of the Code1, Code2, etc. fields.
If that's all you need, Eval is not needed.

Also, if you want to save data to a recordset, you have to use:
rs.Edit (or .AddNew)
rs.Update

-Tom.


>Thanks Tom
>
>I think you are on to something. I will have to look into the syntax. What
>I tried is below
>but I get a 2482 error that reports that it "can't find name 'RSOutPut'".
>The "RSOutPut"
>is the name of the outbound recordset which works fine for all the other
>data.
>Thx Again
>snipet of code follows
>
>intNextCode = 1
>strCompanyID = RSSource!CompanyID
>Do While RSSource!CompanyID = strCompanyID And intNextCode < 7
> strCodeMask = "RSOutPut!Code" & CStr(intNextCode)
> 'This is where we want to stick the Source tables "Code"
> 'value into the OutPut tables "Code1" field
> Eval(strCodeMask) = RSSource!Code
> intNextCode = intNextCode + 1
> RSSource.MoveNext
>Loop
>
>
>
>"Tom van Stiphout" wrote in message
>news:1sgfn3hno9rndpt1r8p5te1v5avbd9ud16@4ax.com...
>> On Sun, 30 Dec 2007 10:55:12 -0500, "KC-Mass"
>> wrote:
>>
>> If I understand you correctly, the Eval function is what you need.
>> -Tom.
>>
>>
>>>I need to create a report that shows the first six codes that apply to
>>>each
>>>company horizontally. The codes are stored in individual records in the
>>>real
>>>data but I am writing them out to another table with six fields labeled
>>>code1, code2,code3, etc.
>>>
>>>To do that I would like to treat the field name as a variable such as
>>>strCurrentField which would be assigned the value "Code" &
>>>cstr(intNextCode)
>>>which would resolve to "Code1", "Code2" etc as it went through a loop and
>>>progressed through the companies records. What I want to do is assign a
>>>value to the expression contained in the variable and have it inset that
>>>value in the field of the same name.
>>> In the old dbase/clipper days you would have preceeded the variable name
>>>with an & (macro substitution)and the program would have treated the
>>>content
>>>of the variable as a literal expression. Any idea how to do that in VBA?
>>>
>

Re: Use Variable to Increment Field Name and Assign Value

Thanks Tom

I am just not being clear today - cold medicine does that to me.

If I only had one "Code" field in the table I would do something like

RSOutPut!Code = Something

Because I have multiple code fields in the output table I want to, in
essence, treat
the field name as a variable so that I can store the second code
into the Code2 field, the third into Code3, etc.

I could do it with if statements or a case structure but that seems unartful
and would not be reusable
if the number of Code fields needed changed.

If you did it with If statements it would look like

If intNextCode = 1 Then
RSOutPut!Code1 = RSSource!Code
End If
If intNextCode = 2 Then
RSOutPut!Code2 = RSSource!Code
End If
etc


So if you look at the two pieces of text :RSOutPut!Code1 and RSOutput!Code2,
they can easily be constructed
in each iteration of a loop. What I need to be able to do is use that
varied string in an assignment statement.

Thx




"Tom van Stiphout" wrote in message
news:lkmfn3hs5kmhbd8upib5u2e46vj9m9timm@4ax.com...
> On Sun, 30 Dec 2007 12:03:03 -0500, "KC-Mass"
> wrote:
>
> I'm still a bit fuzzy on what you're trying to accomplish. Perhaps
> this helps?
> s = s & rsOutPut("Code" & intNextCode)
> This would get the value of the Code1, Code2, etc. fields.
> If that's all you need, Eval is not needed.
>
> Also, if you want to save data to a recordset, you have to use:
> rs.Edit (or .AddNew)
> rs.Update
>
> -Tom.
>
>
>>Thanks Tom
>>
>>I think you are on to something. I will have to look into the syntax.
>>What
>>I tried is below
>>but I get a 2482 error that reports that it "can't find name 'RSOutPut'".
>>The "RSOutPut"
>>is the name of the outbound recordset which works fine for all the other
>>data.
>>Thx Again
>>snipet of code follows
>>
>>intNextCode = 1
>>strCompanyID = RSSource!CompanyID
>>Do While RSSource!CompanyID = strCompanyID And intNextCode < 7
>> strCodeMask = "RSOutPut!Code" & CStr(intNextCode)
>> 'This is where we want to stick the Source tables "Code"
>> 'value into the OutPut tables "Code1" field
>> Eval(strCodeMask) = RSSource!Code
>> intNextCode = intNextCode + 1
>> RSSource.MoveNext
>>Loop
>>
>>
>>
>>"Tom van Stiphout" wrote in message
>>news:1sgfn3hno9rndpt1r8p5te1v5avbd9ud16@4ax.com...
>>> On Sun, 30 Dec 2007 10:55:12 -0500, "KC-Mass"
>>> wrote:
>>>
>>> If I understand you correctly, the Eval function is what you need.
>>> -Tom.
>>>
>>>
>>>>I need to create a report that shows the first six codes that apply to
>>>>each
>>>>company horizontally. The codes are stored in individual records in the
>>>>real
>>>>data but I am writing them out to another table with six fields labeled
>>>>code1, code2,code3, etc.
>>>>
>>>>To do that I would like to treat the field name as a variable such as
>>>>strCurrentField which would be assigned the value "Code" &
>>>>cstr(intNextCode)
>>>>which would resolve to "Code1", "Code2" etc as it went through a loop
>>>>and
>>>>progressed through the companies records. What I want to do is assign a
>>>>value to the expression contained in the variable and have it inset that
>>>>value in the field of the same name.
>>>> In the old dbase/clipper days you would have preceeded the variable
>>>> name
>>>>with an & (macro substitution)and the program would have treated the
>>>>content
>>>>of the variable as a literal expression. Any idea how to do that in VBA?
>>>>
>>

Re: Use Variable to Increment Field Name and Assign Value

On Sun, 30 Dec 2007 14:02:43 -0500, "KC-Mass"
wrote:

OK. Combining your information I think your code can be written as:
rsOutput.AddNew
for i = 1 to 6
rsOutput("Code" & i) = rsSource("Code")
rsSource.MoveNext
next i
rsOutput.Update

-Tom.


>Thanks Tom
>
>I am just not being clear today - cold medicine does that to me.
>
>If I only had one "Code" field in the table I would do something like
>
>RSOutPut!Code = Something
>
>Because I have multiple code fields in the output table I want to, in
>essence, treat
>the field name as a variable so that I can store the second code
>into the Code2 field, the third into Code3, etc.
>
>I could do it with if statements or a case structure but that seems unartful
>and would not be reusable
>if the number of Code fields needed changed.
>
>If you did it with If statements it would look like
>
>If intNextCode = 1 Then
> RSOutPut!Code1 = RSSource!Code
>End If
>If intNextCode = 2 Then
> RSOutPut!Code2 = RSSource!Code
>End If
>etc
>
>
>So if you look at the two pieces of text :RSOutPut!Code1 and RSOutput!Code2,
>they can easily be constructed
>in each iteration of a loop. What I need to be able to do is use that
>varied string in an assignment statement.
>
>Thx
>
>
>
>
>"Tom van Stiphout" wrote in message
>news:lkmfn3hs5kmhbd8upib5u2e46vj9m9timm@4ax.com...
>> On Sun, 30 Dec 2007 12:03:03 -0500, "KC-Mass"
>> wrote:
>>
>> I'm still a bit fuzzy on what you're trying to accomplish. Perhaps
>> this helps?
>> s = s & rsOutPut("Code" & intNextCode)
>> This would get the value of the Code1, Code2, etc. fields.
>> If that's all you need, Eval is not needed.
>>
>> Also, if you want to save data to a recordset, you have to use:
>> rs.Edit (or .AddNew)
>> rs.Update
>>
>> -Tom.
>>
>>
>>>Thanks Tom
>>>
>>>I think you are on to something. I will have to look into the syntax.
>>>What
>>>I tried is below
>>>but I get a 2482 error that reports that it "can't find name 'RSOutPut'".
>>>The "RSOutPut"
>>>is the name of the outbound recordset which works fine for all the other
>>>data.
>>>Thx Again
>>>snipet of code follows
>>>
>>>intNextCode = 1
>>>strCompanyID = RSSource!CompanyID
>>>Do While RSSource!CompanyID = strCompanyID And intNextCode < 7
>>> strCodeMask = "RSOutPut!Code" & CStr(intNextCode)
>>> 'This is where we want to stick the Source tables "Code"
>>> 'value into the OutPut tables "Code1" field
>>> Eval(strCodeMask) = RSSource!Code
>>> intNextCode = intNextCode + 1
>>> RSSource.MoveNext
>>>Loop
>>>
>>>
>>>
>>>"Tom van Stiphout" wrote in message
>>>news:1sgfn3hno9rndpt1r8p5te1v5avbd9ud16@4ax.com...
>>>> On Sun, 30 Dec 2007 10:55:12 -0500, "KC-Mass"
>>>> wrote:
>>>>
>>>> If I understand you correctly, the Eval function is what you need.
>>>> -Tom.
>>>>
>>>>
>>>>>I need to create a report that shows the first six codes that apply to
>>>>>each
>>>>>company horizontally. The codes are stored in individual records in the
>>>>>real
>>>>>data but I am writing them out to another table with six fields labeled
>>>>>code1, code2,code3, etc.
>>>>>
>>>>>To do that I would like to treat the field name as a variable such as
>>>>>strCurrentField which would be assigned the value "Code" &
>>>>>cstr(intNextCode)
>>>>>which would resolve to "Code1", "Code2" etc as it went through a loop
>>>>>and
>>>>>progressed through the companies records. What I want to do is assign a
>>>>>value to the expression contained in the variable and have it inset that
>>>>>value in the field of the same name.
>>>>> In the old dbase/clipper days you would have preceeded the variable
>>>>> name
>>>>>with an & (macro substitution)and the program would have treated the
>>>>>content
>>>>>of the variable as a literal expression. Any idea how to do that in VBA?
>>>>>
>>>
>

Re: Use Variable to Increment Field Name and Assign Value

Thanks That works.

"Tom van Stiphout" wrote in message
news:bgrfn3dojteqochn0hi2hfpq35cru5fr20@4ax.com...
> On Sun, 30 Dec 2007 14:02:43 -0500, "KC-Mass"
> wrote:
>
> OK. Combining your information I think your code can be written as:
> rsOutput.AddNew
> for i = 1 to 6
> rsOutput("Code" & i) = rsSource("Code")
> rsSource.MoveNext
> next i
> rsOutput.Update
>
> -Tom.
>
>
>>Thanks Tom
>>
>>I am just not being clear today - cold medicine does that to me.
>>
>>If I only had one "Code" field in the table I would do something like
>>
>>RSOutPut!Code = Something
>>
>>Because I have multiple code fields in the output table I want to, in
>>essence, treat
>>the field name as a variable so that I can store the second code
>>into the Code2 field, the third into Code3, etc.
>>
>>I could do it with if statements or a case structure but that seems
>>unartful
>>and would not be reusable
>>if the number of Code fields needed changed.
>>
>>If you did it with If statements it would look like
>>
>>If intNextCode = 1 Then
>> RSOutPut!Code1 = RSSource!Code
>>End If
>>If intNextCode = 2 Then
>> RSOutPut!Code2 = RSSource!Code
>>End If
>>etc
>>
>>
>>So if you look at the two pieces of text :RSOutPut!Code1 and
>>RSOutput!Code2,
>>they can easily be constructed
>>in each iteration of a loop. What I need to be able to do is use that
>>varied string in an assignment statement.
>>
>>Thx
>>
>>
>>
>>
>>"Tom van Stiphout" wrote in message
>>news:lkmfn3hs5kmhbd8upib5u2e46vj9m9timm@4ax.com...
>>> On Sun, 30 Dec 2007 12:03:03 -0500, "KC-Mass"
>>> wrote:
>>>
>>> I'm still a bit fuzzy on what you're trying to accomplish. Perhaps
>>> this helps?
>>> s = s & rsOutPut("Code" & intNextCode)
>>> This would get the value of the Code1, Code2, etc. fields.
>>> If that's all you need, Eval is not needed.
>>>
>>> Also, if you want to save data to a recordset, you have to use:
>>> rs.Edit (or .AddNew)
>>> rs.Update
>>>
>>> -Tom.
>>>
>>>
>>>>Thanks Tom
>>>>
>>>>I think you are on to something. I will have to look into the syntax.
>>>>What
>>>>I tried is below
>>>>but I get a 2482 error that reports that it "can't find name
>>>>'RSOutPut'".
>>>>The "RSOutPut"
>>>>is the name of the outbound recordset which works fine for all the other
>>>>data.
>>>>Thx Again
>>>>snipet of code follows
>>>>
>>>>intNextCode = 1
>>>>strCompanyID = RSSource!CompanyID
>>>>Do While RSSource!CompanyID = strCompanyID And intNextCode < 7
>>>> strCodeMask = "RSOutPut!Code" & CStr(intNextCode)
>>>> 'This is where we want to stick the Source tables "Code"
>>>> 'value into the OutPut tables "Code1" field
>>>> Eval(strCodeMask) = RSSource!Code
>>>> intNextCode = intNextCode + 1
>>>> RSSource.MoveNext
>>>>Loop
>>>>
>>>>
>>>>
>>>>"Tom van Stiphout" wrote in message
>>>>news:1sgfn3hno9rndpt1r8p5te1v5avbd9ud16@4ax.com...
>>>>> On Sun, 30 Dec 2007 10:55:12 -0500, "KC-Mass"
>>>>> wrote:
>>>>>
>>>>> If I understand you correctly, the Eval function is what you need.
>>>>> -Tom.
>>>>>
>>>>>
>>>>>>I need to create a report that shows the first six codes that apply to
>>>>>>each
>>>>>>company horizontally. The codes are stored in individual records in
>>>>>>the
>>>>>>real
>>>>>>data but I am writing them out to another table with six fields
>>>>>>labeled
>>>>>>code1, code2,code3, etc.
>>>>>>
>>>>>>To do that I would like to treat the field name as a variable such as
>>>>>>strCurrentField which would be assigned the value "Code" &
>>>>>>cstr(intNextCode)
>>>>>>which would resolve to "Code1", "Code2" etc as it went through a loop
>>>>>>and
>>>>>>progressed through the companies records. What I want to do is assign
>>>>>>a
>>>>>>value to the expression contained in the variable and have it inset
>>>>>>that
>>>>>>value in the field of the same name.
>>>>>> In the old dbase/clipper days you would have preceeded the variable
>>>>>> name
>>>>>>with an & (macro substitution)and the program would have treated the
>>>>>>content
>>>>>>of the variable as a literal expression. Any idea how to do that in
>>>>>>VBA?
>>>>>>
>>>>
>>