Hi,

When I try to open a querydef in a recordset, the recordset it empty.

I'm currently using Access 2002 in an XP environment and have Microsoft DAO 3.6 Object Library checked in my References.

This is the meat of the code I'm using:

dim qdf as DAO.Querydef
dim rec as DAO.Recordset

set qdf = Currentdb.Querydefs("MyQueryName")
set rec = qdf.Openrecordset()

'Check to see if records exist
rec.MoveLast
rec.MoveFirst
lngRecCount = rec.RecordCount
---------------------------------

At this point, lngRecCount equals 0.

This code is located in a module and is executed through a click of a button on a form where the user can select some parameters for the base query to use.

Some points about the query I'm trying to open. It was created through Access (not VBA) and is based on other queries (created through Access), which are pulling parameters from a Form. These parameters are referenced using the "[Forms]![FormName]![FormControl] format. I can open "MyQueryName" if I go through Access, and it displays all the records, but when I try to use the code above, it opens up blank.

I would GREATLY appreciate any help in getting my query to open with it's records.

Thanks!

You use QueryDef to create/edit a Query Definition, not execute a query (open a recordset).

This is how you open a recordset

dim rec as DAO.Recordset
dim lngRecCount as Long

set rec = Currentdb.Openrecordset("MyQueryName")

'Check to see if records exist
If rec.RecordCount < 1 then ' then are records
  ' no records
  do somthing here
end if


or to use you methed use:

rec.MoveLast ' this updates the record count
lngRecCount = rec.RecordCount












Boyd
"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
http://www.officeprogramming.com

Hello,
Access Master, In actuality, the code:
dim qdf as DAO.Querydef
dim rec as DAO.Recordset

set qdf = Currentdb.Querydefs("MyQueryName")
set rec = qdf.Openrecordset()

To open a Recordset from a QueryDef is perfectly correct.
Its more advanced, and the point of it is usually to specify Parameters from the Query, and then open it up with qdf.Openrecordset().
You have to be a little more knowledgeable about what kind of Recorset you are getting.
-------------------------
You don't need to use MoveLast to find out if there are ANY records in a DAO recordset.
This usually is only to populate a DAO Dynaset to find out the TOTAL record count, and then do something with that like a loop.

I understand what you are doing (i.e. Parameterized Queries).

I've done it about 100 times, to use a Parameterized Query where the Parameter is coming from a Form. Although, I must admit, I have't done it for Sub-Queries.
I'm not sure if this can be done because its been a while since I looked at the app which contains these Form References.
My gut is that I think your Forms are just not open when you run the query, and therefore, what I think Access does is use Null for the paramater values, in which case you will usually END UP WITH WHAT YOU HAVE:
**********A ZERO COUNT RECORD************.



Database Agreements

Can I suggest you take the SQL from your query def and put it into a query so you can debug it. If it is not returning any records and has no errors elsewhere, then I suggest your SQL is bugged or not quite right somewhere.

A side note to the above poster... doing a movelast and movefirst prior to a recordcount is fairly standard. DAO recordcounts are bugged more often than not in my experience and doing this helps to ensure the recordcount is correct.