Updating field in Report Header using VBA

[SaturnSeven]

The following code works fine if the field lblCatalogue is located the Report Footer section of the report. But I want to show it in the Report Header, when I move the field to the Header it doesn't update!

Is there a way to do this? or am I stuck with the Footer?

DoCmd.OpenReport "rpt DemandVolumeByStyle", acViewPreview
Reports("rpt DemandVolumeByStyle").Controls("lblCatalogue").Caption = strCat

 

[THWatson]

SaturnSeven
I assume your code is included in a command button on a form.

What happens if you take the last part out of the command button code, and change the label in the Open event for the report itself?

Something like...

Me.lblCatalogue.Caption = Forms!YourFormName!strCat

Tom

 

[CosmoKramer]

Or,

In the On Format event of the Report Header section.....

Si hoc legere scis, nimis eruditionis habes

 

[SaturnSeven]

Thanks for reply
But strCat doesn't exist on the form, it's a calculation from the following code

Function CurrCat()
rstSQL = "SELECT stuff FROM table WHERE x=y"
Set dbs = CurrentDb
Set rstCurrent = dbs.OpenRecordset(rstSQL)
rstCurrent.MoveLast
CurrentCatEnd = rstCurrent.RecordCount
rstCurrent.MoveFirst

For CurrentCatLoop = 1 To CurrentCatEnd
CurrCat = CurrCat & rstCurrent.Fields(0) & ", "
rstCurrent.MoveNext
Debug.Print CurrCat
Next CurrentCatLoop
CurrCat = left(CurrCat, Len(CurrCat) - 2)
End Function

 

[THWatson]

SaturnSeven
From that explanation, two questions...
1. I don't see strCat in the code, so does strCat = the CurrCat value for each record?

2. Where is the Function CurrCat() being run from? The Open event on the Report?

In any event, I'm not clear why things work if you put lblCatalog in the Report Footer, but not if you put it in the Report Header...which is, obviously, where you would want it. Maybe it's because Jet hasn't yet had the chance to get at the record stuff because nothing has yet been formatted.
But, if you are running the Function from the report, what happens if you run it from the Form, so things have already been prepared by the time the report is opened?

Tom