Results 1 to 4 of 4
  1. #1
    LA727 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    2

    How to use a single report with different queries/filters/sorts


    Hi All!

    I've been using access for a few years now and consider myself a pretty advanced user, but for some reason this question has stumped me. I've been trying to use a single report with different queries/filters/sorts and occasionally a filter or sort will "stick" to the report, so when I try to open it again it will give me the message box asking what a particular field is. I've tried to remove the filter/sort in the on_close event, but that doesn't seem to always work either. The worst part is it's seemingly random as to when it decides to give me this error; I can't figure out what's causing it. I'll post my code below, let me know if you have any suggestions.


    Code:
    'This is the typical open report statement I use with this report. Of course each uses different recordsources, filters and sorts.
    DoCmd.OpenReport "CI_Report", acViewReport
    Reports!CI_Report.Report.recordSource = IIf(getSTROpSelectQ = 1, "STR_CI_Open", "STR_CI_Max") 'Function to figure out which query to use
    Reports!CI_Report.Report.Filter = getSTRFilter  'Function to get the filter
    Reports!CI_Report.Report.orderby = getSTRSort  'Function to get the sort
    Reports!CI_Report.Report.OrderByOn = True
    Reports!CI_Report.Report.filterOn = True
    Code:
    'The onClose event for the report. I set the filter and orderby properties to empty strings
    'then set the recordSource as a default query.
    Private Sub Report_Close()
    Me.Filter = ""
    Me.filterOn = False
    Me.orderby = ""
    Me.OrderByOn = False
    Me.recordSource = "CI_q4"
    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have you tried putting your orderby/sortby code in the report open event? Or perhaps the deactivate event?

    failing that - handle the error messages?

  3. #3
    LA727 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2020
    Posts
    2
    Cool, I'll try to add the code in those events.

    But I'm not sure, how do you handle error messages that are asking for the field? Is it just in the onError event?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you would have something like


    Code:
    on error goto errctrl
    
    'code here
    exit function/sub
    
    errctrl:
    select case err
       case 1233445
           'handle this error
        case 3423254
           'handle this error
    end select
    resume next

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 10-12-2017, 06:12 PM
  2. Replies: 17
    Last Post: 08-09-2015, 09:45 AM
  3. Replies: 3
    Last Post: 03-26-2015, 11:10 AM
  4. Multiple Sorts on one page in a report?
    By mpeterson in forum Reports
    Replies: 1
    Last Post: 06-25-2014, 01:01 PM
  5. Need single report to pull from 2 queries
    By Nathan Plemons in forum Access
    Replies: 2
    Last Post: 03-14-2012, 11:04 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums