Select record from recordset via vba.


T

Tim

Hi folks,

Is there a way to get a particular record in recordset? I used the
following loop to select the record but it took a lot of time becuase the
recordset is huge.

Do while not rst.eof
if rst.fields(0).value = 526 then
fABC = rst.fields(1).value
end if
rst.movenext
loop

Is it possible to use SQL to query the recordset? How?

Any help will be appreciated.

Thanks in advance.
Tim.
 
Ad

Advertisements

D

Douglas J. Steele

You could try applying a filter:

rst.Filter = "[" & rst.Fields(0).Name & "] = 526"
Set rstTemp = rst.OpenRecordset
If rstTemp.EOF = False Then
fABC = rstTemp.Fields(1)
End If
rstTemp.Close
Set rstTemp = Nothing
 
J

Jim Burke in Novi

If it's a unique value you're searching for where you know there won't be
duplicates, and if you only need one of the fields from that record, the
simplest thing to do is to use DLookup...

fABC = DLookup("FieldNameBeingRetrieved","tblName","SearchField = " & value)
 
Ad

Advertisements

T

Tim

Hi Douglas,

The code works great but it still took about 30 min to trun all the record
in my query. I used this code in a query which meant the function would be
called every record. I am wondering is there a way to open the recordset
once and return all the value for each record in the query instead of the
function open the recordset and return the value for one record and repeat it
for another record.

Thanks,
Tim.

Douglas J. Steele said:
You could try applying a filter:

rst.Filter = "[" & rst.Fields(0).Name & "] = 526"
Set rstTemp = rst.OpenRecordset
If rstTemp.EOF = False Then
fABC = rstTemp.Fields(1)
End If
rstTemp.Close
Set rstTemp = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tim said:
Hi folks,

Is there a way to get a particular record in recordset? I used the
following loop to select the record but it took a lot of time becuase the
recordset is huge.

Do while not rst.eof
if rst.fields(0).value = 526 then
fABC = rst.fields(1).value
end if
rst.movenext
loop

Is it possible to use SQL to query the recordset? How?

Any help will be appreciated.

Thanks in advance.
Tim.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top