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
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