How to reference a recordset field value

[Dooza]

Whats the difference between the following:

rsName.Fields.Item("fieldname").Value

and

rsName("fieldname")

I always use the first, but have just seen some VB using the second.

Dooza

 

[Bob Barrows]

Whats the difference between the following:

rsName.Fields.Item("fieldname").Value

and

rsName("fieldname")

I always use the first, but have just seen some VB using the second.

Dooza

In VB (and vbscript) Objects and Collections have default properties which
are retrieved when no property name is specified. So, let's break it down:
A recordset's default property is its Fields collection.
The Fields collection's default property is Item.
The Item object's default property is Value.
So your two alternatives above yield the same result ... in VB.

Many people recommend being explicit when referencing properties to avoid
unexpected results. For example, these two statements will yield different
results:

val=rsName("fieldname")
Set val = rsName("fieldname")

The first causes val to contain the field's value. The second causes val to
reference the actual Field object.

Incidently, it is more efficient to use an item's zero-based ordinal index
to retrieve it rather than its name. So if "fieldname" was the 4th field in
the collection (index = 3), it is more efficient to use:

rsName.Fields.Item(3).Value
or
rsName(3)

You can make the code more readable by using a constant:
const fieldname = 3
val=rsName(fieldname)

In a loop, you should always use a Field object to retrieve the field's
value:

set fldFieldname = rsName(fieldname)
do until rsName.EOF
val=fldFieldname.Value
'do something with val
rsName.MoveNext
Loop

 

[Anthony Jones]

In VB (and vbscript) Objects and Collections have default properties which
are retrieved when no property name is specified. So, let's break it down:
A recordset's default property is its Fields collection.
The Fields collection's default property is Item.
The Item object's default property is Value.
So your two alternatives above yield the same result ... in VB.

Many people recommend being explicit when referencing properties to avoid
unexpected results. For example, these two statements will yield different
results:

val=rsName("fieldname")
Set val = rsName("fieldname")

What catch people out is when this sort of thing happens:-

Sub DoSomething(SomeParam)
' Do stuff
End Sub

DoSomething rsName("fieldname")

What gets passed to the sub procedure? Its easy to believe the value gets
passed but in fact an object reference to the field does, despite there not
being an explict use of the Set keyword.

I'm quite happy to rely on default properties .Fields.Item but I'm usually
explicit about .Value.

 

[Dooza]

What catch people out is when this sort of thing happens:-

Sub DoSomething(SomeParam)
' Do stuff
End Sub

DoSomething rsName("fieldname")

What gets passed to the sub procedure? Its easy to believe the value
gets passed but in fact an object reference to the field does, despite
there not being an explict use of the Set keyword.

I'm quite happy to rely on default properties .Fields.Item but I'm
usually explicit about .Value.

Thank you both for excellent explanations.

Dooza