Record Set

[cuttsy]

Is it possible for me to assign a value from a record set to a variable.

Dim db As Database
    Dim rs As DAO.Recordset
    Dim strSQL As String
    Dim strUser As String

    
    Set db = CurrentDb
    
    strSQL = "SELECT * FROM tblCurrentUser"
    Set rs = db.OpenRecordset(strSQL)

The record set has only one record and I need to do some If statements using a field in the record set... Is this possible?

 

[scottn]

sure. first you have to move to the record you want to look at, and then you have to check the field in which you're interested. like this:

If Not rst.EOF then    'Make sure that you haven't opened an empty recordset
    rst.MoveFirst        'You know you have only one record, so it must be first
    If rst.fields(0) = someValue Then  'The fields collection is indexed from 0.
        'Do stuff in here
    end if
end if

Once you're at the record you want, you check the field (column) you're interested in via the .Fields collection. The indexes are 0-based, so the first column is 0, the second is 1, and so on.

 

[Mile-O]

Dim db As Database

Dim db As DAO.Database


As for your question:

myVariable = rs.Fields("MyField")