Fetch data from the recordset

  • Thread starter Thread starter ket_shah
  • Start date Start date
K

ket_shah

Query Name = Query1
Query1= "select * from employee"

I have created a Recordset of a query that is not part of a form

Dim Db As DAO.Database
Dim Rst AsDAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Query1",dbOpenDynaset)

Is this correct ?

If yes than how can I fetch the records from the record set.

Please advise.

Thanks in advance.
 
Your code looks fine! Use the following to reference the data in the recordset:
Rst!NameOfField1
Rst!NameOfField2
etc

You could also reference the data with:
DLookup("NameOfField1","Query1","[EmployeeID] = 1")
 
ket_shah said:
Query Name = Query1
Query1= "select * from employee"

I have created a Recordset of a query that is not part of a form

Dim Db As DAO.Database
Dim Rst AsDAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Query1",dbOpenDynaset)

Is this correct ?

If yes than how can I fetch the records from the record set.
Click to expand...


Fetching records can be done in many ways, depending on what
you want to do.

To look at all the records in sequence:

Do Until rst.EOF
something = rst!somefield
. . .
rst.MoveNext
Loop

To look for a record with a specific value in some numeric
field:

rst.FindFirst "somefield = " & thevalue
If Not rst.NoMatch Then
' value not in dataset
Else
something = rst!somefield
. . .
End If

To look for a record with a specific string in some text
field:

rst.FindFirst "somefield = """ & thestring & """"
. . .

To look for a record with a specific value in some date
field:

rst.FindFirst "somefield = " _
& Format(thedate,"\#m\/d\/yyyy\#")
. . .

There are a lot of other things you can do with a recordset
object so check Help, especially the .AddNew, .Edit and
..Update methods if you want to add new records or edit the
values in existing records.
 
ket_shah said:
Query Name = Query1
Query1= "select * from employee"

I have created a Recordset of a query that is not part of a form

Dim Db As DAO.Database
Dim Rst AsDAO.Recordset
Set Db = CurrentDb
Set Rst = Db.OpenRecordset("Query1",dbOpenDynaset)

Is this correct ?
Yep!


If yes than how can I fetch the records from the record set.
Click to expand...

Just continue like this:

with rst
if .eof and .bof then
msgbox "no records!"
else
while not .eof
msgbox ![Field1] & ", " & ![Field2]
.movenext
wend
endif
end with
set rst = nothing
set db = nothing

Use your Own query field names instead of Field1 and Field2. Cut & paste the
code directly from this post, because there are some dots in there, that you
might not see. Probably you would want to do something different with the
query values - not just display them in a message box.

HTH,
TC
 
Back
Top