pass a string to ADO recordset?

[gmrbase]

Hi folks,

I have an issue where I want to recode about 100 fields in a table ("Source Table" rsado3) into new values in a different table ("Target Table"). To do this, I've tried to automate it by storing field names from Source Table and Target Table in a third table ("Lookup Table", rsado4) and loop through fieldnames in a nested recordset (code piece is below).

I want Access to read the actual variable name stored as a string in a table ("t_var" in)from source table to recode (which is stored as a local string var "tvar").

How can I get Access to recognize the value of t_var as a piece of VBA code (i.e., an element of rsado3 and thus a field in source table), and not either

(a) a literal string
or
(b) an element of rsado4 ( a field in lookup table)

THANKS!!!

' BEGIN RECODES FOR CATEGORY
sql_str2 = "select ag.*, br.age_cat3 as age3 from tbl_recode_stp1_agpe2agyn as ag inner join tbl_benchmark_rawdata as br on ag.pid = br.pid;"
rsado3.Open sql_str2, cnn, adOpenKeyset, adLockOptimistic


Do While Not rsado3.EOF
notnull_count = 0

If rsado3![age3] = 1 Then
rsado4.Open "select * from tbl_agpe_recode_vars where agecat = 1;", cnn, adOpenKeyset, adLockOptimistic
Do While Not rsado4.EOF
tvar = rsado4![t_var]
If IsNull("rsado3!" & "[" & tvar & "]") = False Then
notnull_count = notnull_count + 1
rsado4.MoveNext
Else: rsado4.MoveNext
End If
Loop
rsado4.Close
rsado3![mean_n] = notnull_count
If rsado3![mean_n] <> 0 Then
age1_sum = 'Expression based on vars in rsado4..
rsado3![agpeyn] = age1_sum / rsado3![mean_n]
End If
rsado3.Update
rsado3.MoveNext


'END RECODES FOR CATEGORY
Loop

 

[Remou]

Perhaps:

[tt]If Not IsNull(rsado3.Fields(tvar)) Then[/tt]

http://lessthandot.com

 

[PHV]

I'd replace this:
rsado4![t_var]
with this:
rsado4.Fields(t_var)

and this;
("rsado3!" & "[" & tvar & "]")
with this:
(rsado3.Fields(tvar))

and so on ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gmrbase]

Thanks y'all.

Here's what worked...


Best,
L
' START RECODES FOR YOUNG AGE CATEGORY (3-8 MONTHS)
' If rsado3![age3] = 1 Then
' rsado4.Open "select * from tbl_agpe_recode_vars where agecat = 1;", cnn, adOpenKeyset, adLockOptimistic
' Do While Not rsado4.EOF
' tvar = rsado4![t_var]
' tvar2 = """rsado3![" & tvar & "]"""
' If IsNull(Eval(tvar2)) = False Then
' notnull_count = notnull_count + 1
' rsado4.MoveNext
' Else: rsado4.MoveNext
' End If
' Loop
' rsado4.Close
' rsado3![mean_n] = notnull_count 'this is adding a value where there are nulls (if notnull_count = 0)...
' If rsado3![mean_n] <> 0 Then
' age1_sum = (Nz(rsado3![agyn3_BF], 0) + Nz(rsado3![agyn3_FOOD], 0) + Nz(rsado3![agyn3_SLP], 0) + Nz(rsado3![agyn3_TV], 0) + Nz(rsado3![agyn3_CC], 0) + Nz(rsado3![agyn3_FL], 0) + Nz(rsado3![agyn3_BEHV], 0) + Nz(rsado3![agyn3_NW], 0) + Nz(rsado3![agyn3_COMM], 0) + Nz(rsado3![agyn3_UND], 0) + Nz(rsado3![agyn3_RESP], 0) + Nz(rsado3![agyn3_READ], 0) + Nz(rsado3![agyn3_HS], 0) + Nz(rsado3![agyn3_Car], 0) + Nz(rsado3![agyn3_Burn], 0))
' rsado3![agpeyn] = age1_sum / rsado3![mean_n]
' End If
' rsado3.Update
' rsado3.MoveNext