EVAL not working with Recordset variable

[murrah]

Hi,

I am trying to use Eval with an adodb recordset variable in MS Access
2003.

I am getting the error "can't find the name 'rs' you entered in the
expression".

This way works:
x = rs!CardRecordID

This way doesnt:
y = Eval("rs!" & "CardRecordID")

I am using eval because in the real function the fieldname is passed in
as a parameter:

y = Eval("rs!" & fldname)

I know I can loop through the recordset.fields collection until I find
the one I want but I am curious why the eval wont work.

The full code is:

Private Sub Command1_Click()
Dim myDSN As String
Dim rs As ADODB.Recordset
Dim x As Variant
Dim y As Variant

myDSN = "NCT"
Set myobCon = New ADODB.Connection
myobCon.CursorLocation = adUseClient
myobCon.Open myDSN
x = Null

Set rs = myobCon.Execute("SELECT CardRecordID From Cards")

rs.MoveLast

x = rs!CardRecordID ' This works
y = Eval("rs!" & "CardRecordID") ' This doesnt

rs.Close
myobCon.Close
Set myobCon = Nothing
End Sub

 

[sebt]

Hi

Eval doesn't recognise any variable names, because it doesn't run in
the context from which it was called. It's not really designed for use
with VBA variables, more for accessing values on controls on open
forms.

If you need to parameterise the field name, this will work, avoiding
the need for an explicit loop through the Fields collection:

y=rs.Fields.Item(fldname)

or, since the Fields collection is the default collection and the Item
method is the default method, just

rs!(fldname)

should work too.

cheers


Seb