Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 24th, 2005, 08:49 AM
Registered User
 
Join Date: Aug 2005
Location: , , .
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default Recordset empty when opening querydef

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!
Reply With Quote
  #2 (permalink)  
Old October 16th, 2005, 12:52 AM
Friend of Wrox
 
Join Date: Jul 2005
Location: Oklahoma City, OK, USA.
Posts: 150
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #3 (permalink)  
Old November 4th, 2005, 04:59 PM
Authorized User
 
Join Date: Sep 2004
Location: Nanaimo, BC, Canada.
Posts: 37
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
Reply With Quote
  #4 (permalink)  
Old November 14th, 2005, 09:31 AM
Friend of Wrox
 
Join Date: Sep 2003
Location: Salisbury, Wiltshire, United Kingdom.
Posts: 155
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to leehambly
Default

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.

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 06:59 AM.


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