[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Prevent report from opening when there is no data

Posted on 2005-04-15
37
Medium Priority
?
336 Views
Last Modified: 2008-01-09
Hi,

I have a report that opens through a form (user enters parameters on text boxes and clicks on command button).

I want to prevent the report from opening, if there is no data for the parameters specified. I added a message box that pops up if there are no records but the report still opens.

I used "Cancel=True" inside the NoData() Event of the report but it didn't work.

Here is my code for the command button:

Private Sub cmdPercentageByEvaluator_Click()
On Error GoTo Err_cmdPercentageByEvaluator_Click

    Dim stDocName As String

    If IsNull(Me.txtStartDate.Value) Or IsNull(Me.txtEndDate.Value) Or IsNull(Me.txtEvalFirstName.Value) Or IsNull(Me.txtEvalLastName.Value) Then
        MsgBox "All fields are required"
    Else
        stDocName = "rptPercentageByEvaluator"
        DoCmd.OpenReport stDocName, acPreview
    End If
   
Exit_cmdPercentageByEvaluator_Click:
    Exit Sub

Err_cmdPercentageByEvaluator_Click:
    MsgBox Err.Description
    Resume Exit_cmdPercentageByEvaluator_Click
   
End Sub

And here is my code for the report:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo Err_cmdMainMenu_Click

chtPercentageByEvaluator.hasTitle = True
Me!chtPercentageByEvaluator.ChartTitle.Text = "Evaluator's Name: " & Forms!frmParameters.txtEvalFirstName.Value & " " & Forms!frmParameters.txtEvalLastName.Value

With chtPercentageByEvaluator.ChartTitle.Font
    .Size = 10
    .Bold = True
End With

Err_cmdMainMenu_Click:
    MsgBox "No report available for selected criteria"

End Sub

Any suggestions?

Thanks
0
Comment
Question by:lenyon73
  • 14
  • 12
  • 5
  • +3
37 Comments
 
LVL 66

Expert Comment

by:Jim Horn
ID: 13793689
Try Docmd.Close acReport, Me.name instead of cancel = True

Also, turn off error handling in your form when you call the report, so you don't get a 'You cancelled the Report Open' message, like this...

On Error Resume Next
DoCmd.OpenReport stDocName, acPreview
On Error GoTo Err_cmdPercentageByEvaluator_Click

Hope this helps.
-Jim
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 13793741
The check within the commandbutton event seems ok to me.
You'd better use the OnNoData event of your report object in case there are no records to be shown. For example:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "No data available"
    Cancel = True
End Sub


Good luck!
0
 
LVL 4

Expert Comment

by:zuijdhoek
ID: 13793933
Sorry, I didn't read your question properly. You used the OnNoData event already. So that means your report's recordsource has some data available. If I understand you well, you don't want to open the report at all in case there are no data to be shown. That menas that you have to check this in advance. You can test this before opening the report by querying exactly the same datasource. In case the resultset is not BOF or EOF you can open the report.  
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:lenyon73
ID: 13794558
Jim I tried what you said but it didn't work.

zuijdhoek can you be a little bit more specific?

Thanks guys
0
 
LVL 17

Expert Comment

by:Arji
ID: 13795610
In the On No Data event of your report you can place a macro that has this:

Action

Setwarnings = No
CancelEvent
0
 

Author Comment

by:lenyon73
ID: 13795709
The report still opens...
0
 
LVL 17

Expert Comment

by:Arji
ID: 13795955
This works for every report I have done - The setwarnings = no suppresses the system 'Cancel' message and the cancelevent stops the report from opening.  Does the On No Data event fire?  You can put a Stop statement in the event procedure to find out if it fires.
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13796540
Lets look at it from a different point,
Use can use DCOUNT to count the number of Records exist based on you criteria, and if the DCOUNT value is Zero, then don't open the Report, otherwise open it.

This way you will not need to use NoData() in the Report either.

jaffer
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13800507
I use the No_Data event all the time and it works fine for me.
What version of Access are you using?

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "The report has no data.", vbOKOnly + vbInformation
    Cancel = True
End Sub

Steve
0
 
LVL 17

Expert Comment

by:Arji
ID: 13801313
Steve,
I've been using my little macro since A97 and even now with A2k3.  Is there a difference from version to version?  I'd be curious to know that.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13806349
Lenyon,
What version of Access are you using?

Jaffer,
Your way pulls data 2 times if there is data, why not do it 1 time and use No Data?

Arji,
If it works for you ... great, I don't think MS changed how the No Data event works, I have been using it for quite some time without any *extra* code.


Steve
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13806388
Steve

Do you mean to say:
Once for the DCount, and the other one when the Report opens?

Does DCount Pull the Data, or does it only checks?

jaffer
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13806741
The D*** functions are not magic, think of them as running uncompiled SQL statements, you PC still needs to do all the same work ... pull indexes, do table scans, whatever is needed to process the SQL. Of course you may be able to write a *faster* SQL to use the DCount against but you are still hitting the data 2 times whenever there ARE records and my guess is that most of the time you will have records making the extra checking a waste of local CPU cycles and creates unnecessary network traffic.


Steve
0
 

Author Comment

by:lenyon73
ID: 13808322
Thanks for your responses.

I couldnt make it to work. I am using Access 2003 but my database says Access 2000. Here is a link to my database if someone wants to take a look at it:

http://s18.yousendit.com/d.aspx?id=3HZ5RQ2HKXWNX3B3R8OLFP6BAC

Thanks.
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13808393
"I am using Access 2003 but my database says Access 2000"

upgrade the databases to 2003 format ...

Tools --> Database Utilities --> Convert Database --> To Access 2002-2003 Format.

Steve
0
 

Author Comment

by:lenyon73
ID: 13808486
I did that and tried:

Private Sub Report_NoData(Cancel As Integer)
    MsgBox "The report has no data.", vbOKOnly + vbInformation
    Cancel = True
End Sub


But it still doesnt work...

Thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13808536
do you get the msgbox?
does the On No Data event property say [Event Procedure]  ?

Steve
0
 

Author Comment

by:lenyon73
ID: 13808605
I dont get the msgbox and yes it says Event Procedure.

I get the following msgbox(No report available for selected criteria):


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

On Error GoTo Err_cmdMainMenu_Click

chtPercentageByEvaluator.hasTitle = True
Me!chtPercentageByEvaluator.ChartTitle.Text = "Evaluator's Name: " & Forms!frmParameters.txtEvalFirstName.Value & " " & Forms!frmParameters.txtEvalLastName.Value

With chtPercentageByEvaluator.ChartTitle.Font
    .Size = 10
    .Bold = True
End With

Err_cmdMainMenu_Click:
    MsgBox "No report available for selected criteria"

End Sub


I also tried to take the Err_cmdMainMenu_Click out but it still didnt work.

Thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13808716
You would get that message on any and all errors, it may or may not have anything to do woth there being data. You would get the error if the form that has the criteria is not open.

Steve
0
 

Author Comment

by:lenyon73
ID: 13809303
All I want to do, is prevent a report from opening if there is no data to show.

Thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13809378
that is not what your existing code is doing. It is giving you bad info.

change the error message box so we can see what is really going wrong ...

MsgBox Err.Number & ": " & Err.Description

Steve
0
 

Author Comment

by:lenyon73
ID: 13809509
That gives me "Error 1004: Unable to set the HasTitle property of the Chart Class"

Thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13809667
looks like you need to figure out what to do with that ... maybe take it out for now and see what happens.
0
 

Author Comment

by:lenyon73
ID: 13809694
That error message comes because the title of my chart is supposed to be created dynamically.

Me!chtPercentageByEvaluator.ChartTitle.Text = "Evaluator's Name: " & Forms!frmParameters.txtEvalFirstName.Value & " " & Forms!frmParameters.txtEvalLastName.Value

When there is no record it cannot find a name to display.

0
 
LVL 39

Expert Comment

by:stevbe
ID: 13814345
"Evaluator's Name: " & Forms!frmParameters.txtEvalFirstName.Value & " " & Forms!frmParameters.txtEvalLastName.Value

This is pulling the "Title" from your parameters form. Are these *required* fields?
Do you want to not let them open the report if they do not enter this information?

Can you pull the Title info from the report itself?

Steev
0
 

Author Comment

by:lenyon73
ID: 13816943
Those are required fields. If I dont enter any data there the reports won't open. I also have a date field though, so if I enter the name and a wrong date then the report will open empty.

I don't think I can pull the title from the report itself. Is there a way to do that since my parameters are coming from a different form?

Thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13817124
if Eval names are stored in the database then you could probably link that tablwe to the query the report is based on.
0
 

Author Comment

by:lenyon73
ID: 13817184
Eval names are stored in the database.

Can you be a little bit more specific?

Thanks
0
 
LVL 39

Expert Comment

by:stevbe
ID: 13817241
add the table that has the Eval names to the query that the report is bound to and link it to the related field in one of the other tables in that query, this will stop data from being returned if there isn't any and the NoData should work fine.

Steve
0
 

Author Comment

by:lenyon73
ID: 13817897
I am getting confused...
I already have two tables in the query(tblIndividual and tblQLSR). Here is my query (if that makes any sense):

SELECT tblQLSR.EvaluatorFName, tblQLSR.EvaluatorLName, (Year([SnapshotDate])) AS [Year], Avg(tblQLSR.Individuality) AS Individuality, Avg(tblQLSR.Integration) AS Integration, Avg(tblQLSR.Relationships) AS Relationships, Avg(tblQLSR.[Security/Stability]) AS [Security/Stability], Avg(tblQLSR.[Choice/Preference]) AS [Choice/Preference], Avg(tblQLSR.Opportunity) AS Opportunity, Avg(tblQLSR.[Dignity/Status]) AS [Dignity/Status], Avg(tblQLSR.[Self-Advocacy]) AS [Self-Advocacy], Avg(tblQLSR.Independence) AS Independence, Avg(tblQLSR.Joy) AS Joy, Avg(tblQLSR.[Staff Impression]) AS [Staff Impression]
FROM tblIndividual INNER JOIN tblQLSR ON tblIndividual.IndividualID = tblQLSR.IndividualID
GROUP BY tblQLSR.EvaluatorFName, tblQLSR.EvaluatorLName, (Year([SnapshotDate]))
HAVING (((tblQLSR.EvaluatorFName)=[Forms]![frmParameters].[txtEvalFirstName].[value]) AND ((tblQLSR.EvaluatorLName)=[Forms]![frmParameters].[txtEvalLastName].[value]) AND (((Year([SnapshotDate]))) Between [Forms]![frmParameters].[txtStartDate].[value] And [Forms]![frmParameters].[txtEndDate].[value]));
0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 1000 total points
ID: 13818539
now reading over your original question I see what we need to do ...

build criteria from the parameters form and pass that in the WhereCondition argumant of DoCmd.OpenReport.

    Dim strWhere As String

    If IsNull(Me.txtStartDate.Value) Or IsNull(Me.txtEndDate.Value) Or IsNull(Me.txtEvalFirstName.Value) Or IsNull(Me.txtEvalLastName.Value) Then
        MsgBox "All fields are required"
    Else
        strWhere = "[SnapshotDate] >=# & Me.txtStartDate.Value & "# AND "
        strWhere = "[SnapshotDate] <=# & Me.txtEndDate.Value & "# AND "
        strWhere = "[EvalFirstName] <=' & Me.txtEvalFirstName.Value & "' AND "
        strWhere = "[EvalLastName] <=' & Me.txtEvalLastName.Value & "'"
        DoCmd.OpenReport ReportName:="rptPercentageByEvaluator", View:=acPreview, WhereCOndition:=strWhere
    End If
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13818621
Steve,
your last part should read:

        strWhere = "[SnapshotDate] >=# & Me.txtStartDate.Value & "# AND "
        strWhere = strWhere & " [SnapshotDate] <=# & Me.txtEndDate.Value & "# AND "
        strWhere = strWhere & " [EvalFirstName] <=' & Me.txtEvalFirstName.Value & "' AND "
        strWhere = strWhere & " [EvalLastName] <=' & Me.txtEvalLastName.Value & "'"
        DoCmd.OpenReport ReportName:="rptPercentageByEvaluator", View:=acPreview, WhereCOndition:=strWhere

Or Am I wrong?

jaffer
0
 

Author Comment

by:lenyon73
ID: 13818806
Steve i added your code (with jafferr's modification) and nothing changed. Then i added:

Private Sub Report_NoData(Cancel As Integer)

MsgBox "The report has no data.", vbOKOnly + vbInformation
    Cancel = True

End Sub


and i got the following error:

"The action or method is invalid because the form or report isn't bound to a table or query"

Thanks
0
 

Author Comment

by:lenyon73
ID: 13818850
I guess I got that error because my report was open in design view.

Anyway, even without that error the empty report opens.

Thanks
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 1000 total points
ID: 13819102
Sorry Steve, but I will ask lenyon73 to try this:

lenyon73:
Change your Command button code to this:

Private Sub cmdPercentageByEvaluator_Click()
On Error GoTo Err_cmdPercentageByEvaluator_Click

    Dim stDocName As String

    If IsNull(Me.txtStartDate.Value) Or IsNull(Me.txtEndDate.Value) Or IsNull(Me.txtEvalFirstName.Value) Or IsNull(Me.txtEvalLastName.Value) Then
        MsgBox "All fields are required"
    Else
   
        If DCount("*", "qryPercentageByEvaluator") = 0 Then     '<== It will Count the Records of your Query, if No Records, then it will not run the Report
            MsgBox "No report available for selected criteria"

        Else
            stDocName = "rptPercentageByEvaluator"
            DoCmd.OpenReport stDocName, acPreview
        End If
    End If
   
Exit_cmdPercentageByEvaluator_Click:
    Exit Sub

Err_cmdPercentageByEvaluator_Click:
    MsgBox Err.Description
    Resume Exit_cmdPercentageByEvaluator_Click
   
End Sub



This should take care of it based on my 1st comment.

jaffer
0
 

Author Comment

by:lenyon73
ID: 13819670
That works Jaffer! Thanks a lot.

I gave half points to you and half points to Steve because he really tried to help me.

I appreciate your help guys.

Thanks!
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 13819711
I think the NoData() doesn't work on your Report because you use charts only which are OLE Objects based on Microsoft Graph Chart,
however, if you had any type of normal controls like a textbox or combobox or listbox, then it would have worked.

Thanks for the points and the grade,
And thanks Steve for teaching me something new.

jaffer
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question