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 operator can return more than one value. How can I access these values?

Answer 1

Well, to get all the values, you can view both the fields and the records in your recordset. It might look like this:

 '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 will use the metadata getrows and \ or getstring of the record set object, but I don’t remember if it is available with DAO record sets. You can also set a filter for a specific value in a specific field, etc.

Answer 2

I use this function to not worry about NULL values ​​when reading record sets:

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

Never trust the exact value of rec.recordcount , but rec.RecordCount>0 is safe. This is why you should never use a for loop when using a recordset. If you want to know the record number anyway, what you need to do is rec.movelast and then rec.movefirst

There are two different ways that I know:

 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