General discussion

  • Creator
    Topic
  • #2084234

    Access2000: Filter By Form with Subforms

    Locked

    by irisinfo ·

    I am having difficulty with using the Filter by Form function with subforms within a Main form. Regardless of what I search for, if the field is in a subform, it ends up finding the first record in the database (which doesn’t necessarily have the search criteria I’m seeking). This only happens with subforms. If I search the form individually (accessing it directly via the db window), all works fine. The relationships are tight, all are one-to-many. I can’t find any reference to special cases for searching subforms. Any solutions

All Comments

  • Author
    Replies
    • #3786645

      Access2000: Filter By Form with Subforms

      by rafiandi ·

      In reply to Access2000: Filter By Form with Subforms

      You may try command
      SELECT DISTINCTROW
      example:
      SELECT DISTINCTROW [Expense Reports].[ExpenseReportID], [Expense Reports].[ExpenseRptName], [Expense Reports].[DateSubmitted], [Expense Reports].[AdvanceAmount], Sum([Expense Details].[ExpenseItemAmount]) AS [Total Expenses], [Expense Reports].[Paid]
      FROM [Expense Reports] LEFT JOIN [Expense Details] ON [Expense Reports].[ExpenseReportID] = [Expense Details].[ExpenseReportID]
      WHERE (([Expense Reports].[EmployeeID]=[forms]![Expense Reports by Employee]![EmployeeID]))
      GROUP BY [Expense Reports].[ExpenseReportID], [Expense Reports].[ExpenseRptName], [Expense Reports].[DateSubmitted], [Expense Reports].[AdvanceAmount], [Expense Reports].[Paid];

      you may put this command to form propertiesrecord source table.

    • #3708031

      Access2000: Filter By Form with Subforms

      by james_randy ·

      In reply to Access2000: Filter By Form with Subforms

      The first answer looks good to me. If that doesn’t work, try something else. Hope this helps.

    • #3716614

      Access2000: Filter By Form with Subforms

      by irisinfo ·

      In reply to Access2000: Filter By Form with Subforms

      This question was auto closed due to inactivity

Viewing 2 reply threads