Refer to Field Name dynamically

[Brad]

Thanks for taking the time to read my question.

I am using a do loop to move through my records. As I find matches, I return
text which is equal to a field name. How do I use that text to tell VBA which
field I want to refer to.

Here is what I'm trying:
If InStr(1, rst!PkgCodes, rst2!TestRequired, vbTextCompare)
Then
With rst2
.Edit
!Fields(rst!Analyte) = True 'This is where it fails
as there is no such thing as Fields
.Update
End With
End If

Any ideas on how I do this? I've done it on a form with Controls

Thanks,

Brad

 

[Jim Burke in Novi]

I believe you refer to a recordset field like this: rst.fields("fieldname"),
so if the field name is in a variable it would be rst.fields(varName).

 

[Jim Burke in Novi]

If you want to set the value pretty sure it would be
rst.fields(varName).value = yourValue.

 

[Brad]

Thanks Jim,

I did try this as well. No luck. Any idea why it may not work?

If InStr(1, rst!PkgCodes, rst2!TestRequired, vbTextCompare)
Then
Debug.Print rst2!Fields(rst!Analyte).Name
With rst2
.Edit
!Fields(rst!Analyte).Name = True
.Update
End With
End If

My recordset is updatable. At least if I open the query, the query allows me
to change values, and it does not say "Recordset Not Updatable" at the bottom
of the query when you make changes.


Dim dbs As dao.Database
Dim rst As dao.Recordset
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("tbl_AnalyteList")
Set rst2 = dbs.OpenRecordset("qryTestRequiredPDAEntriesZero")

Brad

 

[Douglas J. Steele]

Try:

..Fields(rst!Analyte) = True

Fields is a Collection of the Recordset object, so you need to use ., not !

That assumes that the field Analyte in rst contains the name of a field in
rst2.

 

[Brad]

Ah, so when referring dynamically, use a period and when referring statically
use an exclamation mark.

Got it.

Thanks so much for your help.

Brad

 

[Douglas J. Steele]

When you refer to a built-in property of an object (which includes built-in
collections), you should use ., and when you refer to something you've
added, you must use !.

In your case, you're trying to use the built-in Fields collection, so you
need to use .