[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2783
  • Last Modified:

Ms Access Filter Report from form input - Have solution, but can it be done more efficiently

Hi,

I am using a form to open reports (screen shot attached) that enables the user to select filters as required. The filters are used to build the "Where" clause of the sql. I have a working solution, but I need to know if my approach is efficient or not, or if there is a better way to achieve this. Please see code[Private Sub Preview_Click()] below.

For anyone checking out the Reporting Menu form, the report type is selected first. This changes the drop down menu contents (code shown below). The sort order is determined within the form, however, I build the "Order by" string during the Open event on teh report. I do not build the Order By string in the form because not all reports have the same fields, or need to be sorted in the same way. I have provided an example of the code attached to the reports.
Private Sub Preview_Click()
'************************************************************************************************
' FUNCTION NAME:        Preview_Click
' PURPOSE:              Previews selected report and closes ReportsMenu
' INPUT PARAMETERS:     [cmboReports].Value
' RETURN:               Preview of selected report
'************************************************************************************************
On Error GoTo Err_Preview_Click
Dim strFilterCriteria As String, errMsg As String
 
'Confirm a report has been selected
    If IsNull(Me.cmboReports) Then
        MsgBox "Select a report to run from the Select Report combo box", vbInformation, "No Report Error"
        Exit Sub
    End If
 
'Set filter criteria for Service
    If IsNull(Me.SelService) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = "[Service]=" & "'" & Me.[SelService] & "'"
    End If
 
'Set filter criteria for ServiceType
    If IsNull(Me.SelServiceType) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[ServiceType]=" & "'" & Me.[SelServiceType] & "'"
    End If
 
'Set filter criteria for Corps
    If IsNull(Me.SelCorps) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[Corps]=" & "'" & Me.[SelCorps] & "'"
    End If
 
'Set filter criteria for Unit
    If IsNull(Me.SelUnit) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[Unit]=" & "'" & Me.[SelUnit] & "'"
    End If
 
'Set filter criteria for Sub Unit
    If IsNull(Me.SelSubUnit) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[SubUnit]=" & "'" & Me.[SelSubUnit] & "'"
    End If
 
'Set filter criteria for Sub Unit
    If IsNull(Me.SelSubSubUnit) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[SubSubUnit]=" & "'" & Me.[SelSubSubUnit] & "'"
    End If
 
'Set filter criteria for FileNo
    If IsNull(Me.selFile) Then
        strFilterCriteria = strFilterCriteria
    Else
        strFilterCriteria = strFilterCriteria & " AND " & "[FileNo]=" & "'" & Me.[selFile] & "'"
    End If
 
    MsgBox strFilterCriteria
    DoCmd.OpenReport [cmboReports], acPreview, , strFilterCriteria           'Open selected report
    DoCmd.Close acForm, "frmReportsMenu1", acSaveNo      'Close ReportsMenu
 
Exit_Preview_Click:
    Exit Sub
 
Err_Preview_Click:
    If Err = 2501 Then
        Resume Exit_Preview_Click
    Else
        MsgBox Err.Description
        Resume Exit_Preview_Click
    End If
End Sub
'*****************************************************************************************
Private Sub SelectReportType_AfterUpdate()
 
'************************************************************************************************
' FUNCTION NAME:        SelReportType_AfterUpdate
' PURPOSE:              Reset control source for Report listbox and requery
' INPUT PARAMETERS:     Me.SelReportType
' RETURN:               Report list box source data refreshed
'************************************************************************************************
On Error GoTo Err_Handler
Dim intReportSelection As Integer, strReportType As String, strCaption As String, sqlRowSource As String
 
    intReportSelection = SelectReportType.Value
 
    Select Case intReportSelection
 
        Case "1"
            strCaption = "Unit Reporting Menu"
            strReportType = "ReportUnit"
        Case "2"
            strCaption = "Chief Clerk Reporting Menu"
            strReportType = "ReportCC"
        Case "3"
            strCaption = "Statistics Reporting Menu"
            strReportType = "ReportStatistics"
        Case "4"
            strCaption = "Manning & Establishments Reporting Menu"
            strReportType = "ReportManning"
        Case "5"
            strCaption = "PMKeyS Reporting Menu"
            strReportType = "ReportPMKeyS"
        Case "6"
            strCaption = "Individual Readiness Reporting Menu"
            strReportType = "ReportAIRN"
        Case "7"
            strCaption = "Unit Register Reporting Menu"
            strReportType = "ReportRegister"
        Case "8"
            DoCmd.OpenForm "frmReportsMenu2", acNormal
            DoCmd.Close acForm, "frmReportsMenu1", acSaveNo
            Exit Sub
        Case "9"
            strCaption = "Registry && Filiing Reporting Menu"
            strReportType = "ReportRegistry"
        Case "10"
            strCaption = "Operations Reporting Menu"
            strReportType = "ReportOperations"
        Case "11"
            strCaption = "Staff-in-Confidence Reporting Menu"
            strReportType = "ReportStaffInConfidence"
        Case "12"
            strCaption = "Welfare-in-Confidence Reporting Menu"
            strReportType = "ReportWelfare"
        Case "13"
            strCaption = "Discipline Reporting Menu"
            strReportType = "ReportDiscipline"
        Case "14"
            strCaption = "Deployment / UAC Reporting Menu"
            strReportType = "ReportDeployable"
        Case "15"
            strCaption = "Formation Reporting Menu"
            strReportType = "ReportFormation"
    End Select
    
    sqlRowSource = "SELECT systblMenus.ObjectName, systblMenus.ObjectTitle FROM systblMenus LEFT JOIN systblMenusData ON systblMenus.ObjectName = systblMenusData.LinkObjectName WHERE (((systblMenus.ObjectHidden)<>Yes) AND ((systblMenusData.ObjectMenuLocation)='" & strReportType & "')) ORDER BY systblMenus.ObjectTitle;"
    
    Me.cmboReports.RowSource = sqlRowSource
    Me.cmboReports.Requery
    Me.Caption = strCaption
    Me.lblTitle.Requery
 
Exit_Handler:
        Exit Sub
Err_Handler:
        MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "SelectReportType_AfterUpdate()"
        Resume Exit_Handler
End Sub
'************************************************************************************************
Private Sub Report_Open(Cancel As Integer)
'************I N C O M P L E T E********************************
'************************************************************************************************
' FUNCTION NAME:        Report_Open
' PURPOSE:              Set OrderBy property (sort order) based on selection from ReportsMenu
' INPUT PARAMETERS:     intSortOption
' RETURN:               Report is sorted based on option selected
'************************************************************************************************
On Error GoTo Err_Handler
Dim intSortOption As Integer, strOrderBy As String
 
    intSortOption = Form_frmReportsMenu1.FrameSort.Value
 
    Select Case intSortOption
        Case "1"
           'Report default
            strOrderBy = "RankOECode DESC,Surname,Initials"
        Case "2"
            'Rank Alphabetical
            strOrderBy = "RankOECode DESC,Surname,Initials"
        Case "3"
           'Alphabetical
            strOrderBy = "Surname, Initials"
        Case "4"
            'SubUnit Rank Alphabetical
            strOrderBy = "SubUnitSortPri, RankOECode DESC,Surname,Initials"
        Case "5"
            'SubUnit SubSubUnit
            strOrderBy = "SubUnitSortPri,SortPriSubSubUnit, RankOECode DESC, Surname, Initials"
        Case "6"
            'Employee ID
            strOrderBy = "EID"
        Case "7"
            'Ascending Date (date field used may vary)
            strOrderBy = "Date, Surname, Initials"
        Case "8"
            'Descending Date (date field used may vary)
            strOrderBy = "Date DESC, Surname, Initials"
        Case "9"
            'Ascending Value (value field used may vary - must update Value field name, or delete option)
            strOrderBy = "Value, Surname, Initials"
        Case "10"
            'Descending Value (value field used may vary)
            strOrderBy = "Value DESC, Surname, Initials"
        Case Else
            MsgBox "Else"
            'Report default
            strOrderBy = "RankOECode DESC,Surname,Initials"
    End Select
 
    Me.OrderBy = strOrderBy
 
Exit_Handler:
        Exit Sub
Err_Handler:
        MsgBox "Error " & Err.Number & " - " & Err.Description, vbExclamation, "Report_Open()"
        Resume Exit_Handler
End Sub

Open in new window

ScreenShot007.png
0
Kev
Asked:
Kev
  • 3
  • 2
2 Solutions
 
Jeffrey CoachmanCommented:
budorat,

I don't really see anything wrong with your code.
As long as it works....
;-)


1. I am not a big fan of "string-ing" my Declarations on one line:
    <Dim intSortOption As Integer, strOrderBy As String>
... Instead I would use:
Dim intSortOption As Integer
Dim strOrderBy As String

This is because a lot of people will wrongly write this:
Dim strEmpName, strProjectName As String
...The assumption here is that both variables are strings.
But in reality only strProjectName is a string because it is explicitly declared as a string.
strEmpName is actually a "Variant" datatype, because not datatype has beed explicitly set to it.
Many people wrongly assume that all the variables will "inherit" the last datatype specified.
This is not the case in your code, obviously, I just thought I would mention it.

2. The other thing is that most developers would frown on using spaces in your object Names.

But again, I really can't see anything inherently bad about your code.
    "If it aint broke..."

;-)

JeffCoachman
0
 
KevAuthor Commented:
Hi Jeff,

Thanks for the feedback. I acknowledge your comments re "string-ing" declarations. I only like to do it because it keeps things tidy for me. I am anally retentive re explicitly setting my data types...... so I will hopefully aviod the problems you mentioned.

I avoid using spaces in my object names at all costs. Have I got an example of that within this code ?? I can't seem to see it (think I need time away from my computer lol).

Kev
0
 
Jeffrey CoachmanCommented:
OK,

Now that I look a bit closer...

I would delete either the Form Title or the Form Caption.
They both say the same thing, ("Unit Reporting Menu"), hence they are redundant.

I would consider making the "Select Report Type" and "Sort Order" option groups ComboBoxes instead.
This will save space on the form.
It will also help if the lists grow
Finally it will save you time from making design view changes to the form every time a selection changes.

;-)

Jeff
0
 
KevAuthor Commented:
Excellent Feedback as always !!!!
0
 
Jeffrey CoachmanCommented:
<I avoid using spaces in my object names at all costs. Have I got an example of that within this code ?? I can't seem to see it>
You are correct, ...sorry, I was looking at your Captions you were setting in code.

;-)

Jeff
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now