Software

Ask something
Discussion Locked

Access2000: Filter By Form with Subforms

0 helpful
5 replies

by irisinfo

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.
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

5 replies

by rafiandi

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.

This discussion is closed to new replies.