Using a string to reference a field in a recordset definition in VBA

[phudgens]

I'm using the following VBA code in Access 2007 to reference a user selected field. The field that the user selects is placed in the string variable: ThisField. Both the field and it's associated table have been determined to exist. I get the error message: "Item not found in the collection."

If IsNumeric(ThisRSet!ThisField) Then GasVol = ThisRSet!ThisField

Does anyone see a problem in my code? Thanks for any help,
Paul Hudgens
Denver

 

[SkipVought]

hi,

try this

If IsNumeric(ThisRSet(ThisField)) Then GasVol = ThisRSet(ThisField)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

oops

If IsNumeric(ThisRSet("ThisField")) Then GasVol = ThisRSet("ThisField")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[dhookom]

Skip,
If ThisField is a string memory variable then I expect your first answer should work.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If you are wondering why this is, Microsoft makes this confusing by providing almost too much flexibility. They provide two notations, default methods, and two ways to provide an index.

There are two types of notations for collections in VB. Bang and dot. You write them as
Dot
ObjectName.CollectionName(index)
the index can be numeric (1,2,3) or string "fieldName" or a variable

Bang
ObjectName.CollectionName!index
the index can only be the string

only in dot can you represent the index with a variable.

For your example
Object = ThisRSet
Collection = Fields
index = the value in the variable ThisField



Dot:
ThisRSet.Fields(ThisField)
Since the fields collection is the default collection you are not required to show it and can write it simply as
ThisRSet(ThisField)

In bang notation you would write
ThisRSet.Fields!FieldName (Field Name cannot be a variable)
since fields is the default property you can drop it
ThisRSet!FieldName

 

[phudgens]

The following code worked:

If IsNumeric(ThisRSet(ThisField)) Then GasVol = ThisRSet(ThisField)

Thanks guys.

Paul Hudgens

 

[SkipVought]

ahh

string variable: ThisField

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}