How do I reference a Recordset using a variable for the name, rather than a literal

[tfurnivall]

I have a situation where the processing for a particular object class is dependent on:
i) Does the class have instance level security, or only class level security
and
ii) The name of the field where the class keeps its instance level security.

Let's explain a little further

Imagine two classes - Widgets and Gadgets.
The Widget Class has a base security level of 0, and the Gadget Class has a base security level of 3. Typically, an operator who has a security level of 0 or above can look at Widgets, but only an operator with a security level of 3 or above can look at Gadgets.

So far, so simple1

Now - each instance of a widget (let's call them RedWidget, BlueWidget and GreenWidget) has a separate security level (this is what I mean by InstanceLevelSecurity). Let's further assume that:
RedWidget has a security level of 1,
BlueWidget has a security level of 2 and
GreenWidget has a security level of 3

This means that operators need a security level o f 1, 2 or 3 to see the Red-, Blue- or Green-Widget objects. This is QUITE APART from the overall class level of 0!

(For a real-world example, assume we are talking about Organizations, Studies and Users. The ability to look at an organization could be level 1, but some organizations - for example LocalSecurityPolice - might have a higher security level. Similarly for other classes)

My problem is that different classes might keep the Instance Level Security in different fields. So I need to be able to gather the name of the security field (for each instance of the class) that contains the InstanceSpecific security level.
For Widgets this could be WidgetSecurityLevel, and for Gadgets it could be GadgetSecurityLevel.

So one of the things that I do is:

SecurityFieldName=Class.SecurityFieldName

and then a little later:

dim BrowseRS as ADODB.Recordset
dim SecurityLevel as long
.....
SecurityLevel=BrowseRS.fields![SecurityFieldName]

I'm running into problems with the fact that Access doesn't recognize the (variable) SecurityFieldName as a valid field name. Indeed - the class does not contain a field called SecurityFieldName - it has a field called WidgetSecurityLevel, or GadgetSecurityLevel, which is exactly the value of SecurityFieldName.

My question is, I guess,

When is it OK to use a variable, that contains the name of a field, to reference that field, or at least to grab its value?

Thanks,

Tony (feeling like he has another-attack-of-sore-head-from-bashing-against-a-brick-wall)

 

[gemma-the-husky]

you have fallen slightly between two stools

either

SecurityLevel=BrowseRS![SecurityFieldName]

or

SecurityLevel=BrowseRS.fields("SecurityFieldName")

so the second of these can use a variable. eg

dim myfield as string

myfield = "SecurityFieldName"
SecurityLevel=BrowseRS.fields(myfield)


good luck with your project!

 

[tfurnivall]

Thanks, Dave,
(Parenthetically, Whitby, our Airedale is currently recovering from knee-surgery, hence a slightly sporadic response to replies!).

SecurityFieldName (in my example) is myfield in yours. So I'm trying to use :

BrowseRS.Fields - pause for enlightenment to strike! - (SecurityField)

{while before I was using BrowseRS.Fields [SecurityField]}

It's amazing to me that with all the research into language definition that was done by Wirth et al during the '60s, that the syntax of VBA is so bloody petty!

I'll try () rather than [] first thing tomorrow morning. I'm now off to take care of a dysphoric dog, due to large amounts of Fentanyl from a pair of patches!

Thanks, Dave

Tony