Results 1 to 2 of 2
  1. #1
    ZJGMoparman is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2012
    Posts
    24

    Dynamic CrossTab Report from Query

    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Its a pain to build a dymanic report. (and doesnt always work)

    Instead you could do is, build a table that has every possible heading that can occur. (i hope you dont have hundreds)
    THEN build a report off this table.
    you append the crosstab data to the 'report' table
    run the report.
    (the best solution)

    but, attached is my attempt at a dynamic report. you can modify. Its all in code of the REPORT_OPEN event, so be sure to set:
    .RecordSource = "table"

    DynamicRpt.mdb

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 10-09-2014, 11:37 AM
  2. Dynamic Dates in Crosstab Column Headings
    By air3jxt in forum Queries
    Replies: 5
    Last Post: 03-05-2014, 05:57 PM
  3. Dynamic crosstab report sorting headings & dlookup
    By chrisangk in forum Programming
    Replies: 9
    Last Post: 01-14-2013, 02:37 PM
  4. Replies: 1
    Last Post: 07-30-2010, 10:28 AM
  5. Dynamic Column Headers but NOT CROSSTAB
    By jtkjames in forum Queries
    Replies: 1
    Last Post: 07-26-2010, 05:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums