asked on

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

Jim Horn

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

zuijdhoek

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!

zuijdhoek

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.

lenyon73

ASKER

Jim I tried what you said but it didn't work.

zuijdhoek can you be a little bit more specific?

Thanks guys

Arji

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

Action

Setwarnings = No
CancelEvent

lenyon73

ASKER

The report still opens...

Arji

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.

jjafferr

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

stevbe

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

Arji

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.

stevbe

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

jjafferr

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

stevbe

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

lenyon73

ASKER

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.

stevbe

"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

lenyon73

ASKER

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

stevbe

do you get the msgbox?
does the On No Data event property say [Event Procedure] ?

Steve

lenyon73

ASKER

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

stevbe

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

lenyon73

ASKER

All I want to do, is prevent a report from opening if there is no data to show.

Thanks

stevbe

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

lenyon73

ASKER

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

Thanks

stevbe

looks like you need to figure out what to do with that ... maybe take it out for now and see what happens.

lenyon73

ASKER

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.

stevbe

"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

lenyon73

ASKER

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

stevbe

if Eval names are stored in the database then you could probably link that tablwe to the query the report is based on.

lenyon73

ASKER

Eval names are stored in the database.

Can you be a little bit more specific?

Thanks

stevbe

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

lenyon73

ASKER

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]));

SOLUTION

stevbe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jjafferr

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

lenyon73

ASKER

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

lenyon73

ASKER

I guess I got that error because my report was open in design view.

Anyway, even without that error the empty report opens.

Thanks

ASKER CERTIFIED SOLUTION

jjafferr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lenyon73

ASKER

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!

jjafferr

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