ADO recordset to variable

[Tim2525]

Hi All,

Trying to load a result from a query to a variable - currRate. When I get to code line "currRate = objRec.GetString(adClipString, ColumnDelimeter:=" ", RowDelimeter:=vbCrLf)", I get the following error.

Run-time error '3021':
Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

The table only has one row in it - a currency rate.

Any help would be most appreciated.

    Dim strSQL As String
    Dim currRate As Currency
    Dim objRec As ADODB.Recordset
    
    strSQL = "SELECT rate FROM qryCurrentRate;"

    Set objRec = New ADODB.Recordset

    objRec.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
    
    currRate = objRec.GetString(adClipString, ColumnDelimeter:=" ", RowDelimeter:=vbCrLf)
    
    MsgBox (currRate)

    objRec.Close
    
    Set objRec = Nothing

TIA,

Tim

 

[stix4t2]

try DLOOKUP( "Rate", "qryCurrentRate")

this will return the rate.

or,

currRate = objRec!Rate

this will return the value in the field.

 

[Tim2525]

Thanks for the reply stix4t2

I'm wanting to learn the ADO part so I tried your suggestion and I'm getting "0" zero return. I did notice my sql statement was completely off. I modified it and still getting zero returned.

Any ideas?

    strSQL = "SELECT rate FROM tblRate;"

    Set objRec = New ADODB.Recordset

    objRec.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly, adCmdText
    
    currRate = objRec!Rate
    
    MsgBox (currRate)

    objRec.Close

TIA,
Tim