Access cross-tab queries are nice, but it is a little tricky to link the cross-tab query to an Access form. Here's how.
Step 1:
This step dynamically changes the cross-tab query to filter the cross-tab query using the record you are on in a form.
The revised query must be secretly executed behind the scenes to dynamically change the stored field names.
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "frmFeesPivotNew"
Dim strsql As String
strsql = "TRANSFORM Sum(qryFeesPivot_Step1.Amount) AS SumOfAmount SELECT qryFeesPivot_Step1.Feetype " & _
" FROM qryFeesPivot_Step1 WHERE (((qryFeesPivot_Step1.LoanID) = " & Me.LoanID & "))" & _
" GROUP BY qryFeesPivot_Step1.sortorder, qryFeesPivot_Step1.Feetype ORDER BY qryFeesPivot_Step1.sortorder " & _
" PIVOT [DocType] & ' ' & [docdate] & ' ' & [docVersion];"
Dim dbsCurrent As Database
Dim qryTest As QueryDef
Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("qryFeesPivot_Step2")
qryTest.sql = strsql
DoCmd.OpenForm stDocName, acNormal
Forms!frmFeesPivotNew.RecordSource = strsql
Step 2:
The cross-tab will be in an Access continuous form. Put this code in the "on load" event of that form.
This dynamically changes the field names and captions based on your cross-tab query.
Set rst = Me.Form.RecordsetClone
For i = 0 To rst.Fields.Count - 1
If i < 11 Then
Me("Text" & i + 1).ControlSource = rst.Fields(i).Name
Me("Label" & i + 1).Caption = rst.Fields(i).Name
Me("Text" & i + 1).ColumnHidden = False
Me("Text" & i + 1).ColumnWidth = 1500
End If
Next
For i = i + 1 To 10
Me("Text" & i).ColumnHidden = True
Me("Label" & i).Visible = False
Next
Set rst = Nothing