Cant open recordset from a query

[randika100100]

Hi all!
I'm trying to open a recordset from a query using following code.

Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim param As DAO.Parameter
Set qdf = db.QueryDefs("FilteredClientQueryForAddFamily")

For Each param In qdf.Parameters
param.value = Eval(param.Name)
Next param

Set rst = qdf.OpenRecordset

It doesn't work. There are no errors and it doesnt fill the recordset either.I might have done something stupid and I apologize for asking this sort of stupid questions.Can someone plz help me. Thanks in advance

 

[pbaldy]

Is db ever declared and set?

 

[randika100100]

Is db ever declared and set?

Thank you very much for your response. Sorry it was my fault that I haven't put set db part here. This is the code. I get only one record in the recordset after running this.But the query has more than one records .

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim param As DAO.Parameter


Set db = CurrentDb
Set qdf = db.QueryDefs("FilteredClientQueryForAddFamily")

For Each param In qdf.Parameters
param.value = Eval(param.Name)
Next param

Set rst = qdf.OpenRecordset

any help would be greatly appreciated.

 

[pbaldy]

How do you know you only get one record (I assume you still haven't posted all the code)? If you're using RecordCount, you have to move last first to guarantee an accurate number.

 

[randika100100]

How do you know you only get one record (I assume you still haven't posted all the code)? If you're using RecordCount, you have to move last first to guarantee an accurate number.

Thanks again for replying. I have put a msgbox rst.RecordCount just after Set rst = qdf.OpenRecordset(dbOpenDynaset)
and it returns 1 then I checked rst in the locals window and had a look at the fields and found details of the first record.In addition record count shows as one in local window too.

To check this i have created a new database and a table and a query. Still the same results. This is the full code of the new database test.

Private Sub Command0_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
Dim param As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("testQuery")

For Each param In qdf.Parameters
param.Value = Eval(param.Name)

Next param


Set rst = qdf.OpenRecordset(dbOpenDynaset)

MsgBox rst.RecordCount



End Sub

This query doesn't even have parameters as I wanted to make it simple. Still it returns only one record to the record set . I even tried this as advised in many places.

Private Sub Command0_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset


Set db = CurrentDb



Set rst = db.OpenRecordset("testQuery",dbOpenDynaset)

MsgBox rst.RecordCount



End Sub

Result is the same. I'm totally confused and frustrated

It works marvelously when I try to open the table directly with

Set rst = db.OpenRecordset("test",dbOpenTable)

im using Access 2007 . I really appreciate your help. Thanks in advance.

 

[pbaldy]

I probably wasn't clear:

http://www.baldyweb.com/RecordCounts.htm

 

[randika100100]

Thank you very much . You saved me. I dont know if i can ever pay you back ( as im pretty new to VBA ) . But thanks a lot.

 

[pbaldy]

Happpy to help!