How to display a message in place of a chart!

[deehutchins]

Good afternoon everyone,

I am currently using access 2010 and I have been wrecking my brain :banghead: to figure out how to display message in place of my charts when there is no data. Currently, whenever the chart has no data to display it just shows a white blank space. I would like to replace that with a message. It would be greatly appreciate if some could give me a step by step break down of how to do this and if possible an easy solution. Short VBA is fine, but please add where to place the code.

Thanks

 

[vbaInet]

I don't use charts in Access but I'm guessing the data is being pulled from the Record Source of the form?

 

[JHB]

Place a label control with some text over the graph, make it invisible.
Use a recordset to determine if the graph contains data to show!

Place the code in the form's OnCurrent event or if it is in a report, then in the report's OnLoad event.

Private Sub Form_Current()
  Dim dbs As DAO.Database, rst As DAO.Recordset
  
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(Me.YourGraphName.RowSource)
  If rst.EOF Then
    Me.NoGraphDataLabel.Visible = True
  Else
    Me.NoGraphDataLabel.Visible = False
  End If
End Sub

 

[deehutchins]

Thanks for the post, but I think I did something wrong because it did not work. Please guide me. Here is the name of the Graph: Rpt_Clinical and the row source that it is pulling from is ELECT DISTINCTROW Case_review.[Which Departments].Value, Count(Case_review.ID) AS CountOfID FROM Case_review WHERE (((Case_review.[Type of Issue].Value)="Clinical")) GROUP BY Case_review.[Which Departments].Value;

Would I make the graph the control for the label?

Please help!

Thanks,

 

[vbaInet]

If you upload a sample db (nothing confidential) I'm sure we can fix it.

 

[deehutchins]

The report is called CQM_Case_Review_Summary_Report, I am wanting to replace both clinical and non-clinical graph whenever there is no data with a message.

I really do appreciate the help.

Thanks,

 

[vbaInet]

By the way you didn't need to post the same question in the Crystal Reports forum. That thread was dated 2008 and it's not related to Access reports.

I'll take a look.

 

[deehutchins]

Lol, okay thank you. I was just trying to get as many answer as possible in a short period of time.

 

[vbaInet]

Don't we all

See attached and notice the label behind the non-clinical subreport. Ensure that the following properties of each subreport are set:

Filter On Empty Master: Yes
Can Shrink: Yes
Can Grow: Yes

... and leave the label's border to be transparent.

 

[deehutchins]

Okay so the only question I have left is how do I set the rowsource for the chart so that the label will show if the chart has no data?

Thanks for the setup example.

 

[vbaInet]

I didn't even look at that because I thought you were using the native chart form in Access. You know that charts can be built using one of the views of a form instead of a graph control right?

I'll have another look.

 

[vbaInet]

If you want to keep things simple you can set the Record Source of the subreport to that of the graph as well. But that means the query will be run twice, one for the subreport and another for the graph.

 

[vbaInet]

Attached! This time I've focused on Clinical Issues.

You will notice that Clinical Issues works in Report View and Print Preview whereas the Non Clinical Issues we did earlier only works in Print Preview.

Note the following:
1. qryCIssuesRpt - a new report that is the record source of the graph object
2. The query is used in the report's Open event to toggle the visibility of the subform
3. The label for the subreport is now detached. It will become invisible if it's attached to the subreport when it goes invisible.