Prevent report from opening when there is no data

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
lenyon73Asked:
Who is Participating?
 
jjafferrCommented:
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
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
 
zuijdhoekCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
zuijdhoekCommented:
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
 
lenyon73Author Commented:
Jim I tried what you said but it didn't work.

zuijdhoek can you be a little bit more specific?

Thanks guys
0
 
ArjiCommented:
In the On No Data event of your report you can place a macro that has this:

Action

Setwarnings = No
CancelEvent
0
 
lenyon73Author Commented:
The report still opens...
0
 
ArjiCommented:
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
 
jjafferrCommented:
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
 
stevbeCommented:
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
 
ArjiCommented:
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
 
stevbeCommented:
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
 
jjafferrCommented:
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
 
stevbeCommented:
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
 
lenyon73Author Commented:
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
 
stevbeCommented:
"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
 
lenyon73Author Commented:
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
 
stevbeCommented:
do you get the msgbox?
does the On No Data event property say [Event Procedure]  ?

Steve
0
 
lenyon73Author Commented:
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
 
stevbeCommented:
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
 
lenyon73Author Commented:
All I want to do, is prevent a report from opening if there is no data to show.

Thanks
0
 
stevbeCommented:
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
 
lenyon73Author Commented:
That gives me "Error 1004: Unable to set the HasTitle property of the Chart Class"

Thanks
0
 
stevbeCommented:
looks like you need to figure out what to do with that ... maybe take it out for now and see what happens.
0
 
lenyon73Author Commented:
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
 
stevbeCommented:
"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
 
lenyon73Author Commented:
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
 
stevbeCommented:
if Eval names are stored in the database then you could probably link that tablwe to the query the report is based on.
0
 
lenyon73Author Commented:
Eval names are stored in the database.

Can you be a little bit more specific?

Thanks
0
 
stevbeCommented:
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
 
lenyon73Author Commented:
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
 
stevbeCommented:
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
 
jjafferrCommented:
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
 
lenyon73Author Commented:
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
 
lenyon73Author Commented:
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
 
lenyon73Author Commented:
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
 
jjafferrCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.