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 |
VBA >