Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old August 24th, 2005, 08:49 AM
Registered User
Join Date: Aug 2005
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Recordset empty when opening querydef


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

Old October 16th, 2005, 12:52 AM
Friend of Wrox
Join Date: Jul 2005
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts

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

"Hi Tech Coach"
Access Based Accounting/Business Solutions developer.
Old November 4th, 2005, 04:59 PM
Authorized User
Join Date: Sep 2004
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts

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
Old November 14th, 2005, 09:31 AM
Friend of Wrox
Join Date: Sep 2003
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly

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.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Recordset Empty Check Coby Access VBA 2 April 27th, 2007 04:09 PM
Empty recordset = redirect frankriedel PHP How-To 2 November 12th, 2005 04:49 AM
QueryDef and/or Recordset? bhunter Access 3 February 18th, 2004 04:52 PM
Opening A RecordSet Onyx2ir Access VBA 6 December 31st, 2003 12:46 AM
Trap for empty recordset in a SP Mitch Access VBA 2 September 5th, 2003 04:47 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.