dynamic reporting, run time error 91 on CreateReportControl method

[Thumbs]

Stumped again. So I'm using a form as a sort of custom report generator. I have textboxes/comboboxes that correspond with each column, and I have a check box for each field that allows the user to select whether or not to include that field in the report. There is a run report button at the bottom, on which I have a sub that generates the SQL string to pass to my dynamic report creation sub. Everything up to the dynamic report sub works fine.



When I run the sub, I am getting a run time error '91': Object variable or With block not set. The error happens on the each CreateReportControl method. I have tried changing the arguments around, and so far the only thing that works is changing ".Name" to the name of the dummy query "qryDummy". This doesnt work on the last CreateReportControl call for creating the page numbering in the footer, only the first two, and I still get the same run time error.

I have included all of my code below, including the sub and sample SQL string that is generated by the On Click sub and passed to the report creation sub. I have marked the problematic code in red.

Any help on this would be much appreciated.

SQL String:

SELECT  AssetData.Hostname, AssetData.Identifier, AssetData.Zone,  AssetData.AssetType, AssetData.AssetStatus, AssetData.Manufacturer,  AssetData.Model FROM AssetData WHERE (((AssetData.Hostname) Like '*'  & Forms!AssetDataReporting!Hostname & '*' Or  Forms!AssetDataReporting!Hostname Is Null) AND ((AssetData.Identifier)  Like '*' & Forms!AssetDataReporting!Identifier & '*' Or  Forms!AssetDataReporting!Identifier Is Null) AND ((AssetData.Zone) Like  '*' & Forms!AssetDataReporting!Zone & '*' Or  Forms!AssetDataReporting!Zone Is Null) AND ((AssetData.AssetType) Like  '*' & Forms!AssetDataReporting!AssetType & '*' Or  Forms!AssetDataReporting!AssetType Is Null) AND ((AssetData.AssetStatus)  Like '*' & Forms!AssetDataReporting!AssetStatus & '*' Or  Forms!AssetDataReporting!AssetStatus Is Null) AND  ((AssetData.Manufacturer) Like '*' &  Forms!AssetDataReporting!Manufacturer & '*' Or  Forms!AssetDataReporting!Manufacturer Is Null) AND ((AssetData.Model)  Like '*' & Forms!AssetDataReporting!Model & '*' Or  Forms!AssetDataReporting!M
odel Is Null));

sub that runs On Click:

Private Sub RunReport_Click()

Dim sSQLSelect As String
Dim sSQLString As String
Dim sSQLWhere As String
Dim CkName As String
Dim CkNameFrom As String
Dim CkNameTo As String
Dim ctl As Control

sSQLString = "SELECT "

For Each ctl In Me.Controls
With ctl
Select Case .ControlType
Case acCheckBox
CkName = Mid(.name, 3)
If .Value = True And .name <> "CkReceivedDate" And .name <>  "CkWtyExpireDate" And .name <> "CkNextPMIDate" Then
sSQLSelect = sSQLSelect & ", AssetData." & CkName & ""
sSQLWhere = sSQLWhere & " AND ((AssetData." & CkName & ")  Like '*' & Forms!AssetDataReporting!" & CkName & " & '*'  Or Forms!AssetDataReporting!" & CkName & " Is Null)"

ElseIf (.name = "CkReceivedDate" Or .name = "CkWtyExpireDate" Or .name = "CkNextPMIDate") And .Value = True Then
sSQLSelect = sSQLSelect & ", AssetData." & CkName & ""
CkNameFrom = CkName & "From"
CkNameTo = CkName & "To"
sSQLWhere = sSQLWhere & " AND (((AssetData." & CkName & ")  >= [Forms]![AssetDataReporting]!" & CkNameFrom & " Or  [Forms]![AssetDataReporting]!" & CkNameFrom & " Is Null) AND  ((AssetData." & CkName & ") <= [Forms]![AssetDataReporting]!"  & CkNameTo & " Or [Forms]![AssetDataReporting]!" & CkNameTo  & " Is Null))"

End If
End Select
End With
Next ctl

sSQLString = sSQLString & Mid(sSQLSelect, 3) & " FROM AssetData WHERE (" & Mid(sSQLWhere, 6) & ");"
Debug.Print sSQLString

CreateAutoReport sSQLString

End Sub

Sub that generates the report:

Public Sub CreateAutoReport(strSQL As String)
Dim rpt As Access.Report
Dim rptReport As Access.Report
Dim strCaption As String
 
     CurrentDb.QueryDefs("qryDummy").SQL = strSQL
 
     ' Open dummy query to invoke NewObjectAutoReport command on it
     ' Put the report created to design view to make properties editable
     With DoCmd
         .OpenQuery "qryDummy", acViewNormal
         .RunCommand acCmdNewObjectAutoReport
         .Close acQuery, "qryDummy"
         .RunCommand acCmdDesignView
     End With
 
     ' Get reference to just created report
     For Each rpt In Reports
         If rpt.Caption = "qryDummy" Then Set rptReport = rpt
     Next
 
     With rptReport
 
         ' Create title control
         With CreateReportControl(.Name, acLabel, _
             acPageHeader, , "Title", 0, 0)
             .FontBold = True
             .FontSize = 12
             .SizeToFit
         End With
 
         ' Create timestamp on footer
         CreateReportControl .Name, acLabel, _
             acPageFooter, , Now(), 0, 0
 
         ' Create page numbering on footer
         With CreateReportControl(.Name, acTextBox, _
             acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", _
             .Width - 1000, 0)
             .SizeToFit
         End With
 
         ' Detach the report from dummy query
         .RecordSource = strSQL
 
         ' Set the report caption to autogenerated unique string
         strCaption = GetUniqueReportName
         If strCaption <> "" Then .Caption = strCaption
 
     End With
 
     DoCmd.RunCommand acCmdPrintPreview
 
     Set rptReport = Nothing
 
End Sub
 
 
Public Function GetUniqueReportName() As String
Dim intCounter As Integer
Dim blnIsUnique As Boolean
 
     For intCounter = 1 To 256
         GetUniqueReportName = "rptAutoReport_" & Format(intCounter, "0000")
         blnIsUnique = True
         For Each rpt In CurrentProject.AllReports
             If rpt.name = GetUniqueReportName Then blnIsUnique = False
         Next
         If blnIsUnique Then Exit Function
     Next
 
     GetUniqueReportName = ""
 
End Function

[Thumbs]

Ok, I solved this. I ended up using a completely different function, written with different logic to build the dynamic report. All is right in the world.