Results 1 to 12 of 12
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Open report from query result


    I import orders for many companies and each has there own packing list requirements. Presently, at the end of the process I open all reports and print those have orders. It gets overwhelming because there's a lot of reports that are empty because no order was submitted for that company in that batch.

    What's the shortest path to only opening packing list reports for the companies that have submitted orders on that specific batch?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Properly setting up the jointype for the query report is based on and/or filtering the query.
    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
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Properly setting up the jointype for the query report is based on and/or filtering the query
    I don't understand the response. How would jointype in a query determine whether a report will open or not? Maybe my question was misunderstood.

    Assume I have a custom designed report for company A, B, C, D and E.
    Assume I import an order for company A, B and C.
    I only want those 3 reports to open.
    I don't want D and E to open.
    Presently, I'm opening all 5 reports and have to flip through them and print accordingly.
    thanks!

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    The jointype can influence whether the report presents a record that seems to have no data - your 'empty' report because company has no order submitted in that batch. I assume the report is based on a query that joins related tables. Show the SQL statement for the report RecordSource.

    So either adjust the jointype(s) in the query or filter the recordset to exclude records where some field is Null.
    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
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    your 'empty' report because company has no order submitted in that batch
    Correct. So I don't want that report to open. I only want to open reports that have records to display.

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Did you try either of my suggestions? You did not post the SQL as requested.
    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
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    There's no incentive to try your suggestions or post SQL. You are not reading my responses or I am failing miserably at trying to explain my objective.

    I import orders for many companies and each has there own packing list requirements which translates to each having a report for themselves. Assume 5 companies, there will be 5 unique reports. Presently, at the end of the process I open all reports and only print those that have orders. If a report is blank, then obviously there was no order imported for that company in that batch. It gets overwhelming because there's a lot of reports that are empty because no order was submitted for that company in that batch.

    I only want to open the reports for companies that submitted an order in that specific batch. How do only open reports that have a record for that company in the query?

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I am reading very carefully.

    As I suggested, the report's RecordSource can be filtered to exclude records that don't meet criteria. In this case the criteria would be some field of the RecordSource is null, maybe the batch number.

    Possibly setting query jointype to 'only the records where key fields in joined tables match' will exclude the 'empty' records.

    If I had the report's RecordSource SQL I might have a clue.

    Wait, you have designed a separate report object for each company and not one report that groups the data by company?

    Show the code procedure that opens the report(s).
    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.

  9. #9
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    the report's RecordSource can be filtered to exclude records that don't meet criteria
    ... let's try this way...
    5 unique reports
    Each report has it's own unique query as a recordsource.
    A macro triggers all 5 reports to open.
    How do I stop reports from opening if it's recordsource is empty?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    That is why I was so confused. Expected one report filtered to only the desired company records. That's how I would do it. Otherwise, need VBA code to check if some data is null and if it is don't open report. The code can be a recordset that you loop through or can be a series of 5 DLookup functions.

    Why 5 reports? If you add companies you have to create new reports? Are these reports identical in structure?
    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.

  11. #11
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    Why 5 reports? If you add companies you have to create new reports?
    Correct. From first sentence in thread...
    I import orders for many companies and each has there own packing list requirements
    i.e. Each has their own logos, layouts, field criteria, etc.

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Okay, then as I said you must resort to VBA.

    I see two options.

    1. Open a recordset of records that returns only the company IDs that meet the criteria for the desired report output. Loop through the recordset and print report for each company encountered.

    2. Use a series of DLookup functions and IF THEN conditional statements to determine if report should print for each company

    EDIT: Another solution possibly the NoData event of the form. http://msdn.microsoft.com/en-us/libr...ffice.11).aspx
    Last edited by June7; 10-06-2011 at 07:40 AM. Reason: Offer another solution
    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. VB ADO Query Result Set
    By Bedsingar in forum Access
    Replies: 5
    Last Post: 09-05-2011, 10:45 AM
  2. Use query result to open form.
    By Playerpawn in forum Access
    Replies: 3
    Last Post: 05-12-2011, 11:18 PM
  3. Replies: 1
    Last Post: 10-29-2010, 10:46 AM
  4. open a report using VBA from dynamic query?
    By haggisns in forum Programming
    Replies: 3
    Last Post: 10-15-2010, 08:45 AM
  5. My report yields more than one result
    By hawzmolly in forum Reports
    Replies: 16
    Last Post: 07-04-2009, 10:30 AM

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