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