Link to home
Start Free TrialLog in
TTBSADMINFlag 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.
Avatar of omgang
Flag of United States of America image

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

Try this.

Just note that what you asked for here is not something that might be considered "easy" if you are new to programming...
User generated imageUser generated imageAccess--Sample--SelectHideShowDi.mdb
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial