Crosstab report error

[foshizzle]

Im trying to build a cross-tab report with dynamic column headings. I've download various examples and am trying to import the code from a report in an example database into my own report. I've named the textbox controls for all column headings and detail rows to match that of the example.

The report errors out at line below with 'Object doesn't support this property or method' but it works in the example... What am I doing wrong. Do textboxes have captions? I tried setting this to value instead but that didnt work either.
Me("txtHeading" & intX).Caption = rst(intX).Name

Private Sub Report_Open(Cancel As Integer)
  Const conNumColumns = 11
  Dim qdf As DAO.QueryDef
  Dim rst As DAO.Recordset
  Dim intColumnCount As Integer
  Dim intX As Integer

  On Error GoTo Handle_Err

  ' Set record source.
  RecordSource = "qryRAirlineFuelMTD2_Crosstab"
  ' Open QueryDef object.
  Set qdf = CurrentDb.QueryDefs("qryRAirlineFuelMTD2_Crosstab")
  ' Set query parameters based on values in form.
  qdf.Parameters("Forms![frmReportBuildAirlineFuelUseMTD]!txtStartDate") = Forms![frmReportBuildAirlineFuelUseMTD]!txtStartDate
  qdf.Parameters("Forms![frmReportBuildAirlineFuelUseMTD]!txtEndDate") = Forms![frmReportBuildAirlineFuelUseMTD]!txtEndDate

  ' Open recordset.
  Set rst = qdf.OpenRecordset
  ' Don't open report if there are no data.
  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.
    Me("txtHeading" & intX).Caption = rst(intX).Name
  Next intX

  ' Set control source of first text box in detail section to row header.
  Me("txtColumn1").ControlSource = "=[" & rst(1).Name & "]"

  ' Start totals in column 2 (the first column with a crosstab value).
  For intX = 2 To intColumnCount
    ' Set control source of text box in detail section to fraction of column total; replace nulls by 0.
    Me("txtColumn" & intX).ControlSource = "=Nz([" & rst(intX).Name & "],0)/Sum([" & 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

 

[theDBguy]

Hi. When you get the error, go to Debug mode. While the line is highlighted, hover you mouse over the variables to see what is Access seeing. Alternatively, you can use the Immediate Window to examine those values.

 

[foshizzle]

Hi. When you get the error, go to Debug mode. While the line is highlighted, hover you mouse over the variables to see what is Access seeing. Alternatively, you can use the Immediate Window to examine those values.

Thanks for your reply. This is what is highlighted

Me("txtHeading" & intX).Caption = rst(intX).Name

 

[theDBguy]

Thanks for your reply. This is what is highlighted

Me("txtHeading" & intX).Caption = rst(intX).Name

Understood. So, I was saying you could use your mouse or in the Immediate Window, enter something like the following (one at a time) and see what happens.

?intX
?Me("txtHeading" & intX).Caption
?rst(intX).Name

 

[foshizzle]

Understood. So, I was saying you could use your mouse or in the Immediate Window, enter something like the following (one at a time) and see what happens.

?intX
?Me("txtHeading" & intX).Caption
?rst(intX).Name

Ok, i see. Here are the results:

?intX
1
??Me("txtHeading" & intX).Caption
Run-time error '438': Object doesn't support this property or method.

 

[theDBguy]

Ok, i see. Here are the results:

?intX
1
??Me("txtHeading" & intX).Caption
Run-time error '438': Object doesn't support this property or method.

Okay, now I think I get it. You asked earlier if Textboxes have Captions, I don't think they do, but Labels have them. So, either use a Label instead of a Textbox, or remove the Caption part in your code.

 

[foshizzle]

Okay, now I think I get it. You asked earlier if Textboxes have Captions, I don't think they do, but Labels have them. So, either use a Label instead of a Textbox, or remove the Caption part in your code.

Ok.
If I change Caption to Label, I get the same error in the immediate window "Object doesn't support this property or method."
If I remove the Caption altogether and check the immediate window, it says "you entered an expression that has no value.

 

[theDBguy]

Ok.
If I change Caption to Label, I get the same error in the immediate window "Object doesn't support this property or method."
If I remove the Caption altogether and check the immediate window, it says "you entered an expression that has no value.

Okay, two things. I said Textboxes don't have Captions, and they certainly don't have a Label property - I never said that. Instead, what I said was use a Label Control instead of a Textbox, because Label controls have a Caption property.

So, in other words, instead of Me.TextboxName.Caption, I was saying more like Me.LabelName.Caption.

Second, you only showed us earlier two things from your Immediate Window: the value of intX and the line where you're getting an error. You didn't show us the third line I suggested earlier. If you try it now, what do you get? In other words, what does this produce in the Immediate Window?

?rst(intX).Name

 

[foshizzle]

You're right; sorry i skipped over some key words in the comment. I finally got the report to run but none of the detail is right; not sure what it was doing. So I started over, trying the suggestions from this UtterAccess post. I've named my labels and detail controls accordingly. No errors, but the report opens blank. I'm going to include a sample here also.
Use the form 'frmReportBuildAirlineFuelUseMTD' to open the report. I'm testing dates 12/01/20 thru 12/31/20. Thanks

    'Fill in the label captions and control sources.
   
    Dim intColCount As Integer
    Dim intControlCount As Integer
    Dim dbsReport As DAO.Database
    Dim rstReport As DAO.Recordset
    Dim i As Integer
    Dim StrName As String
    Dim qdf As QueryDef
    
    On Error Resume Next
    Set dbsReport = CurrentDb
    Set qdf = dbsReport.QueryDefs(Me.RecordSource)
    Set rstReport = qdf.OpenRecordset()
    intColCount = rstReport.Fields.Count
    intControlCount = Me.Detail.Controls.Count
    
    If intControlCount < intColCount Then
        intColCount = intControlCount
    End If
    
    '  Fill in information for the necessary controls.
    For i = 1 To intColCount
        StrName = rstReport.Fields(i - 1).Name
        Me.Controls("Head" & i).Caption = StrName
        Me.Controls("Col" & i).ControlSource = StrName
    Next i
    '  Hide the extra controls
    '  nothing to hide, already marked in report as invisible...