How to access values in a recordset

Question

Take for example this code:

sSQL = "select CtyMarket from Market where Country = '" & Country.Value & "'"
Set rec = CurrentDb.OpenRecordset(sSQL)

This statement can return more than one value. How can I access those values?

Answer 1

well, in order to get all the values you could browse both fields and records in your recordset. It could look like that:

'You'll need to declare a new variable
Dim i as long

If rec.EOF and rec.BOF then
Else
    do while not rec.EOF
        for i = 0 to rec.fields.count - 1
            debug.print rec.fields(i).value
        next i
        rec.movenext
    loop
endif

Other ways to get your data would be to use the getrows and\or getstring metyhods of the recordset object, but I do not remember if these are available with DAO recordsets. You could also set a filter for a specific value on a specific field, etc

Answer 2

I use this function to not care about NULL values when reading recordsets:

Public Function toStr(pVar_In As Variant) As String
    On Error Resume Next
    toStr = CStr(pVar_In)
End Function

Never trust the exact amount of rec.recordcount but rec.RecordCount>0 is safe. That's why you should never use a for loop when using a recordset. If you'd like to know the recordcount anyway what you have to do first is rec.movelast and then rec.movefirst

There are two different ways that I know of:

While not rec.eof
    msgbox toStr(rec!CtyMarket)
    rec.moveNext
Wend

or

While not rec.eof
    msgbox toStr(rec.fields("CtyMarket").value)
    rec.moveNext
Wend