How to access values in a recordset

ms-access recordset
Ali picture Ali · May 13, 2011 · Viewed 82.5k times · Source

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

Philippe Grondier picture Philippe Grondier · May 13, 2011

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

Related questions

How to populate a ComboBox with a Recordset using VBA

There is some literature available at expert's exchange and at teck republic about using the combobox.recordset property to populate a combobox in an Access form. These controls are usually populated with a "SELECT *" string in the 'rowsource' properties of …

Read More
Why clone an MS-Access recordset?

I'm a newbie at VBA and attempting to understand someone else's code. Set rstClone = Me.RecordsetClone rstClone.MoveFirst Why does the recordset have to be cloned? Why can't the code be Me.Recordset.MoveFirst?

Read More
insert full ADO Recordset into existing ACCESS table WITHOUT LOOP

I have a filled ADO recordset in my VBA module. I also have a table in ACCESS that has exactly the same structure as the recordset. Now I fill the table using a loop (which is fine) going through each …

Read More