Crosstab Form

[asanteza]

I create forms from crosstab queries. How can I make the fields on the form dynamic (new fields should be added to the form when the crosstab data changes)?

[CJ_London]

To add new controls you would need to go into form design view. But there are other ways - see similar threads links at the bottom of this thread

[ranman256]

dont. just use the query as the result.
no form needed, nor the effort to create all permutations.

[Welshgasman]

Here is a way I did it, quite a while back though.

Private Sub Form_Load()
Dim n As Integer
Dim fld As Variant
  
For Each fld In Me.Recordset.Fields
    Do
        With Me.Controls(n)
            If .ControlType = acTextBox And Left(.Name, 5) = "vName" Then
                .ControlSource = fld.Name
                .Controls(0).Caption = fld.Name
                 n = n + 1
                 Exit Do
              End If
           End With
           n = n + 1
      Loop
 Next
End Sub


Public Sub Form_Open(Cancel As Integer)
Dim strCaller As String, strSql As String, strQuery As String


strCaller = PersonList()


strQuery = "qryWeekCallSummary_Crosstab"


'strSQL = "PARAMETERS pCallSystem Text ( 255 );"
strSql = strSql & " TRANSFORM Sum([SumOfCallSecs]/86400) AS Duration"
strSql = strSql & " SELECT qryWeekCallSummary.WeekEndDate"
strSql = strSql & " FROM qryWeekCallSummary "
If Len(gCallSystem) > 0 Then
    strSql = strSql & " WHERE qryWeekCallSummary.CallSystem = '" & gCallSystem & "'"
End If
strSql = strSql & " GROUP BY qryWeekCallSummary.WeekEndDate"
strSql = strSql & " ORDER BY qryWeekCallSummary.WeekEndDate DESC"
strSql = strSql & " PIVOT qryWeekCallSummary.Caller IN (" & strCaller & ")"


'CurrentDb.QueryDefs(strQuery).Parameters("[pCallSystem]") = gCallSystem
CurrentDb.QueryDefs(strQuery).SQL = strSql
Me.RecordSource = strSql
End Sub

Basically create enough controls for the columns, then populate as needed.

Public Function PersonList() As String
    Dim lngCount  As Long
    Dim strSql    As String
    Dim strPeople As String
    giMaxPeople = 10
    
    
    strSql = " SELECT [CallerName]" & _
             " FROM tblCaller" & _
             " WHERE [Required] = True" & _
             " ORDER BY [CallerName]"
             
    With CurrentDb.OpenRecordset(strSql)
        Do Until .EOF
            lngCount = lngCount + 1
            If lngCount > giMaxPeople Then Exit Do
        
            strPeople = strPeople & Chr(34) & !CallerName & Chr(34) & ","
            .MoveNext
        Loop
    End With
    
    If Right(strPeople, 1) = "," Then
        PersonList = Left(strPeople, Len(strPeople) - 1)
    End If
    
End Function

[June7]

As ranman noted, can just set subform container SourceObject to the query, no code needed.

But why do you want a form with crosstab display? Too bad MS removed pivot table/form from Access.