Recordset access and pin-point record question

[Tom Lee]

Hi Guys,

Firstly thanks for all the help you have providing me...

I have another question I was stuck with.

Here is a simple code

dim strsql as string
dim scr as recordset

strsql = "Select A, B, C from somewhere"
scr = docmd.runsql strsql
after I docmd.runsql strsql, i would like to get the result into a
certain record set call "scr". once I have that, I would like to get
the value of B in the Nth record in "scr". Is it possible? Can someone
please give me hints?

Sincerely,

Tom

[Rick Collard]

Instead of using docmd.runsql you need to open a recordset using your
SQL. Here's some air code using DAO:

dim strsql as string
dim scr as recordset
Dim db As Database, rs As Recordset

strsql = "Select A, B, C from somewhere"
Set db = DBEngine.Workspaces(0).Databases(0)
set rs = db.OpenRecordset(strsql, dbOpenDynaset)

You could use a loop to move through the recordset to get to the Nth
record:

for i = 1 to N
rs.MoveNext
next i

However, there iss usually a better way. You might reconsider your
SQL expression and add a Where clause to get the record of interest.
When your recordset's current record is the record of interest you can
access the value of B using:

rs!B

Hope that helps.

Rick Collard
www.msc-lims.com

On 15 Sep 2003 08:20:09 -0700, ze***@hotmail.com (Tom Lee) wrote:

Hi Guys,

Firstly thanks for all the help you have providing me...

I have another question I was stuck with.

Here is a simple code

dim strsql as string
dim scr as recordset

strsql = "Select A, B, C from somewhere"
scr = docmd.runsql strsql
after I docmd.runsql strsql, i would like to get the result into a
certain record set call "scr". once I have that, I would like to get
the value of B in the Nth record in "scr". Is it possible? Can someone
please give me hints?

Sincerely,

Tom