Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    178

    Unanswered: reports and filter for sub report/form if populated.

    Hey guys,

    I have a report which contains sub reports/forms. I want know how I can call the report to show records/orders that have populated subforms/reports.

    i.e if the subform/reports is blank, show no records/order. but if there is info in it show the record/order in the report.

    can you help please.

    Thanks

    Aboo

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have the report's record source only list those with matching data.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2006
    Posts
    178
    Hi Trekky,

    Can you please elaborate.

    for example, i have a report that uses the record source of orders table.

    In that report i also have a subform called subfrmList, which contains a list of items linked through the OrderID.

    If the subfrmList is empty i dont want to view the order information in the report.


    thanks

    Aboo

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so you would need your report to NOT use the orders table, but a query that joins the orders table to the "list of items linked through the order ID". If an order has no matching "whatever they are, presumably products" then this query will not show that order. If your report uses that query, then no orders without products will be there.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2006
    Posts
    178
    Hi Trekker,

    Can I do this without creating a query?

    Crrently i am ok filtering orders for particular weeks like

    Code:
     DoCmd.OpenReport stDocName, acNormal, , "Week Between " & Me.txtWeeks & " AND " & Me.txtWeekF
    is there some way of creating a statement by WHERE subform is > "" ?

    Thanks

    Aboo

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Why? The report will be a lot faster if you base it on some SQL re post 4. You could apply a filter on opening with code, but it would have to refer to the query I mentioned re post 4.

    In short, no. One way or the other, you will need some SQL or a saved Query re post 4.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2006
    Posts
    178
    Thanks Trekky,

    Another question, if create a query linking the TblOrders with the TblItemsOrdered and only showing those where OrderIDs are matching, will i not get duplicating rows? as many of items on the "TblItemsOrdered" will have the same OrderID?

    Thanks

    Aboo

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes.

    Then you use DISTINCT or apply GROUPING to counter that.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    May 2006
    Posts
    178
    Thanks Trekky

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •