Variable Number of Fields in Reports

Question

What's the best way to handle a report (tabular) that has variable number of fields (columns) which is based on the output of a query?

Most often I do not know how many fields will be displayed on the report, so what I do is create as many textfields as possible and set their visible property to false. If a column is needed, I just assign it a value and set visible to true. This makes the report not only big but hard to maintain as well...

Answer

From: Shamil Salakhetdinov <shamil@marta.darts.spb.ru>
To: Microsoft Access Database Discussion List <ACCESS-L@PEACH.EASE.LSOFT.COM>
Subject: Re: Variable Number of Fields in Reports
Date: 24 April 1998 5:05

Raymon,

We use similar solution but we keep all controls shifted to the left. Btw, you can generate as many controls as you want using template control by something like this function:

Sub smsCloneRptCtls()
    Dim rpt As Report
    Dim ctlTemplate As TextBox
    Dim ctl As TextBox
    Dim prp As Property
    Dim i As Integer
    
    DoCmd.OpenReport "rptTst", acViewDesign
    
    Set rpt = Reports![rptTst]
    Set ctlTemplate = rpt![txtTemplate]
    
    For i = 0 To 100
        Set ctl = CreateReportControl(rpt.Name, acTextBox, , , , _
                                    ctlTemplate.Left, _
                                    ctlTemplate.Top, _
                                    ctlTemplate.Width, _
                                    ctlTemplate.Height)
      
       For Each prp In ctlTemplate.Properties
          On Error Resume Next
          ctl.Properties(prp.Name) = ctlTemplate.Properties(prp.Name)
       Next
       
       ctl.Visible = False
       ctl.Name = "txt" & i
    Next i
    
End Sub

The same way you can generate corresponding column labels. Then on Report_Open you can shift controls to the right/set their controlsource/make them visible based on report's recordsource:

Private Sub Report_Open(Cancel As Integer
    
    Dim rst As Recordset
    Dim i As Integer
    '
    Set rst = CodeDb().OpenRecordset(Me.RecordSource, dbOpenSnapshot)
    
    For i = 0 To rst.Fields.Count - 1
        Me("txt" & i).ControlSource = rst(i).Name
        Me("txt" & i).Visible = True
        If i <> 0 Then
            Me("txt" & i).Left = Me("txt" & CStr(i - 1)).Left + _
                                 Me("txt" & CStr(i - 1)).Width
        End If
    Next i
End Sub