Here is a way I did it, quite a while back though.
Code:
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.
Code:
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