Show me how to throw a ADODB.recordset field into a variable

[WorkrAnt]

I want to find out the value of a single field in a sql stmt and put it into a variable for later use. I dont want to add, or update the table.

For instance....

Private Sub cmdCustomer_Click()

'Recordsets
Dim cn As ADODB.Connection, _
rcd0 As New ADODB.Recordset, _
fldDealerNo As ADODB.Field

Dim g as String
Dim strSQL3 As String


strSQL3 = "SELECT max(S_CUSTNO) as newnum FROM CUSTOMER"


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''OPEN CONNECTIONS ''''''''''''''''''''''''''''''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Set cn = CurrentProject.Connection 'working with a table in my open access database

rcd0.Open "CUSTOMER", cn, adOpenStatic, adLockReadOnly, adCmdTable

cn.Execute strSQL

Set fldDealerNo = rcd0.Fields 'here is where i get lost

g = fldDealerNo

Debug.Print "This is G'''''''''''''''''"
Debug.Print g
Debug.Print "this is G'''''''''''''''''"

'Close the connections
rcd0.Close

End Sub()

 

[Golom]

You don't need a field object. You can reference fields in a recordset using

g = rcd0.Fields("FieldName").Value

or

g = rcd0.Fields(OrdinalPosition).Value

or

g = rcd0![FieldName]

 

[WorkrAnt]

Thank you very much!!!! Since I was trying to get the max value of a field, I had to change a few things, but it worked.


strSQL3 = "SELECT max(S_CUSTNO) as newnum FROM CUSTOMER"

rcd0.Open strSQL3, cn2, adOpenStatic, adLockReadOnly, adCmdText

cn.Execute strSQL3


g = rcd04.Fields("newnum").Value