Create an Access form tied to a cross-tab query

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