How to format the detail of a dynamically created subreport

[kmacon]

I have a form that contains a command button. The command button's
OnClick event builds a report using the CreateReport and
CreateReportControl functions and then opens the main report. I set
the OnFormat property of the detail section in the subreport to a
function which is located in a standard module. I am not able to
access the subreport nor it's controls in the module. I need to hide a
text box in the subreport if there is no information in any of the rows
in the detail of the subreport. Any help is appreciated. I have
posted the code below.

Form Code:

Private Sub cmdPreviewReport_Click()

' Create the subreport
' --------------------

Set rs = CurrentDb.OpenRecordset("tblSGPrices")

Set rpt = CreateReport(CurrentDb.Name)
DoCmd.RunCommand acCmdReportHdrFtr
intColumnCount = rs.Fields.Count

' Create Header section labels and Detail section text boxes
' ----------------------------------------------------------

For i = 0 To intColumnCount - 1

' Create the detail section text boxes and set controlsource
' ----------------------------------------------------------
Set tbox = CreateReportControl(rpt.Name, acTextBox, acDetail)
tbox.Name = "col" + Format(i)
rpt.Controls("col" + Format(i)).ControlSource =
rs.Fields(i).Name

' Create the Header section labels
' --------------------------------

Set lbl = CreateReportControl(rpt.Name, acLabel, acHeader)
lbl.Name = "header" + Format(i)
Next
rpt.Section(acDetail).OnFormat = "=FormatDetailSG()"
End Sub
Standard Module:

Function FormatDetailSG()
Dim i As Integer
Dim rpt As Report
Set rpt = Reports("Report1") 'THIS IS WHERE I GET THE FOLLOWING
ERROR:

(ERROR 2451 - The report name 'Report1' you entered is misspelled or
refers to a report that isn't open or doesn't exist)

For i = 0 To rpt.Section(acDetail).Controls.Count - 1
If Reports(SGToFormat)("col" + Format(i)) <> "" Then
Reports(SGToFormat)("col" + Format(i)).Visible = False
Else
Reports(SGToFormat)("col" + Format(i)).Visible = True
End If
Next i
End Function

[MacDermott]

Quite aside from the fact that I'm perplexed as to quite why you'd be
wanting to do all this programmatically anyhow, I don't see any code
creating or opening a report named Report1, so it doesn't seem too odd that
Access can't find it.
Is there more you're not telling us?

<km****@stcroixknits.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

I have a form that contains a command button. The command button's
OnClick event builds a report using the CreateReport and
CreateReportControl functions and then opens the main report. I set
the OnFormat property of the detail section in the subreport to a
function which is located in a standard module. I am not able to
access the subreport nor it's controls in the module. I need to hide a
text box in the subreport if there is no information in any of the rows
in the detail of the subreport. Any help is appreciated. I have
posted the code below.

Form Code:

Private Sub cmdPreviewReport_Click()

' Create the subreport
' --------------------

Set rs = CurrentDb.OpenRecordset("tblSGPrices")

Set rpt = CreateReport(CurrentDb.Name)
DoCmd.RunCommand acCmdReportHdrFtr
intColumnCount = rs.Fields.Count

' Create Header section labels and Detail section text boxes
' ----------------------------------------------------------

For i = 0 To intColumnCount - 1

' Create the detail section text boxes and set controlsource
' ----------------------------------------------------------
Set tbox = CreateReportControl(rpt.Name, acTextBox, acDetail)
tbox.Name = "col" + Format(i)
rpt.Controls("col" + Format(i)).ControlSource =
rs.Fields(i).Name

' Create the Header section labels
' --------------------------------

Set lbl = CreateReportControl(rpt.Name, acLabel, acHeader)
lbl.Name = "header" + Format(i)
Next
rpt.Section(acDetail).OnFormat = "=FormatDetailSG()"
End Sub
Standard Module:

Function FormatDetailSG()
Dim i As Integer
Dim rpt As Report
Set rpt = Reports("Report1") 'THIS IS WHERE I GET THE FOLLOWING
ERROR:

(ERROR 2451 - The report name 'Report1' you entered is misspelled or
refers to a report that isn't open or doesn't exist)

For i = 0 To rpt.Section(acDetail).Controls.Count - 1
If Reports(SGToFormat)("col" + Format(i)) <> "" Then
Reports(SGToFormat)("col" + Format(i)).Visible = False
Else
Reports(SGToFormat)("col" + Format(i)).Visible = True
End If
Next i
End Function

[Ken]

The reason I am doing this in code is because I am using a crosstab
query to create the data for the subreport and therefore the number of
columns are dynamic. So I have to create the text boxes in code to get
the number necessary to hold the data. When I create the subreport
using the CreateReport function, the default name of the report is
Report1. The data I am creating in the crosstab query is in this form:

Style Color Small Medium Large X-Large XX-Large 3XL
1234 Red A A A B A
1234 Black A A
4567 Black A A A
8910 White A A A

The letters (A,B) under the Size columns represent a price code and
that the Style/Color is available in that Size. Each Style/Color may
not be available in all Sizes, so I want to hide the column (Size) if
there is nothing to display. So, I need to do this in the
Detail_Format event of the subreport. Because I am creating this
subreport in code, I am assigning a function to the OnFormat property.
The only place I know that the program can see the function is in a
module, however when I try to access the subreport or it's controls in
the function it comes up with the error listed above. I am assuming
that the subreport is out of scope in the module but I don't know how
else I would accomplish this.

[Ken]

I have found the answer to my problem. I used the following to access
the controls on the subreport:

Reports("mainreport").Section(acDetail).Controls(" report1").Controls("col1")

This gives me access to a text box named "col1".