Flag for Trinidad and Tobago asked on

Dynamic Reports

I am presently building a database in Ms Access 2007. The user have indicated that they would like to select their own fields when generating a report. It is possible that the fields can be randomly chosen. I would prefer that the user can simply click check boxes representing each field and click on a command button which opens onto the report.

Can anyone suggest a solution to this problem.
Page Layout SoftwareMicrosoft AccessDB Reporting Tools

Avatar of undefined
Last Comment
Jeffrey Coachman

8/22/2022 - Mon

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

    Exit Sub

    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) & ","
            '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

    Exit Sub

    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

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).

Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

Try this.

Just note that what you asked for here is not something that might be considered "easy" if you are new to programming...
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question