Field Reference Problem

[MikeC]

I'm trying to do the following:

1) Obtain the *name* of a field from one ADO recordset
(rst1).
2) Obtain the *value* of this field from a second ADO
recordset (rst2).
3) Concatenate the field *value* to a string that I am
building inside a loop.

Below is a code fragment:

'===========================================
With rst1
If strLtr <> vbNullString Then
strLtr = strLtr & CStr(rst2!Eval("[" & !FieldName
& "]")) & strLineFeed
Else
strLtr = CStr(rst2!Eval("[" & !FieldName & "]")) &
strLineFeed
End If
End With
'===========================================

When I execute the code, I get the below error:

Error #3265 - "Item cannot be found in the collection
corresponding to the requested name or ordinal."

The error occurs in the *Eval* function. I'm guessing
that VBA is looking for the field in the rst1 recordset.
It doesn't seem to know that I want it to look for the
field in rst2. I've tried approximately 15 different
variations of the above, and I just don't seem to know how
to make VBA understand that I'm referencing a field
contained in rst2.

Does anyone understand how to fix this code?

Any help you can offer would be greatly appreciated.
Thanks.

 

[Dirk Goldgar]

I'm trying to do the following:

1) Obtain the *name* of a field from one ADO recordset
(rst1).
2) Obtain the *value* of this field from a second ADO
recordset (rst2).
3) Concatenate the field *value* to a string that I am
building inside a loop.

Below is a code fragment:

'===========================================
With rst1
If strLtr <> vbNullString Then
strLtr = strLtr & CStr(rst2!Eval("[" & !FieldName
& "]")) & strLineFeed
Else
strLtr = CStr(rst2!Eval("[" & !FieldName & "]")) &
strLineFeed
End If
End With
'===========================================

When I execute the code, I get the below error:

Error #3265 - "Item cannot be found in the collection
corresponding to the requested name or ordinal."

The error occurs in the *Eval* function. I'm guessing
that VBA is looking for the field in the rst1 recordset.
It doesn't seem to know that I want it to look for the
field in rst2. I've tried approximately 15 different
variations of the above, and I just don't seem to know how
to make VBA understand that I'm referencing a field
contained in rst2.

Does anyone understand how to fix this code?

Any help you can offer would be greatly appreciated.
Thanks.

I think you want something like this:

'------ start of revised code snippet ------
With rst1
strLtr = strLtr & rst2.Fields(!FieldName) & strLineFeed
End With

'------ end of revised code snippet ------

I don't actually see the point of your "If strLtr <> vbNullString Then"
logic, as it works just as well doing the concatenation whether strLtr
is currently a null string or not.

 

[MikeC]

Dirk,

Thanks for the tips.

I was sure the rst2.Fields(!FieldName) was going to fix
the problem, but I'm still getting the same error. The
error happens on the line containing the new code.

Any other ideas?

In the meantime, I'm going to do some experimenting...

-----Original Message-----

I'm trying to do the following:

1) Obtain the *name* of a field from one ADO recordset
(rst1).
2) Obtain the *value* of this field from a second ADO
recordset (rst2).
3) Concatenate the field *value* to a string that I am
building inside a loop.

Below is a code fragment:

'===========================================
With rst1
If strLtr <> vbNullString Then
strLtr = strLtr & CStr(rst2!Eval("[" & ! FieldName
& "]")) & strLineFeed
Else
strLtr = CStr(rst2!Eval("[" & !FieldName & "]")) &
strLineFeed
End If
End With
'===========================================

When I execute the code, I get the below error:

Error #3265 - "Item cannot be found in the collection
corresponding to the requested name or ordinal."

The error occurs in the *Eval* function. I'm guessing
that VBA is looking for the field in the rst1 recordset.
It doesn't seem to know that I want it to look for the
field in rst2. I've tried approximately 15 different
variations of the above, and I just don't seem to know how
to make VBA understand that I'm referencing a field
contained in rst2.

Does anyone understand how to fix this code?

Any help you can offer would be greatly appreciated.
Thanks.

I think you want something like this:

'------ start of revised code snippet ------
With rst1
strLtr = strLtr & rst2.Fields(!FieldName) & strLineFeed
End With

'------ end of revised code snippet ------

I don't actually see the point of your "If strLtr <> vbNullString Then"
logic, as it works just as well doing the concatenation whether strLtr
is currently a null string or not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

 

[MikeC]

Dirk,

OK, I think I figured out the final tweak. I
appended ".Value" to the name of the field just to clarify
to VBA that I want to pass the value contained in rst1!
FieldName. Once I made this last change, the error went
away. Here's the updated code fragment:

With rst1
strLtr = strLtr & rst2.Fields(!FieldName.Value) &
strLineFeed
End With

Thanks for your help.

-----Original Message-----
Dirk,

Thanks for the tips.

I was sure the rst2.Fields(!FieldName) was going to fix
the problem, but I'm still getting the same error. The
error happens on the line containing the new code.

Any other ideas?

In the meantime, I'm going to do some experimenting...

-----Original Message-----

I'm trying to do the following:

1) Obtain the *name* of a field from one ADO recordset
(rst1).
2) Obtain the *value* of this field from a second ADO
recordset (rst2).
3) Concatenate the field *value* to a string that I am
building inside a loop.

Below is a code fragment:

'===========================================
With rst1
If strLtr <> vbNullString Then
strLtr = strLtr & CStr(rst2!Eval("[" & ! FieldName
& "]")) & strLineFeed
Else
strLtr = CStr(rst2!Eval("[" & !FieldName & "]")) &
strLineFeed
End If
End With
'===========================================

When I execute the code, I get the below error:

Error #3265 - "Item cannot be found in the collection
corresponding to the requested name or ordinal."

The error occurs in the *Eval* function. I'm guessing
that VBA is looking for the field in the rst1 recordset.
It doesn't seem to know that I want it to look for the
field in rst2. I've tried approximately 15 different
variations of the above, and I just don't seem to know how
to make VBA understand that I'm referencing a field
contained in rst2.

Does anyone understand how to fix this code?

Any help you can offer would be greatly appreciated.
Thanks.

I think you want something like this:

'------ start of revised code snippet ------
With rst1
strLtr = strLtr & rst2.Fields(!FieldName) & strLineFeed
End With

'------ end of revised code snippet ------

I don't actually see the point of your "If strLtr <> vbNullString Then"
logic, as it works just as well doing the concatenation whether strLtr
is currently a null string or not.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)


.

.

 

[Dirk Goldgar]

Dirk,

OK, I think I figured out the final tweak. I
appended ".Value" to the name of the field just to clarify
to VBA that I want to pass the value contained in rst1!
FieldName. Once I made this last change, the error went
away. Here's the updated code fragment:

With rst1
strLtr = strLtr & rst2.Fields(!FieldName.Value) &
strLineFeed
End With

Thanks for your help.

That's interesting. It's my understanding that the Value property is
the default property of an ADO Field object, so I wouldn't have expected
you to have to specify it explicitly. Live and learn.