Link to home
Start Free TrialLog in
Avatar of Sam Hens
Sam HensFlag for United States of America

asked on

A report to be custom filtered with VBA

User generated imageI have 4 reports, I would like to see if I could turn them into one report by having a filter change the layout or recordset. I have a form which has 4 Combo Boxes, depending what you choose you could get an error telling you to select the right criteria to open 1 of 4 reports with the option to sort.  The type of report is either individual agent or all agents, then the review period is either current week or previous week, while agent is only required if type is single.  I want to try to eliminate some querys and reports by making these reports one, but making a custom filter select the different record sets based on some more vba, but I am pretty new to the world of VBA so I need some advice/guidance. This is nessicary because there are a few more things I need to do/add.

Avatar of Richard Cole
Richard Cole
Flag of United Kingdom of Great Britain and Northern Ireland image

You will need to post more information about what you are trying to do, along with an example sheet or some images

Avatar of Sam Hens


Thank you, I updated my question, how is that? If you need to see the code I have so far I will post it as well.

Is this for Access?

Yes this is for access

I’ve added access to the topics list - hopefully someone with knowledge of Access will be able to help you.


if I understand right what you want:
  • You have 4 different reports
  • You have 4 different queries for them (1 for each)
  • You have dependent comboboxes already working, means: If you select "Single" you can enter a name, if not, the name is disabled or hidden - or something like that.

And now you want to reduce the number of reports and queries by changing the layout of a report with VBA before executing?

Of course you should always post code you already have, at least for "Run Report" (and don't forget to enclose code in code tags here).

Better would be if you (in general) upload a demo database containing the reports, queries, this form and needed tables, filled with some anonymized demo data.

With the current information it's not possible to say much about your problem.

So in general:
  • If the report you want to have for currently 4 reports looks the same in all cases and only differs in the filter setting, it's easy: The button code would only need to assemble the filter string (=WHERE condition without the word "WHERE") which you can use in the DoCmd.OpenReport command to return a report with the desired records.
  • If the report has different columns or different layout, stay with what you currently have. It's in general a bad idea to change the layout of an object (form or report) at runtime. If you ever need to deploy your database file as ACCDE or with an Access Runtime version the code would fail as they don't allow design changes at run time.

In general, it is no problem to have many reports/queries as long as you don't have a file size near 2GB for the Access file. And that is rather rare if you don't save binary content into the file (which in general is not a good idea).
Report and query definitions costs as good as no space in the file.

For more information you would need to provide a demo database as said above, and/or screenshots of the 4 reports, SQL texts of the queries, code you currently use (complete code of the shown form, additionally code in the reports, if there is some, code from modules/class modules which belongs to this code) and also demo screenshots of what you want to achieve. (So usually easier to upload a demo database...)


Thank you, I'm pretty new to this form, but ill keep all that in mind next time I need help, I was struggling to make a filter, i was trying to use recordset, also tried the where cause with out where. Maybe I did it wrong I was putting rs = where clause then having the filter = that record set.  
The commented out section is my code, building a demo database right now, its just a bit difficult excluding all the sensitive info

Option Explicit
Dim rptSingle As Boolean

Private Sub btnClear_Click()
    ' reset the form
    cboRevPd.Value = Null
    cboAgent.Value = Null
    cboSortBy.Value = Null
    cboType.Value = Null
End Sub
Private Sub Slct_job_Change()

'Dim MySet As QueryDef

'Set MySet = MyDB.CreateQueryDef("Create_table", "SELECT Break, Classification, Branch into temp_table FROM Job_table WHERE Job_no = [Forms]![UseExistData]![Slct_job] And Data > 0 GROUP BY Job_table.Branch, Job_table.Classification, Job_table.Break;")

' renamed the comboboxes so the code is easier to read
Private Sub btnRunReport_Click()
'Dim strSQl As Recordset
'Dim strSQl2 As Recordset
'Dim db As DAO.Database

 '  Set strSQl = CurrentDb.OpenRecordset("tbl_4_impend_Prev = Full Name;", dbOpenDynaset)
  ' Set strSQl2 = CurrentDb.OpenRecordset("tbl_3_impend = Full Name;", dbOpenDynaset)

    ' if the user hasn't selected all the items for the report exit
    If IsNull(Me.cboType.Value) And IsNull(cboRevPd.Value) And IsNull(cboAgent.Value) Then
        MsgBox "Please select all the required options"
        Exit Sub
    End If
    Dim sortBy As String
    sortBy = ""
    If Not IsNull(cboSortBy.Value) Then
        ' the user wants to sort the report by the selected field
        Select Case cboSortBy.Value
            Case "Date"
                sortBy = "Event Time"
            Case "Name"
                sortBy = "Full Name"
            Case "Logged In"
                sortBy = "SStart"
            Case "Logged Out"
                sortBy = "Out Time"
            Case "Total"
                sortBy = "SumOfCountOfEvent Type"
        End Select
    End If
    If IsNull(cboType.Value) Then
        MsgBox "Please select all the required options"
        Exit Sub
    End If
    If rptSingle Then
        ' the user has selected to run a report for a single agent
        If IsNull(cboAgent.Value) Then
            MsgBox "Selecting an agent is required for 'Single' reports"
            Exit Sub
            ' appending "" changes the value from null to "" if nothing is selected
            Select Case cboRevPd.Value & ""
                Case ""
                    MsgBox "Selecting a review period is required."
                Case "Current Week"
                    ' DoCmd.OpenReport "Rpt_TimeclockAgent_Curr", acViewReport, , , , sortBy
                    DoCmd.OpenReport "Rpt_TimeclockAgent_Curr", acViewReport, , , , sortBy
                Case "Previous Week"
                    ' DoCmd.OpenReport "Rpt_TimeclockAgent", acViewReport, , , , sortBy
                    DoCmd.OpenReport "Rpt_TimeclockAgent", acViewReport, , , , sortBy
            End Select
        End If
        ' the user has selected to run a report for all agents
        ' appending "" changes the value from null to "" if nothing is selected
        Select Case cboRevPd.Value & ""
            Case ""
                MsgBox "Selecting a review period is required."
            Case "Current Week"
                'DoCmd.OpenReport "Rpt_Timeclock_Curr", acViewReport, , , , sortBy
                DoCmd.OpenReport "Rpt_Timeclock_curr", acViewReport, , , , sortBy
            Case "Previous Week"
                'DoCmd.OpenReport "Rpt_Timeclock", acViewReport, , , , sortBy
                DoCmd.OpenReport "Rpt_Timeclock", acViewReport, , , , sortBy
        End Select
    End If

End Sub

Private Sub cboType_Click()

    ' if Single is selected then the an agent is required
    ' the equals in the parens returns a true or false, so it can
    ' be used as a value for a boolean variable
    rptSingle = (cboType.Value = "Single")
    ' enable or disable the combo for selecting an agent if it is or isn't required
    cboAgent.Enabled = rptSingle

End Sub

Open in new window

Avatar of Bitsqueezer
Flag of Germany 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
Yeah, I didn't realize I left in stuff I haven't quite finished, that was me trying a different way to create a filter since the other way I tried didn't work. I ended up working on a different problem for a minute  due to trouble finding good VBA access resources, since most of it seems like its for excel. so thank you for the material ill study it!
My thought process was I have 4 reports, 2 reports are to select a single agent, while all have the same data types just different periods of time. So I used a boolean to help limit the 4 reports  to two based off the type of report selected. If "all" is selected it locked the agent combo since that should only be for single type reports, while the user still has to select the review period that they want either this week and last week. The Sortby combo is optional.
This entire code for the most part is in the on-click event of the button, so when they click the button it will give them an error or run the report. I figured that the ambiguous error you mentioned only runs if you don't choose  the type which should be obviously enough, but you are right about that it should be fool proof for the users.
You have taught me a lot with this, I really appreciate the help.