Select record from recordset via vba.

[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.

 

[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

 

[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)

 

[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.

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)

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.