Using a Variable for a Field Name

[mclpar]

Using Access 2000, what is the correct syntax for using a variable to reference a field name when trying to read/write text in that particular field in a DAO recordset? For example, the code

[ strData = rstSample![My Field] ]

returns the contents of field [My Field] from recordset Sample just fine. However, I have a list box that I use to select the field name. I want to use the selected field name from that list box in the code to read/write text in that particular field. The code below

[ strData = rstSample!Me![lstListBox] ]

doesn't work. I get a message "Run-time error 3265: Item not found in this collection".
I have tried to find information on this in bookstores with no luck at all. All suggestions will be greatly appreciated. Thanks.

 

[Remou]

If you are referring to the name of the field, you can use brackets:
strData = rstSample(Me![lstListBox])

You can also say:
Me(strData)
and
Forms(strFormName)

 

[mclpar]

I tried the following code:

[ strFieldText = rstStudentData(Me![strFieldName]) ]

and now I get the following error message: "Run-time error '2465': Microsoft Access can't find the field "strFieldName" referred to in you expression".
It looks like Access still thinks strFieldName is the literal name of the field, rather than a variable whose value is the field name.

 

[Remou]

Is strFieldName a variable or the name of a control in your form? If it is a variable, you need to skip the exclamation mark (!):
[tt]strFieldText = rstStudentData(Me(strFieldName))[/tt]
assuming that strFieldName contains the name of a valid field in rstStudentData recordset.

If strFieldName is a control, check that it contains the name of a field in your recordset.

 

[mclpar]

Thanks for the reply. strFieldName is a variable name. I did try the code

strFieldText = rstStudentData(Me(strFieldName))

and now the error message says "Run-time error '2465': Microsoft Access can't find the field 'Parole Officer' referred to in you expression". So, at least it is recognizing that it is a variable and does show the selected field name (Parole Officer).
I double-checked my spelling for additional spaces, etc. in my tables and that all looks okay. I then tried eliminating the one blank space in my field name, etc. However, I still get the same error message but with the revised spelling. Still, if I use the code

strFieldText = rstStudentData![Parole Officer]

I do get the correct result, so the field name "Parole Officer" must be present.
I'm totally stumped why it doesn't find the field. Is there some code I can use with message boxes to display the field names in the recordset after it has opened?

 

[Remou]

You need square brackets, because of the space:
[tt]strFieldName = "[" & strFieldName & "]"
strFieldText = rstStudentData(Me(strFieldName))[/tt]

 

[MajP]

You may find this confusing because of VB's use of default methods and properties. What is defaulting in this syntax is the fields collection. So if you write it out it may seem clearer. The chain is: Recordset, the fields collection, then the (field index)
index is either a string name or a integer value
So in code
strData = rsRecordset.fields("fieldName")
or = rsRecordset.fields(index)

using the bang
strData = rsRecordset.fields![fieldname]
Vb allows you to drop the "fields" collection because it is a default.

 

[mclpar]

Thank you both for your help. I was able to get the code to work! Again, thanks!