Filtering Master Records and Subform Child Records

[Nerther]

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

[June7]

You can filter the main form on patient info and the subform records will synchronize. If you want to also filter the subform on payment info, that will be a separate event.

If you want to filter on parameters against both tables at once, can't use your data entry form/subform for that. Use another form or report that has a query as RecordSource that joins the tables. Use that query as the RecordSource for form or report. Use dynamic parameters in the query or use VBA code to construct multi-criteria filter string and pass the filter when opening form or report.

[Nerther]

You can filter the main form on patient info and the subform records will synchronize. If you want to also filter the subform on payment info, that will be a separate event.

If you want to filter on parameters against both tables at once, can't use your data entry form/subform for that. Use another form or report that has a query as RecordSource that joins the tables. Use that query the RecordSource for form or report. Use dynamic parameters in the query or use VBA code to construct multi-criteria filter string and pass the filter when opening form or report.

Hi, thanks for your response.

Creating a new form would be a huge amount of work because the current form has over 50 fields in structured tabs.......there must be another way?

I can run a join query which can return results that I want. This will give me a list of the Master Record ID's that I want to display. Is there anyway to pass this list to the Filter function I wonder- perhaps as an array4? Or another way perhaps?

Cheers
Nerther

[June7]

A 'huge' amount of work? Open new form/report, build query in RecordSource, drag fields from field list to create controls, arrange controls. About 10 minutes to create basic object.

Pass to what Filter - the main form or the subform? As noted, can't use your existing form/subform.

[Nerther]

My form has a plethora of tabs and tabs within the subform as well, not to mention a lot of calculations based on user entry etc. Also, the form is still in development so I would then have to replicate changes across another new form....

Since I can return the results I want in a Query, I could go through each of the Master Records from the Query, get their Record ID, and build a new query with an IN() string with all the ID's that I want to show. I can then run this new query and assign it as the recordset for the Master Form.

I can then do the same for the Subform, grab all the Record ID's of the Child table from the original returning query, then build another query with an IN() string, run this, and assign the recordset to the Subform.......

A convoluted way of getting what I want to achieve, but much less work than duplicating forms....unless there's any other options? Like somehow passing a list of Record ID's to the Filter in the Main form? I can't seem to work out how to pass a list of "matching" criteria to a Form's filter though

[orange]

I don't see why you refer to your tables as Main and Child. It seems based on June7 responses you are dealing with
Patients, Visits and Payments.

If you use Form/subform, the data should synchronize as June7 said.

If your tables and relationships are set up properly, forms etc are

relatively

straight forward.

Seems to me (and it's easy when you're not directly involved) that you have time to work on a workaround, but no time to set it up properly.

Good luck with your project, regardless of what you decide to do.

[June7]

Well, you could use VBA to set RecordSource or Filter properties of both forms, I simply don't understand why. Filter main form and subform synchronizes. Can then further filter the subform (that many records it's necessary?). But to initiate a filter of main form with criteria from subform is not practical. Children do not dictate to parent.