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