The easy way is to put cancel = True in the On No Data event of the report. But why replicate code across all reports that way? My app selects reports from a listbox, so I would prefer to check the record count of the query, and if 0, never get to the DoCmd.OpenReport line. Here's the code (case 2 is where I would like to check if the report query has data, and my DCount attempt is not working): -
...
-
-
Dim stDocRS As String
-
-
'store report name in variable
-
stDocName = Me.lstReportName.Column(1)
-
stDocRS = stDocName & "." & RecordSource
-
-
'check print options
-
Select Case Me.grpPrintOptions.Value
-
-
Case 2 'print preview
-
If DCount("*", stDocRS) = 0 Then
-
MsgBox "No records match the current criteria"
-
Exit Sub
-
Else
-
DoCmd.OpenReport stDocName, acViewPreview
-
...
-
The DCount is producing a 3078 error, and the text reads "...Access cannot find the input table or query '[correct report name is shown here].[but here shows one of the underlying tables that feed the query?]'...". This is strange since the report's Record Source is the query.
Thanks for looking,
Tux
11 17106 @tuxalot
I think you may require a little trickery to get the desired results: - Define the desired Report in some manner, in your case a List Box:
- strReportName = "Invoice"
- Open the Report in 'Hidden' Mode:
- DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
- Retrieve the RecordSource for the Report:
- strRecordSource = Reports(strReportName).RecordSource
- Use DCount() to see if any Records exist in the RecordSource, then take the appropriate action:
- If DCount("*", strRecordSource) > 0 Then
-
DoCmd.OpenReport strReportName, acViewPreview
-
Else
-
DoCmd.Close acReport, strReportName, acSaveNo
-
End If
- All together now:
- Dim strReportName As String
-
Dim strRecordSource As String
-
-
strReportName = "Invoice"
-
-
DoCmd.OpenReport strReportName, acViewDesign, , , acHidden
-
-
strRecordSource = Reports(strReportName).RecordSource
-
-
If DCount("*", strRecordSource) > 0 Then
-
DoCmd.OpenReport strReportName, acViewPreview
-
Else
-
DoCmd.Close acReport, strReportName, acSaveNo
-
End If
[/list]
fantastic. Thanks again ADezii.
@tuxalot
You are quite welcome, tuxalot.
NeoPa 32,554
Expert Mod 16PB @tuxalot
Tux, can I try to convince you that, while this is a working method, it is not a good idea.
I hear what you're saying about repeating the code for every report, but consider the effect of testing the data source before running the query every time. This may be negligible for extremely quick reports, but I'm sure you will have reports that take much longer to run (if not currently, then surely you will in future if you stay in the field). What you are considering is effectively to run the queries twice for every time the report is run.
Is this really what you want as your standard approach when running reports? Is the alternative really so annoying? Only you can decide for you, but I would certainly advise setting Cancel to False instead.
Thanks NeoPa for the advice. I understand how this approach could potentially cause issues. As they say there are many ways to skin the proverbial cat. Developers new to Access like me truly rely on folks like you and the other experts on this forum to help steer us in the right direction.
Thanks to you and ADezii for your insight and expertise.
Tux
NeoPa 32,554
Expert Mod 16PB
I see you're also trying to post answers where you can Tux, so you're especially welcome :)
fantastic code, worked like a charm for me. thank you very much!
NeoPa 32,554
Expert Mod 16PB
An important issue to bear in mind, when Cancel is set to True in the OnNoData event procedure, is that the code that calls for the report to open returns an error. This needs to be handled if you don't want the user to see it crash every time.
To be clear, the two bits of code would look something like : - On Error Resume Next
-
Call DoCmd.OpenReport(ReportName:="ReportName", View:=acPreview)
-
On Error GoTo 0
- Private Sub Report_NoData(Cancel As Integer)
-
Dim strMsg As String
-
-
Cancel = True
-
strMsg = "There is no data available for the selected parameters"
-
Call MsgBox(Prompt:=strMsg, _
-
Buttons:=vbExclamation Or vbOKOnly, _
-
TITLE:=Me.Name)
-
End Sub
Notice there is also the possibility of notifying the operator if you choose to. Otherwise, only line #4 is required within the procedure wrapper.
I did not know that! Thanks, NeoPa.
NeoPa 32,554
Expert Mod 16PB
Always pleased to help further understanding Mark :-)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Mike |
last post by:
I have to pass a date from a form to a report query. I have written a
query that works fine when I execute from SQL view, But I dont know
how to pass a value from the form to this query.
SELECT...
|
by: Tom |
last post by:
Hi all:
I've run into a problem that is confounding me. Hope you can help.
I have the following query (having removed alot of stuff for clarity:
SELECT tblComponent.TagNumber,...
|
by: momo |
last post by:
Hello,
How do I pass an sql query to the crystal report?
For example "Select * from products where productid = 215"
how can i build a crystal report in vb.net based on this query which returns...
|
by: z.ghulam |
last post by:
I am designing an order database at work and am having problems
creating a specific report I'm after.
Basically, each order has an 'order type' and what I would like is a
report which lists the...
|
by: Mike Abbott |
last post by:
Hello folks,
I am fairly new to access after using dBase III for the last 10 years.
I have successfully imported dBase tales into access.
I have created a Parameter Query with a start date...
|
by: icony |
last post by:
Hi everyone,
Here's my problem. I have a report that i want to use with the command
openreport. The report open and close in a fraction of a second. I cant
see why i cant view the thing. Cause i...
|
by: anamika |
last post by:
Hi
We all know its very easy to pass a sql query to Crystal Report from
vb.net
However, my requirement is that I want something which is exactly
opposite to this.
My Cyrstal Report has generated...
|
by: mudman04 |
last post by:
Hi,
I searched online for some similar issues that I am facing but was not able come up with anything. I am fairly new with Access (2 months experience) and I am trying to remove a message...
|
by: raddrummer |
last post by:
Hi there,
I'm woking on a function that takes the input from a form (including Payroll Contact), uses it as a query parameter, runs the query, and then emaills out a custom .xls file using the...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM).
In this month's session, the creator of the excellent VBE...
|
by: MeoLessi9 |
last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: Aftab Ahmad |
last post by:
Hello Experts!
I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
|
by: Aftab Ahmad |
last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below.
Dim IE As Object
Set IE =...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
| | |