I've been pulling my hair out of this issue for a couple days now. I am trying to create a report that will display values from a crosstab query. The crosstab query has a parameter "week". When I select the week, I want only the values to display from that week in the report.
My report currently has 17 unbound text boxes in the report header (txtHeading1 through txtHeading17). I also have 17 unbound text boxes in the report titled (txtColumn1 through txtColumn17).
Below is my code for the report.
When i try to open the report, I get the error "Run-time error '438': Object doesn't support this property or method"
Thanks in advance for your help.
Code:
Option Compare Database
Option Explicit
Private Sub Report_Close()
DoCmd.Restore
End Sub
Private Sub Report_Open(Cancel As Integer)
Const conNumColumns = 17
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset
'Dim qdf As QueryDef
'Dim rst As Recordset
Dim intColumnCount As Integer
Dim intX As Integer
'Don't open report if Weekly Pick form is not open
If Not CurrentProject.AllForms("frmViewPicks").IsLoaded Then
Cancel = True
MsgBox "Please open this report from frmViewPicks.", vbExclamation
Exit Sub
End If
'On Error GoTo Handle_Err
'Set record source
RecordSource = "qryCrossTabPicks"
'Open QueryDef object
Set qdf = CurrentDb.QueryDefs("qryCrosstabPicks")
'Set query parameters based on values in form.
qdf.Parameters("Week") = Forms![frmViewPicks].WeekCombo
'Open Recordset
Set rst = qdf.OpenRecordset
'Don't open report if week is not selected.
If rst.RecordCount = 0 Then
MsgBox "No records found.", vbInformation
Cancel = True
'GoTo Handle_Exit
End If
'Fix number of columns in crosstab query and limit to max available.
intColumnCount = rst.Fields.Count - 1
If intColumnCount >= conNumColumns Then
intColumnCount = conNumColumns - 1
End If
For intX = 1 To intColumnCount
'Set caption of label in page header to field name.
Controls("txtHeading" & intX).Caption = rst(intX).Name
Next intX
'Set control source of first text box in detail section to row header.
Me("txtColumn").ControlSource = "=[" & rst(1).Name & "]"
For intX = 2 To intColumnCount
Me("txtColumn" & intX).ControlSource = rst(intX).Name
Next intX
DoCmd.Maximize
'Handle_Exit:
' On Error Resume Next
' rst.Close
' Set rst = Nothing
' Set qdf = Nothing
' Exit Sub
'Handle_Err:
' MsgBox Err.Description, vbExclamation
' Resume Handle_Exit
End Sub