Double substitution

[Biggles]

Probably not a good title, but here is my problem. I have a table that
defines the relationship between one variable and the eventual source for
that value. Here is that table:
CONV_VALUE FIELD_TAG FIELD_VALUE_SOURCE
188743767 AuditHrs rs(1)!yr1audhrs
188743768 ITHRS rs(1)!yr1ithrs

RS(7).open selects the above table and RS(1) selects the source where the
values such as audit hours and IT hours are held.

I want create this:
V_NUM_FIELD = "'" & rs(7)!CONV_VALUE & "'"

ssql(3) = "INSERT INTO MSP_NUMBER_FIELDS " & _
"(PROJ_ID,
NUM_CATEGORY,NUM_REF_UID,NUM_FIELD_ID,NUM_VALUE) " & _
"VALUES (" & v_proj_id & ",0," & v_TASK_UID & "," & _
rs(7)!CONV_VALUE & "," & V_NUM_FIELD & ")"

But I know that my ssql(3) will end up as
....Values(...,188743767,'rs(1)!yr1audhrs')

as opposed to

....Values(...,188743767,300) which is what I want.

Is this possible without writing a large select case statement, (which means
that I would have to maintain two lists, original table and the select case)?

 

[John W. Vinson]

Probably not a good title, but here is my problem. I have a table that
defines the relationship between one variable and the eventual source for
that value. Here is that table:
CONV_VALUE FIELD_TAG FIELD_VALUE_SOURCE
188743767 AuditHrs rs(1)!yr1audhrs
188743768 ITHRS rs(1)!yr1ithrs

RS(7).open selects the above table and RS(1) selects the source where the
values such as audit hours and IT hours are held.

I want create this:
V_NUM_FIELD = "'" & rs(7)!CONV_VALUE & "'"

ssql(3) = "INSERT INTO MSP_NUMBER_FIELDS " & _
"(PROJ_ID,
NUM_CATEGORY,NUM_REF_UID,NUM_FIELD_ID,NUM_VALUE) " & _
"VALUES (" & v_proj_id & ",0," & v_TASK_UID & "," & _
rs(7)!CONV_VALUE & "," & V_NUM_FIELD & ")"

But I know that my ssql(3) will end up as
...Values(...,188743767,'rs(1)!yr1audhrs')

as opposed to

...Values(...,188743767,300) which is what I want.

Is this possible without writing a large select case statement, (which means
that I would have to maintain two lists, original table and the select case)?

Take a look at the Eval() function - not sure it will help though. You may
need to set a variable to the value from the recordset.

 

[Biggles]

--
Yours Fictionally, Biggles


:

Take a look at the Eval() function - not sure it will help though. You may
need to set a variable to the value from the recordset.

John

Thanks for your help, I couldn't get that to work, so I went with the brute
force method:
Select Case rs(9)!CONV_VALUE
Case 188743731
v_NUM_FIELD = rs(1)!txtaudit_entity_no
Case 188743734
v_NUM_FIELD = rs(1)!AE_ABBV
Case 188743737
v_NUM_FIELD = rs(1)!txtAUDIT_ENTITY
Case 188743740
v_NUM_FIELD = rs(1)!txtMANAGER_NAME
Case 188743743
v_NUM_FIELD = rs(1)!transtatus
End Select

ssql(3) = "INSERT INTO MSP_TEXT_FIELDS " & _
"(PROJ_ID,
TEXT_CATEGORY,TEXT_REF_UID,TEXT_FIELD_ID,TEXT_VALUE) " & _
"VALUES (" & v_proj_id & ",0," & v_TASK_UID & "," & _
rs(9)!CONV_VALUE & "," & v_NUM_FIELD & ")"

I thought about creating a list of the fields and then some method of a For
Each, but had trouble with that, seemed to run into the same problems.