Referring to field names

Derek Brown
Derek Brown used Ask the Experts™
on
I have this as my sql with an application that is not Access but uses VBA:

        strSql = "SELECT DH, RDW, DFT, IFL1, CH1, CW1, CD1, BVP1, IFL2, CH2, CW2, CD2, BVP2 FROM qryGetDataForCADCAM"  (Data from an access database not in the applications Project)
        Set rs = dbWrapper.RecordSetOpenEx(strSql, CursorTypeEnum_adOpenDynamic, LockTypeEnum_adLockOptimistic)

But when I reference each field I have to use "rs!" as in Height = rs!DFT+rs!CH1+rs!CH2

Is this peculiar to the setup I am using or is it a requirement of VBA 7.1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Neil FlemingConsultant and developer

Commented:
It's an Access standard convention for field names.
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Nothing you are missing, but you can use "WITH"

  With rs
      Height = !DFT+!CH1+!CH2
  End With

  Saves a bit of typing and is a tad faster.

Jim.
John TsioumprisSoftware & Systems Engineer

Commented:
Old Habits die hard...similar to Jim's
With rs
      Height = .DFT+.CH1+.CH2
  End With

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You can even do this to clearly express, that you use the Value property of the field, not the field object:

Height = rs!DFT.Value + rs!CH1.Value + rs!CH2.Value

Open in new window

Consulting
Distinguished Expert 2017
Commented:
There are various syntaxes available:

The recordse object is a container, wich hold a collection of Field objects, and each Field has a value.
so the complete syntax looks like the Following:
rs.Fields("fieldName").Value
Or
rs.fields(x).Value (with x being the index number of the field).

But the Field object has the Value property set to default property, so you can omit it:
rs.Fields("fieldName") is the same as rs.Fields("fieldName").Value

The recordset object also has the Fields property set to default, so once again you can omit it:
rs("FieldName") is the same as rs.Fields("fieldName") is the same as rs.Fields("fieldName").Value.

Finally, instead of parenthesis and quotes, VBA allow to use an exclamation mark:
rs!FieldName is the same as above.

Whatever syntax you choose is a mater of preferences, just don't mix (for obvious readability reasons).

Author

Commented:
Thank you all. Very helpful!

As I have to use these field references multiple times this seems to work:

Dim CH1 AS String
CH1 = rs!CH1

Before I write all code would this cause a problem elsewhere. My concern is that I am using a field name as a Dim
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
It's not unusual to place the value of a field in a variable and then use it else where, but the only reason you do that is if you plan to close the recordset or modify the value in some way.    If you are not going to do that, then just reference the field itself with rs!CH1.   The variable is just extra overhead and complexity.

Jim.

Author

Commented:
Thanks All

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial