Problem defining a variable when reading a table in VBA

[Keith]

Hello,

I have a problem using VBA code to read an Access table. Below is an
explanation of the question and some code that shows the problem. The
following code opens a table and reads each record in the field defined as
“Year†. We know it is the field defined as “Year†because the operational
line includes the name of the field being referenced..

TestVariable = rst!Year

It is hard-coded to the field “Yearâ€

I need to read a file, where the field to be read is defined in code.

Instead of using “Year†I need to use MyVariable, where MyVariable = “Yearâ€
or maybe “Month†etc. It is impossible to code all the possibilities
because the code could encounter any number of user-defined fields.

So I need to change the line of code to work from

TestVariable = rst!Year

To

TestVariable = ? <-- what would go here use a variable instead of
“rst!Yearâ€

What would I do?

Thanks

Keith

Following is example VBA code.

Dim Selected_table as variant
Selected_Table = “my_table_name_hereâ€
Dim TestVariable as variant
Dim MyVariable as variant

MyVariable = “Yearâ€

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(Selected_Table)


count = 0

Do Until rst.EOF
On Error Resume Next
rst.MoveNext
TestVariable = rst!Year ‘ Here the Field to be printed is “Yearâ€
‘TestVariable = rst!MyVariable ‘ Here I need to have MyVariable be
anything on the list of variables in the table. How do I Do that?
count = count + 1
If count > 5 Then Exit Do
Loop


MsgBox "count of items in table = " & count & " Year Value >" &
TestVariable & "<"


rst.Close

 

[George Nicholson]

MyVariable = "Year"
TestVariable = rst.Fields(MyVaraible)


Standard Disclaimer: Per Microsoft product documentation, it is a VERY bad
idea to use Reserved Words (like Year, Month) for field names.

 

[Keith]

Hi George,
thanks very much for the help and advice.
keith