Changing the Caption of a Label in a Report using Code.

[Curry]

Hi There,

Here is some code that I have written to change the caption on a series of Labels in the Header of a report which needs to be done each month with new data load.

The only way I have been able to get it to work is to list each label Dollar12 to Dollar1 individually as you can see. This is very long and is a pain to change when needed.

Dim db As Database, Header As Date, MthDays As Integer, Mthstr, Mth As Integer
Set db = CurrentDb
'Start month which is entered in a text box in a form
Header = Header_Update_Date
DoCmd.OpenReport "HOSPITAL TITLE DOLLARS", acViewDesign
Mthstr = Format(Header, "mmm yy")
Reports![HOSPITAL TITLE DOLLARS]![Dollars12 Label].Caption = Mthstr
'Returns the number of days in the particular month
MthDays = DaysInMonth(Header)
Header = Header + MthDays
Mthstr = Format(Header, "mmm yy")
Reports![HOSPITAL TITLE DOLLARS]![Dollars11 Label].Caption = Mthstr
MthDays = DaysInMonth(Header)
Header = Header + MthDays
Mthstr = Format(Header, "mmm yy")
Reports![HOSPITAL TITLE DOLLARS]![Dollars10 Label].Caption = Mthstr
MthDays = DaysInMonth(Header)
Header = Header + MthDays
Mthstr = Format(Header, "mmm yy").......and so forth.....


Any ideas...

 

[Rich]

Why not just use text boxes , which by the look of it don't need any code at all?

 

[Fornatian]

If you used text boxes as Rich suggests you could set the source to an expression suchas

=Format(DateAdd("m",x,Me.OpenArgs),"mmm-yy"

where

• x = an integer from 1 -12 dependent on how many months to calculate forward
• Header_Update_Date is passed as the OpenArgs value in the Docmd.OpenReport statement

 

[Curry]

Thanks guys...I will give this a go...

I Curry...