Variable Fieldnames in DAO.Recordset

[craigachan]

I'm hoping someone can help me solve this issue. I have a table with a lot of fields in the same row with similar names the differences are numbers.

D1, D2, D3, ......
Chr1Amt, Chr1Code, Chr1Desc, Chr1Dx, . . . .
Chr2Amt, Chr2Code, Chr2Desc, Chr2Dx, . . . .

And so on. I have been able to run thru these field for the most part using the following from tblLedger

Dim c as long
for c = 1 to 10
me("Chr" & c & "Amt") = something
me("Chr" & c & "Code") = something
me("Chr" & c & "Desc") = something
next c

But when working with a dao.recordset I start having problems with the names.

I've tried:

Dim rs as dao.recordset, msql as string
1 msql = "SELECT * FROM tblPosts WHERE ID = " & me.ID
2 set rs = currrentdb.openrecordset(msql)
3 Do until rs.eof
4 for c = 1 to 10
5 me("Chr" & c & "Amt") = rs!("Chr" & c & "Amt")
6 next c
7 rs.movenext
8 loop
9 rs.close
10 set rs = nothing

The problem I'm having is on line 5 referencing the rs!. . . . . I've tried rs("Chr" & c & "Amt), rs!("Chr" & c & "Amt) which isn't recognized
and I've tried
Dim strF as field
set strF = "Chr" & c & "Amt" so then rs(strF) But I can't get it to work. this part is a little over my head and I'm hoping that someone might be able to help me.

thanks in advance.

 

[theDBguy]

Hi. rs(fldVar) or rs.Fields(fldVar) should both work.

However, having multiple fields with sequential numbers in the name is usually a sign of non normalized table design.

 

[arnelgp]

you need to re-structure your table.
it violates Normalization rule.

 

[craigachan]

Thanks for your feedback. This database has been around a long time and working as is and I was hoping not to mess it up at this time with restructuring. I'll inform the boss that this will be an issue at some time.

What worked is simplified below. And all fields are recognized.

dim strD as string, strA as string
for c - 1 to 10
strA = "Chr" & c & "Amt"
strD = "Dx" & c
me(strD) = rs.Fields(strD)
me(strA) = rs.Fields(strA)
next c

The loop for c is all in the same row of the table. If you're working with several rows, I brought up on row at a time in the rs recordset.

Thanks everyone for your help.

 

[theDBguy]

Thanks for your feedback. This database has been around a long time and working as is and I was hoping not to mess it up at this time with restructuring. I'll inform the boss that this will be an issue at some time.

What worked is simplified below. And all fields are recognized.

dim strD as string, strA as string
for c - 1 to 10
strA = "Chr" & c & "Amt"
strD = "Dx" & c
me(strD) = rs.Fields(strD)
me(strA) = rs.Fields(strA)
next c

The loop for c is all in the same row of the table. If you're working with several rows, I brought up on row at a time in the rs recordset.

Thanks everyone for your help.

Hi. Good luck with your project.