Use a Variable to call a Field in VBA 1

[ACCESSDUMMY]

I'm trying to use a variable in a loop for shortening my VBA code. I have 100 fields in a table that I need to run the same code on, but I don't know how to use a variable to reference a field name. Is this possible?

The fields in the table are as follows:

Fund 1
Fund 2
Fund 3
Fund 4
etc...
all the way to Fund 100


Here's the code I have so far.

Do Until rst.EOF
For i = 1 To 100
fieldvar = "rst.[Fund " & i & "]"
If fieldvar > 0 Then
rst2.AddNew
rst2!Employee = rst!Employee
fundvar = "Number = '" & i & "'"
rst3.FindFirst fundvar
If rst3.NoMatch Then
rst2![Fund Letter] = " "
Else
rst2![Fund Letter] = rst3![Corresponding Fund Letter]
End If
rst2![Fund %] = rst!["Fund " & i]
rst2.Update
End If
Next
Loop
rst.Close
rst2.Close
rst3.Close


Any help would be greatly appreciated!

 

[KenReay]

Hi

Do not follow your code exactly

but

rst("Fund " & i)

in particular I do not follow:

fieldvar = "rst.[Fund " & i & "]"
If fieldvar > 0 Then

do you mean if rst("Fund" & i) > 0 Then

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[p27br]

maybe you should normalize your table by having a lookup table with a fund number field with 100 records.Fund number 1 through 100, then have a column in your existing table that looks up the fund number in the lookup table. This way you will have less columns in your main table and your code will be easier to write.

 

[LittleSmudge]

Yes AccessDummy Ken is on the right track - its curved brackets you need rather than square brackets


See also FAQ700-2190 on why you should always avoid space characters in object names.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[vbajock]

rst("Fund " & i) should be

rst("Fund" & trim$(str$(i)))

 

[ACCESSDUMMY]

Thanks Ken for your help... it works now!