CreateReportControl() won't write to header section

[broon]

I have an Access 2007 report with a custom header section named SSNHeader. The SSNHeader has preset labels, textboxes, and a chart in it. I want to dynamically add labels and textboxes to the SSNHeader depending on input from a form. I'm using the following code to create the first textbox. But the control is always written to the Detail section. The SSNHeader is not recognized for some reason. I need the control to be placed in the SSNHeader section.

DoCmd.OpenReport "Hidden_Paycheckv2", acViewDesign

' *** Dynamically create report controls ***
Dim txt401k As Control
Set txt401k = CreateReportControl("hidden_paycheckv2", acTextBox, SSNHeader, "", ER_401k, 200, 11000, 5000, 300)

[pbaldy]

I haven't used it (don't like to create controls on the fly), but it appears that argument is supposed to be a constant, not your name. An example:

acGroupLevel1Header

Help has a listing.

[broon]

Using the Access constant rather than the custom name did get the textbox to show up in the right place. But the data field I'm binding the textbox to does not show up in the report. The textbox is just empty. Is there a way to bind the textbox to a specific field on the fly?

[pbaldy]

It appears that one of the arguments is meant to set that. Have you tried enclosing the field name in quotes?

[broon]

Enclosing the field name in quotes got the value to show up. Thanks!

I'm having one more issue. I can set the backcolor of the textbox but I cannot set it for the label for some reason. Here's my code:

Dim txtWc, lblWc As Control
Set lblWc = CreateReportControl("hidden_paycheckv2", acLabel, acGroupLevel1Header, , , 500, top, 3300, 240)
With lblWc
.BackColor = RGB(194, 220, 255)
.FontName = "Cambria"
.FontSize = "9"
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
.Caption = "Workers' Compensation"
End With
Set txtWc = CreateReportControl("hidden_paycheckv2", acTextBox, acGroupLevel1Header, "", "Work_Comp", 3840, top, 1620, 240)
With txtWc
.Format = "Currency"
.BackColor = RGB(194, 220, 255)
.FontName = "Cambria"
.FontSize = "9"
.ForeColor = RGB(0, 0, 0)
.BorderColor = RGB(0, 0, 0)
End With
top = top + top_offset

Do you know why the backcolor of the label won't change?

[pbaldy]

My only thought offhand is to make sure the back style is set to Normal, not Transparent.

[broon]

Good call. The BackStyle was defaulting to Transparent.

Do you know if there is a way to hide report controls and collapse the space they would have taken up if their value is zero or null?

[pbaldy]

My first thought would be to play with the Can Grow/Can Shrink properties of the controls, and perhaps the section they're in.

[broon]

I don't think the Can Grow/Shrink properties will work because the report is laid out in two columns. One column contains the data I want to be dynamically displayed depending on user input and on whether the data fields have values. The second column contains a chart. I think the chart will prevent the space from collapsing. The idea is for the whole report to fit on one page per employee. Maybe this is not possible.