Wrox Programmer Forums
| 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 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 December 9th, 2005, 11:00 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default Check if query returns results

I need to make a function that checks if a query returns any results. I been struggling with this for a while now, here is what I got but I get an error message saying too few parameters expected 2.

Set db = CurrentDb
Set qry = db.OpenQueryDef("qFrameCheckOverPack")
Set rst = qry.OpenRecordset() ' The error is for this line *****
If rst.RecordCount > 0 Then
     'Query has results
Else
     'Query has no results
End If

Thanks for any help
Marcin

 
Old December 9th, 2005, 11:04 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Instead of checking the RecordCount, I always use...

If Not(rs.EOF AND rs.BOF) Then 'Records Returned
...
...

That is using an ADODB recordset, but I would assume it would work in your case.

Mike


Mike
EchoVue.com
 
Old December 9th, 2005, 11:08 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ya but it does not even get that far because I get an error when I set the recordset

 
Old December 9th, 2005, 11:14 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Does the query in question ask for Parameters? IE. Date range, Amount etc.

Mike
EchoVue.com
 
Old December 9th, 2005, 11:20 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

No there is no parameters in the query, it simply displays records of items which are overshipped. As a result I need a way of prompting the user that this order has too many items and this occurs if this query has any results.

 
Old December 9th, 2005, 11:25 AM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

How does the query know what the order is?

Any chance you could post the SQL of the query?
Just go into the query, select the SQL view, and copy it here.

Mike
EchoVue.com
 
Old December 9th, 2005, 11:31 AM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

SELECT qFrameCheckOverPack.FrameTimeStamp, qFrameCheckOverPack.ID, qFrameCheckOverPack.POS_NR, qFrameCheckOverPack.FrameNo, qFrameCheckOverPack.ItemID, qFrameCheckOverPack.QtyGood, qFrameCheckOverPack.MachineCode, qFrameCheckOverPack.IsitOverPack, IIf([MachineCode]="T1","HGP","TAM") AS MachineName
FROM qFrameCheckOverPack
WHERE (((qFrameCheckOverPack.IsitOverPack)="bad"));

the query qFrameCheckOverPack is identical to this one except I have a if statement which checks if goodqty is > origqty and if it is then it IsitOverPack = bad

The queries work fine since I can click on them and they display the correct results

 
Old December 9th, 2005, 12:23 PM
Friend of Wrox
 
Join Date: Oct 2004
Location: Clinton, UT, USA.
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts
Default

Huh?!

I am afraid I am drawing a blank right now. I am also headed into a meeting - Does anyone else have any idea?

If not, I may have a couple of things we can try when I get back in an hour or two.

Mike

Mike
EchoVue.com
 
Old December 9th, 2005, 12:47 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Ok I got pretty far, I got it working what I had to do is set each parameter for my first query. I did by
    Set Par1 = qdf.Parameters![Result]
    Par1 = [Forms]![fFrameSheet]![cboResult]

I was wondering what if one of my parameters in my query was ">70 AND <77"
How would I add that as part of a variable, but it cannot be a string because it says invalid data type

 
Old December 9th, 2005, 12:59 PM
Authorized User
 
Join Date: Jan 2005
Location: , , .
Posts: 51
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Fixed my status parameter issue, I just included it as part of my query. Thanks for the tips





Similar Threads
Thread Thread Starter Forum Replies Last Post
Edit Query Results in Results Grid druid2112 SQL Server 2005 1 June 28th, 2007 08:49 AM
SQL Query returns error Raphasevilla Access VBA 2 February 22nd, 2006 08:30 AM
RecordCount Query returns EOF and BOF jigs_bhavsar Pro VB Databases 3 November 11th, 2004 09:32 AM
access returns results but not when through ASP whyulil Classic ASP Databases 4 June 8th, 2004 11:18 PM
Results Page always returns '0 found' jonrayworth Dreamweaver (all versions) 4 November 9th, 2003 05:48 PM





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