Dynamic fields by hiding unused in query

[Scottish_Anatomist]

Hey Everyone,

I'm trying to create a form that displays a crosstab query by putting in the maximum number of columns that could be returned as text boxes and I'm trying to create some VBA that will hide the unused ones.

I originally tried to use a report based on some code that was posted as an example, however, although it worked when print previewing the report, I couldn't get it to work on the report view using the onPaint event instead of onDormat event. As someone suggested using a form instead to use as a subForm, I altered the code to try this

Option Compare Database
Option Explicit

' constant of number of maximum possible columns in report
Const conTotalColumns = 13

'Variable declarations
Dim dbsFormReport As Database
Dim rstFormReport As Recordset
Dim intColumnCount As Integer
Dim lngRgColumnTotal(1 To conTotalColumns) As Long
Dim lngReportTotal As Long

Private Sub Form_Load()
Dim intH As Integer
Dim intD As Integer
Dim qdf As QueryDef
Dim frm As Form

'set database
Set dbsFormReport = CurrentDb
'Set form
Set frm = [Forms]![frm_ReportingMainMenu]
'set querydef object
Set qdf = dbsFormReport.QueryDefs(Me.RecordSource)

qdf.Parameters("[Forms]![frm_ReportingMainMenu]![NavigationSubform].[Form]![opt_Grouping]") = frm![NavigationSubform].Form![opt_Grouping]
'MsgBox "grouping is" & frm![NavigationSubform].Form![opt_Grouping]
qdf.Parameters("[Forms]![frm_ReportingMainMenu]![NavigationSubform].[Form]![cbo_PGTYearInt]") = frm![NavigationSubform].Form![cbo_PGTYearInt]
qdf.Parameters("[Forms]![frm_ReportingMainMenu]![NavigationSubform].[Form]![lst_PGTSchool]") = frm![NavigationSubform].Form![lst_PGTSchool]

'Set recordset
Set rstFormReport = qdf.OpenRecordset()

intColumnCount = rstFormReport.Fields.Count
MsgBox "debug " & intColumnCount

'move to start of recordset
rstFormReport.MoveFirst

'Initialise variables
InitVars

MsgBox rstFormReport(1).Name
'****************
'***FormHeader***
'****************

'column headings into text boxes
For intH = 1 To intColumnCount
    Me("Header" + intH) = rstFormReport(intH - 1).Name
Next intH

'Get average column
Me("Header" + (intColumnCount + 1)) = "Average Fee"

'hide all other unused columns
For intH = (intColumnCount + 2) To conTotalColumns
    Me("Header" + intH).Visible = False
Next intH

'****************
'***FormDetail***
'****************

'MsgBox "I'M HERE!"
If Not rstFormReport.EOF Then
    For intD = 1 To intColumnCount
        Me("Column" + intD) = rstFormReport(intD - 1)
    Next intD

    For intD = (intColumnCount + 2) To conTotalColumns
        Me("Column" + intD).Visible = False
    Next intD

    rstFormReport.MoveNext
    
End If

End Sub

Private Sub InitVars()
Dim intX As Integer

lngReportTotal = 0

'initialise array
For intX = 1 To conTotalColumns
    lngRgColumnTotal(intX) = 0
Next intX
End Sub

However I keep getting errors, including a type mismatch that I can't figure out at:

Me("Header" + intH) = rstFormReport(intH - 1).Name

Any help would be gratefully received.

[I'm using Access 2010]

 

[Ranman256]

THATS why it's best to display a Crosstab as a data sheet, NOT a form.

 

[sneuberg]

If you want to concatenate intH on to the end of "Header" try

"Header" & intH

Apparently "+" doesn't mean concatenate in this context.

 

[Scottish_Anatomist]

Thanks sneuberg, that fixed that problem! Although unfortunately still can't get the whole thing to work lol.

Yeah Ranman256, that was my first instinct as well, however then I ran into problems sorting the column headings into the order I needed them.

 

[CJ_London]

have you tried creating an unbound report, add a subform/report control then put in the subform sourceobject

Query.mycrosstabquerynamehere