Results 1 to 8 of 8
  1. #1
    Varda is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    44

    Embedded Macro: Where Condition to open a subreport

    Using Access 2010



    I have a form (frmSelectDates_AllItems_Reports) with a combo box (cboFindActionByID), and a command button.
    And I have a report with three subreports on it. The main report is not bound to anything. On the subreports is a field called "ActionByID."

    There's an embedded macro on the On Click event of the command button.

    I would like to select a name in the combo box, click the command button, and have the report open so all three subreports are filtered by the name selected.

    As a first step I've tried to filter only the first subreport. I've tried every single variation known to man, and I keep getting ALL the results in this subreport, and not filtered.

    I've checked numerous times that I'm referring to the control name of the subreport and not the subreport's name (I ended up just changing the control name to be the same to avoid any conflict).

    Here's what I have:

    Open Report
    Report Name: rptAllItems_ActionBy
    View: Print Preview
    Filter Name
    Where Condition= ="[Reports]![rptAllItems_ActionBy]![rptAllItems_Completed_Summary_ActionBy].[Report]![ActionByID]=[Forms]![frmSelectDates_AllItems_Reports].[Form]![cboFindActionByID]"
    Window mode: Normal


    I would appreciate any help or guidance as to which YouTube video to watch or which keywords to search on Google. I've spent over 8 hours searching for the solution before finally giving up and posting here.

    Varda

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Is that name of subreport? That is code in the subreport Open event? Subreports aren't 'opened' - the main report is opened and subreport just tags along. Subreport Open event won't trigger.

    Best would be to bind the main report to a recordset of the ActionByID values. Set subreport container Master/Child Links properties. Filter main report and subreports will synchronize.

    Alternatives:

    1. set the Filter property of subreport with reference to control on form

    2. dynamic parameterized query as subreport RecordSource (I NEVER use dynamic queries)
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Varda is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    44
    rptAllItems_ActionBy is the main report.
    The first subreport that I've tried practicing with is called rptAllItems_Completed_Summary_ActionBy.


    The embedded macro is in the OnClick of the command button on the form.


    1. I will try to bind the main report to ActionByID, but was hoping to use the same report for other filtered reports.
    2. I am trying to avoid doing ths because then I won't be able to use the same report for a different filter.
    3. I've done this and successfully, but I would like to learn how to do it in a way that would allow me to use this report for other filters.


    Thank you very much for your very prompt response. I truly appreciate it.

    Varda

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What other filters? Can those data be included in the main report RecordSource? Dynamically apply filter to whichever fields you want. I don't use macros.

    I prefer http://www.allenbrowne.com/ser-62.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Varda is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    44
    No, it would be practically impossible to include all the filters I want in the main report RecordSource.

    The one filter is a Person Name (tblStaff), and the other filter is a Category. There's no direct relation between these two.

    For now I'm using your suggestion of binding the main report, but that means I had to create another report for the other filter (there are two different fields that relate to tblStaff, so I linked the master and child fields of the subreports differently to capture the second filter). Now I need to create a few more reports like it, each one bound by what I need to filter by. This isn't the ideal solution I was hoping for.

    The Embedded Macro would have been ideal if there's a way to reference a field in a subreport in the Where Condition.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That WHERE CONDITION applies to the main report, has nothing to do with subreports.

    It is possible to programmatically change the RecordSource of a report with VBA. I have never tried to change report Master/Child Links properties with VBA.

    I have also never tried to programmatically set Filter property of a subreport. Don't know if possible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    Varda is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Jun 2013
    Posts
    44
    Oh, I had no idea the Where Condition applies only to the main report and cannot effect the subreports. Now you see, THAT's helpful information to know, and NONE of the websites in my search results mentioned this! ARGH! Could have saved 8 hours yesterday trying to do the impossible.

    One day I'll likely ask about changing the RecordSource of a report with VBA, but for now I'm not sure I need it.

    I'm not going to try and mess with changing the report Master/Child links with VBA as I don't have the technical knowledge to do this anyway.

    Thank you for your guidance. Much appreciated.

    Varda

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The WHERE CONDITION applies to the report named in the Open command.

    As already stated, subreports aren't 'opened', they tag along with main report.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-02-2014, 11:22 AM
  2. Replies: 1
    Last Post: 12-03-2013, 01:38 PM
  3. Replies: 2
    Last Post: 06-30-2013, 09:59 AM
  4. Type mismatch in condition embedded macro
    By geremore in forum Programming
    Replies: 3
    Last Post: 08-09-2011, 02:05 PM
  5. Run Embedded Macro
    By smikkelsen in forum Forms
    Replies: 0
    Last Post: 07-07-2010, 09:44 PM

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