Hello:
What is the best way to stop a report from running if the query is empty? Currently, I have a form that has a command button on it. The user has to make selections from 3 combo boxes on the form and then via the cmdbutton the report opens in preview mode. I want to stop it from executing if the query is empty. I thought I could do this by checking the record count by the following code but I error out with the following message: -
Too few parameters. Expected <number>. (Error 3061)
-
This parameter query requires the specified number of parameters, which you did not supply. Provide the expected number of parameters, and run the query again.
-
Here is my code: -
Private Sub cmdRptSummary_Click()
-
'Checks to see if any of the controls are null
-
-
If Nz([CboYear], 0) = 0 Or Nz([CboMonth], 0) = 0 _
-
Or Nz([cboWeek], 0) = 0 Then
-
-
If Nz([CboYear], 0) = 0 Then MsgBox "Please select the Forecast Year from the" & _
-
" drop down menu", 64, "Select Forecast Year"
-
If Nz([CboMonth], 0) = 0 Then MsgBox "Please select the Forecast Month from the" & _
-
" drop down menu", 64, "Select Forecast Month"
-
If Nz([cboWeek], 0) = 0 Then MsgBox "Please select the Forecast Week from the" & _
-
" drop down menu", 64, "Select Forecast Week"
-
-
Else
-
-
'If no null values then check to see if there is data to report on
-
-
Dim rst As DAO.Recordset
-
Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
-
-
If rst.BOF And rst.EOF Then 'If none, then end process and send out MsgBox
-
-
MsgBox "Given your selections, there are no records to delete and reload.", 64, "No Records Match"
-
-
Else
-
-
'If there is data, open the Forecast Summary Report
-
-
DoCmd.OpenReport "SumReport", acViewPreview
-
-
End If
-
End If
-
End Sub
-
The error focus is on the following line: -
Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
-
I never set a openrecordset to a query before so I am thinking that that is where my problem is.... I now the query works when I test it without the command button...
Any ideas?
Thanks,
Keith.
4 8979
Hi Keith. The OpenRecordset method generally works fine with queries. I have checked that it works without error on a query returning no records (Query2 in the code below, deliberately set so that the WHERE clause won't return any records), and it works as expected - the recordcount property is 0 indicating no records.
I reckon the parameter error is resulting from the underlying query itself - I have seen such errors occur when a query refers to a form control (in the criteria of the query) which may work fine in the query editor and fail when opened in other contexts. There is a known bug in Access over the use of form field references in queries.
Could you check the underlying query to see whether it refers to a form control in its criteria somewhere?
Cheers
Stewart -
Dim RS As DAO.Recordset
-
Set RS = CurrentDb.OpenRecordset("Query2")
-
If RS.RecordCount = 0 Then
-
MsgBox ("Empty")
-
Else
-
MsgBox ("Not Empty: " & RS.RecordCount)
-
End If
-
RS.Close
-
Stewart:
Thanks for the reply, and yes my query does refer to my combo boxes that are located on my form. Let me try your idea and see what I come up with...
Thanks,
Keith
ADezii 8,834
Recognized Expert Expert
Hello:
What is the best way to stop a report from running if the query is empty? Currently, I have a form that has a command button on it. The user has to make selections from 3 combo boxes on the form and then via the cmdbutton the report opens in preview mode. I want to stop it from executing if the query is empty. I thought I could do this by checking the record count by the following code but I error out with the following message: -
Too few parameters. Expected <number>. (Error 3061)
-
This parameter query requires the specified number of parameters, which you did not supply. Provide the expected number of parameters, and run the query again.
-
Here is my code: -
Private Sub cmdRptSummary_Click()
-
'Checks to see if any of the controls are null
-
-
If Nz([CboYear], 0) = 0 Or Nz([CboMonth], 0) = 0 _
-
Or Nz([cboWeek], 0) = 0 Then
-
-
If Nz([CboYear], 0) = 0 Then MsgBox "Please select the Forecast Year from the" & _
-
" drop down menu", 64, "Select Forecast Year"
-
If Nz([CboMonth], 0) = 0 Then MsgBox "Please select the Forecast Month from the" & _
-
" drop down menu", 64, "Select Forecast Month"
-
If Nz([cboWeek], 0) = 0 Then MsgBox "Please select the Forecast Week from the" & _
-
" drop down menu", 64, "Select Forecast Week"
-
-
Else
-
-
'If no null values then check to see if there is data to report on
-
-
Dim rst As DAO.Recordset
-
Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
-
-
If rst.BOF And rst.EOF Then 'If none, then end process and send out MsgBox
-
-
MsgBox "Given your selections, there are no records to delete and reload.", 64, "No Records Match"
-
-
Else
-
-
'If there is data, open the Forecast Summary Report
-
-
DoCmd.OpenReport "SumReport", acViewPreview
-
-
End If
-
End If
-
End Sub
-
The error focus is on the following line: -
Set rst = CurrentDb.OpenRecordset("QryForecastSummary")
-
I never set a openrecordset to a query before so I am thinking that that is where my problem is.... I now the query works when I test it without the command button...
Any ideas?
Thanks,
Keith.
Keith, there is no need to go through all this unnecessary work, Why not use the Report Event that was specifically designed for this situation? - In your Report's NoData() Event:
-
Private Sub Report_NoData(Cancel As Integer)
-
Cancel = True
-
End Sub
- In the Click() Event of your Command Button:
-
Private Sub cmdReportSummary_Click()
-
On Error GoTo Err_cmdRptSummary
-
Const conCANCEL_REPORT_OPEN As Integer = 2501
-
-
DoCmd.OpenReport "SumReport", acViewPreview
-
-
Exit_cmdRptSummary:
-
Exit Sub
-
-
Err_cmdRptSummary:
-
If Err.Number = 2501 Then 'Report Open was Canceled
-
MsgBox "Report contains no valid data!", vbExclamation, "No Data to Report"
-
Else
-
MsgBox Err.Description, vbExclamation, "Error in Opening Report"
-
End If
-
Resume Exit_cmdRptSummary
-
End Sub
- Let me know how you make out.
ADezii:
Using the Report's NoData Event worked perfectly. Below was my end solution for those inquiring minds: - Private Sub cmdRptSummary_Click()
-
'Checks to see if any of the controls are null
-
-
If Nz([CboYear], 0) = 0 Or Nz([CboMonth], 0) = 0 _
-
Or Nz([cboWeek], 0) = 0 Then
-
-
If Nz([CboYear], 0) = 0 Then MsgBox "Please select the Forecast Year from the" & _
-
" drop down menu", 64, "Select Forecast Year"
-
If Nz([CboMonth], 0) = 0 Then MsgBox "Please select the Forecast Month from the" & _
-
" drop down menu", 64, "Select Forecast Month"
-
If Nz([cboWeek], 0) = 0 Then MsgBox "Please select the Forecast Week from the" & _
-
" drop down menu", 64, "Select Forecast Week"
-
-
Else
-
-
'If there is no data to report on, stop the report from running and display
-
'the error message, otherwise open the report in preview mode.
-
-
On Error GoTo Err_cmdRptSummary
-
Const conCANCEL_REPORT_OPEN As Integer = 2501
-
-
DoCmd.OpenReport "SumReport", acViewPreview
-
-
Exit_cmdRptSummary:
-
Exit Sub
-
-
Err_cmdRptSummary:
-
If Err.Number = 2501 Then 'Report Open was Canceled
-
MsgBox "Given the selections there is no data to report on," & _
-
" please select different parameters and try again.", vbExclamation, "No Data to Report"
-
Else
-
-
MsgBox Err.Description, vbExclamation, "Error in Opening Report"
-
-
End If
-
Resume Exit_cmdRptSummary
-
End If
-
End Sub
I never used that event before. It will sure come in handy !
Thanks to both of you for getting back to me. It is comforting to know I can get some ideas when I feel like I am running out of them. Unfortunately, My 1000 + page Access 2003 book was not doing the trick, though I have benn using it lately to bang my head a few times.... Your solution did the trick.
Take care,
Keith. ;-)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Mal |
last post by:
Greetings.
I have a perplexing problem....please help.
I am having a problem with an insert query. (SQL below)
When I run the query via code (executing the SQL string) it crashes
Access and gives me a Page Fault error in OLEAUT32.dll
When I run the query from the query window it gives the normal "You
are about to run an action query..." then appears to run, yet DOES NOT
|
by: Nathan Bloomfield |
last post by:
Does anyone know if there is any documentation which relates to Access2k + ?
or can anyone help adjust the code?
I am having trouble converting the DAO references.
TITLE :INF: How to Create a Dynamic Crosstab Report
PRODUCT :Microsoft Access
PROD/VER:1.00 1.10
OPER/SYS:WINDOWS
|
by: Robert |
last post by:
I have a report that runs on a parameter query. I run the report, enter the
parameter, and it works as long as there is at least one record returned by
the query. But if it comes up empty, I get an error in the report. I think
there is some way to query the query to see if it's got any records and then
exit the report. How is this done?
Robert
|
by: Intrepid_Yellow |
last post by:
Hi,
I have the following code that runs my report generator. The user
selects a table from a combo box, then whatever fields they want from a
list box. (This part all works and the report runs fine). There is then
a combo box they can select a field from (eg CompanyID etc) and then
the list box below that contains the values (eg Microsoft, Novell etc).
These are all multi-select list boxes. Now I can get the code to work
if the user...
|
by: sara |
last post by:
I am stuck.
I have a report that I use in multiple places, so I call it with
varying parameters (using the Where Clause in the code).
I preview the report, send it to snap, then close the preview (the
user can go to the server to see the snap view). Print, snap, then
close is the only way I can snap with a Where clause.
If there is No data that meets the criteria, I can cancel the print,
| |
by: Phil Stanton |
last post by:
I am running a query that calls a function used to format addresses
depending on the width of a control on a report that shows that address. The
same query is used as the RecordSource of lots of similar reports, but all
with different sized Address text boxes. For the function to work, the
report need to be open in design view, so that the Text Box Width can be
"measured". The function is obviously called for each line of the query
(about...
|
by: creative1 |
last post by:
Here is how you create a complex data report that involves parent and child commands and you can update information at runtime. Its pretty straight forward to work with simple queries; however, working with complex reports is tricky
Assumption: Reader of this article have basic knowledge of creating data reports.
Creating a Parent-Child Command and create a DataReport
Suppose we have a database called company with two tables
...
|
by: jambonjamasb |
last post by:
Hi
I have two tables:
email_tbl
Data_table
Data table is is used to create a Form
Data_form
|
by: pupilstuff |
last post by:
hi guys i wan to make dyanmic crystal report according to values which i checked from check box
thats all i did
1. I made data set having data table name "Customer"
2 i put four columm id,name
,age ,sex
3. then i made crystal report and drag those columm in crystal report
4 thn i drag crystal report viwer
5 thn i write tht code at button click
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |