Dynamic Reports

Take a look at the two procedures below.  Each performs a similar function to what you ask.  Based upon user selections on the form the procedures generate SQL statements to filter the subforms.  You could use something very similar to generate the SQL statement for the underlying query for your report.

OM Gang



Private Sub cmdBoardExecute_Click()
'modifies the subform recordsource SQL based upon users selected criteria
On Error GoTo Err_cmdBoardExecute_Click

    Dim strSQL As String
   
        'starting SQL for subform recordsource
    strSQL = "SELECT tblBoard.boardID, tblBoard.bname, tblBoard.type_ID, TypeCode.typename, tblBoard.status_ID, " _
            & "StatusCode.status, tblBoard.year_established, tblBoard.term_length, tblBoard.term_limit, " _
            & "tblBoard.dept_ID, DeptCode.dept, tblBoard.hist, tblBoard.maximummembers, tblBoard.law, " _
            & "tblBoard.confirmation, tblBoard.oath, tblBoard.conduct_disclosure, tblBoard.ethics_disclosure, " _
            & "tblBoard.until_successor, tblBoard.chairpersontype_ID, ChairpersonTypeCode.chairpersontype, " _
            & "tblBoard.compensation, tblBoard.bnotes, tblBoard.staff_contact, tblBoard.URL FROM (((tblBoard " _
            & "INNER JOIN TypeCode ON tblBoard.type_ID=TypeCode.type_ID) INNER JOIN StatusCode ON " _
            & "tblBoard.status_ID=StatusCode.status_ID) INNER JOIN DeptCode ON tblBoard.dept_ID=DeptCode.dept_ID) " _
            & "INNER JOIN ChairpersonTypeCode ON tblBoard.chairpersontype_ID=ChairpersonTypeCode.chairpersontype_ID " _
            & "WHERE (((tblBoard.bname) Like " & Chr(34) & "*" & Chr(34) & ")"
           
    If Not IsNull(Me.cboBoard) Then
        strSQL = strSQL & " AND ((tblBoard.boardID)=" & Me.cboBoard & ")"
    End If
           
    If Not IsNull(Me.cboBoardType) Then
        strSQL = strSQL & " AND ((tblBoard.type_ID)=" & Me.cboBoardType & ")"
    End If
   
    If Not IsNull(Me.cboBoardStatus) Then
        strSQL = strSQL & " AND ((tblBoard.status_ID)=" & Me.cboBoardStatus & ")"
    End If
       
    If Not IsNull(Me.cboBoardDept) Then
        strSQL = strSQL & " AND ((tblBoard.dept_ID)=" & Me.cboBoardDept & ")"
    End If
   
   
        'add closing right parantheses for where clause
    strSQL = strSQL & ")"
   
        'update subform recordsource with new SQL statement
    Me.sfrmBoardExport.Form.RecordSource = strSQL
        'requery the subform to show changes
    Me.sfrmBoardExport.Requery

Exit_cmdBoardExecute_Click:
    Exit Sub

Err_cmdBoardExecute_Click:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub cmdBoardExecute_Click of VBA Document Form_frmDataExport"
    Resume Exit_cmdBoardExecute_Click

End Sub

Private Sub cmdBoardMemberExecute_Click()
'modifies the subform recordsource SQL based upon users selected criteria
On Error GoTo Err_cmdBoardMemberExecute_Click

    Dim strSQL As String, strOrderBy As String, strQualCriteria
    Dim varItem As Variant
   
        'starting SQL for subform recordsource
    strSQL = "SELECT tblPosition.boardID, tblBoard.bname, tblPosition.posnumber, tblPosition.member_id, tblMember.last_name, " _
            & "tblMember.first_name, tblMember.address_1, tblMember.address_2, tblMember.CityCode, CityCode.CityName, " _
            & "tblMember.CountyCode, CountyCode.CountyName, CityStateZipXRef.StateCode, CityStateZipXRef.ZipCode, tblMember.home_phone, " _
            & "tblMember.work_phone, tblPosition.term_number, tblPosition.start_date, tblPosition.end_date, tblPosition.[1qualificationID], " _
            & "QualificationCode.qualification AS Qual1, tblPosition.[2qualificationID], QualificationCode_1.qualification AS Qual2, " _
            & "tblPosition.[3qualificationID], QualificationCode_2.qualification AS Qual3 " _
            & "FROM ((((QualificationCode AS QualificationCode_2 INNER JOIN (QualificationCode AS QualificationCode_1 INNER JOIN " _
            & "(QualificationCode INNER JOIN (tblPosition INNER JOIN tblMember ON tblPosition.member_id = tblMember.member_id) " _
            & "ON QualificationCode.qualificationID = tblPosition.[1qualificationID]) ON QualificationCode_1.qualificationID = " _
            & "tblPosition.[2qualificationID]) ON QualificationCode_2.qualificationID = tblPosition.[3qualificationID]) INNER JOIN " _
            & "CityStateZipXRef ON tblMember.XrefId = CityStateZipXRef.XrefId) INNER JOIN CityCode ON CityStateZipXRef.CityCode = " _
            & "CityCode.CityCode) INNER JOIN CountyCode ON CityStateZipXRef.CountyCode = CountyCode.CountyCode) INNER JOIN tblBoard ON " _
            & "tblPosition.boardID = tblBoard.boardID WHERE (((tblBoard.bname) Like " & Chr(34) & "*" & Chr(34) & ")"
   
    If Not IsNull(Me.cboBoardMemberBoard) Then
        strSQL = strSQL & " AND ((tblPosition.boardID)=" & Me.cboBoardMemberBoard & ")"
    End If
   
    If Me.lstQualifications.ItemsSelected.Count <> 0 Then
            'iterate through selected items in form listbox control, retrieve value for each and add to our SQL statement where clause
        For Each varItem In Me.lstQualifications.ItemsSelected
            strQualCriteria = strQualCriteria & Me.lstQualifications.ItemData(varItem) & ","
        Next
            'trim trailing comma from string
        strQualCriteria = Left(strQualCriteria, Len(strQualCriteria) - 1)
            'add to the SQL statement where clause
        strSQL = strSQL & " AND ((tblPosition.[1qualificationID]) In (" & strQualCriteria & "))) OR (((tblPosition.[2qualificationID]) " _
                & "In (" & strQualCriteria & "))) OR (((tblPosition.[3qualificationID]) In (" & strQualCriteria & "))"
    End If
   
        'order by clause for SQL statement
    strOrderBy = "ORDER BY tblPosition.posnumber"
        'add closing right parantheses for where clause and order by clause
    strSQL = strSQL & ") " & strOrderBy
   
        'update subform recordsource with new SQL statement
    Me.sfrmBoardMemberExport.Form.RecordSource = strSQL
        'requery the subform to show changes
    Me.sfrmBoardMemberExport.Requery

Exit_cmdBoardMemberExecute_Click:
    Exit Sub

Err_cmdBoardMemberExecute_Click:
    MsgBox Err.Number & ", " & Err.Description, , "Error in Sub cmdBoardMemberExecute_Click of VBA Document Form_frmDataExport"
    Resume Exit_cmdBoardMemberExecute_Click

End Sub

Look at sample. Only few strings of code in report footer "On Format event" (Layout is used). Limitations: you can't reorder columns without additional coding
Dbreport.accdb

What type of report are we talking about?

1) For a columnar report, with one field below the other, the idea would be to collapse unselected fields. Modify the record source so that unselected fields become Null, and use “can shrink: yes” to collapse the text boxes.

2) For a tabular report, it can become complicated if you need to control column widths. I created this once, with a dozen predefined text boxes moved into position during the report's open event. This is some tedious programming.

Sometimes, it's simpler to export the data to excel, so the users can finish the formatting to their liking. I know from experience that users like that, and it significantly reduces development time (you need to create much less special case reports).

(°v°)

Try this.

Just note that what you asked for here is not something that might be considered "easy" if you are new to programming...
Access--Sample--SelectHideShowDi.mdb