Hi all,
I would love some help on this issue that I've been trying to sort out for weeks.....no doubt I'm over thinking it :P
I have a master table and child table. I have created a Main form with a subform for the child records to display the data...... and all is well.
Now, I have created a new form with Lookup Lists etc for the user to select to create a filter or query on this Main Form and its subform.
So, say we have the following fields on the Master Table
- First Name
- Last Name
- Location
And say we have the following fields on the related Child Table
- Payment Made
- Amount Paid
- Date Paid.
So, in this scenario, A person can come and visit our Clinic multiple times, and each time, arranging a payment. Hence the one to many relationship with the Child Table
Now, I can create a filter no problem to filter the master records on "First Name".
E.g.
Me.Filter = "[First Name] = '"John"'Me.FilterOn = True
But, how can I create a filter that shows me only Master Records where "Payment Made = True"
Or, show me Master Records where "Location = Smith Street" AND "Payment Made = True"
I can create this as a query of course, which looks something like "SELECT DISTINCT MasterTable.* from MasterTable INNER JOIN ChildTable on LinkingID"and then assign this record source to the Main Form, but the problem with this is that the resulting records cannot be edited in the Form because of the DISTINCT function used in the SQL query.
and, if I go and create a filter on the Sub Form like
Me.Filter = "[Payment Made] = 'True'Me.FilterOn = True
then sure it filters the child records, but does not filter the joined master records..
I hope what I'm saying makes sense. Does anyone have any ideas?
Cheers,
Nerther