Referencing a field using a variable 2

[Chesterex1]

I have code as follows

Dim strField as string, dbT as Database, rsT as RecordSet
strField = "something"
Set dbT = CurrentDb
Set rsT = dbT.OpenRecordset("table&quot
if rsT!strField = "na" then ....


I WANT to use a variable to reference the field name in a recordset - Is this possible? As I have a table with over 235 fields and they are named sequentially - I want to perform the same function over and over on the same fields.
Thanks
Tim

 

[KenReay]

Hi

You can reference the fields as if they were an array so

Rs(0)=

or via a variable which contains the field name so

strMyField = "Fieldname"

Rs(strFieldname)

so if you field names were Field1, Field2 ...etc, you could do

For i = 1 to n
strFieldName = "Field" & i
Rs(strFieldName) = ...blah
..etc
Next i

does that help?

Regards

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

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

UK

 

[LittleSmudge]

Yes it's possible - and you were almost there

You need

if rsT(strField) = "na" then ....

I have used this with numerically indexed fields too.
Eg
If you have fields called Fld1, Fld2, Fld3, .. ..

You can use

Dim intC As Integer

For intC = 1 to 3
    If rst("Fld" & intC) = "na" Then
        etc..
Next

This ALSO works for CONTROLS on a form. So you can use

For intC = 1 to 3
    Me("txtBox" & intC) = intC
Next

Which will put the value of intC into the three controls txtBox1, txtBox2 and txtBox3



'ope-that-'elps.






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

 

[Chesterex1]

This is perfect -- I thought I needed the ! character in there - but I don't -- you saved me much aggravation!
Thanks Ken!!!

-Tim
RBC Dominion Securities

 

[Chesterex1]

Thanks LittleSmudge -- just what I needed!!