Dynamic Chart Title in Report

[Kirsti]

Hi,



I have a chart in a report based on data for the previous month - Month(Now())-1 in the query.

The chart title doesn't seem to allow for any code. At the moment I have named it: 'Number of Applications - Previous Month', but I would like some help with some VBA to code the title, so that the month actually shows in the report title. i.e. If I run the report today, I would like it to say: 'Number of Applications - February', and then when I run it in April I'd like it to say: 'Number of Applications - March'.

Is this possible, and is anyone able to help me with this?

Thanks,
Kirsti

[June7]

Need VBA. Use the Format event of whatever section of report the graph is in, I am guessing the Detail section.

Review this http://social.msdn.microsoft.com/For...-a91c7cd0b5fc/

Try:
Me.yourgraphname.ChartTitle = "Number of Applications - " & Month(Now())-1

[Kirsti]

Ok, I'm having trouble.

You are right, my graph is in the Detail section. I have:

Private Sub Detail_Format()
Me.Graph4.ChartTitle = "Test"
End Sub

But I am getting an error:
"The expression On Format you entered as the event property setting produced the following error:Procedure declaration does not match description of event or procedure having the same name."

[Kirsti]

Got it working with:

Private Sub Detail_Format (Cancel As Integer, FormatCount As Integer)
Me.Graph4.ChartTitle.Text = "Test"
End sub

Now just have to have a play and test the Month(Now()-1) part!

[June7]

If you want month name instead of number:

Format(DateAdd("m",-1,Now()),"mmmm")

[Kirsti]

Ok,

Now working perfectly!!


Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.Graph4.ChartTitle.Text = "Number of Applications - " & Choose(Month(Now()) - 1, "January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December")
End Sub

Thanks for getting me started June.

[Kirsti]

Oooh, perfect.

I posted my reply before seeing yours, but that's much better without the hard coding, and also works perfectly.

Thanks again.

[Kirsti]

One more question - If there is no data for the previous month, the report is throwing an error, which makes sense, but would you be able to suggest some kind of error handling code?

[June7]

Throwing what kind of error? If there is no data is there reason to run report?

[Kirsti]

I put dummy data in the table yesterday to fix, and now I can't seem to replicate the error, I think I'll just worry about it later if I see the error again. And you're right, in theory there is no reason to run the report if there's no data, but I have set up a button on a form to e-mail all reports to management, and they can hit that button whenever they like, so it is possible there will sometimes be reports with no data created.

Thanks again for all of your help with this June.

[June7]

Can use OnNoData event of form to handle when no data.