How to filter across subforms

[sueb]

So I have two tables ("Customers" and "Orders") with a 1-to-many relationship between them, and I have a form that shows Customer data with Order data in a subform. However, I notice that with this structure, I can't make a filter (using the built-in filtering mechanism) that collects all Customer/Orders that meet a criteria; I can only filter within a single Customer's Order set.

Must I write a bit of code for every kind of filter my users might want to do? Or would it work differently if I made the Customer data a subform on an Orders main form?

Or am I just thinking about this wrong?

[NeoPa]

Sue, this sounds as if you are thinking about this the wrong way. If you have a form that is a subform of another, which is defined by the link between orders and customers, then surely this is not an appropriate place to provide a search/filter mechanism that goes across all customers.

I'm sure it could be done - but is it logically sensible? Will the users not get confused by this schizophrenic approach?

[sueb]

I'm not sure how the need to gather orders for widgets as opposed to thingamajigs is schizophrenic, but we'll let that pass.

Do you have a suggestion for me?

[NeoPa]

I know too little of your database to be of much help here, but I would look at providing the functionality via a separate form. Alternatively, you may (I can't know how true this is for you) be able to reuse the Sub-Form as a stand-alone form and just change how it is filtered.

[sueb]

So I made a form that displays the Orders, with the Customer information as a subform, because usually we are looking through the Orders for status, making notes about scheduling, etc. I didn't foresee this (although it makes sense now that I've done it), but the Customer subform doesn't show (as I had hoped) the Customer for the Order I'm looking at, but the entire Customer database.

How do I restrict the subform to the Customer linked with the Order being displayed?

[NeoPa]

Right Sue. To understand this you need an understanding of Subform controls. These are different from the forms that are used as sub-forms, but they contain the them.

In the properties of the Subform control, you will find two that you need to set to ensure that the sub-form data reflects only those records that match the data in the main form. These two are Link Child Fields and Link Master Fields. If you set these up then it should behave as you expected it should.

[sueb]

Wow, that is exactly what I needed, NeoPa! And I don't know that I would have EVER stumbled across that on my own--I am so grateful to you and the other experts on this site.

Thanks!

[NeoPa]

It's my pleasure to help Sue.

Sometimes, just looking at the results created after running a wizard, can give a lot of understanding about how various typical setups work.

[sueb]

You are so right, NeoPa! I only wish my actual job were managing this (and other databases) so that I had enough time to really take advantage of what Access is telling me. As it is, I'm expected to do this "in my spare time." HA!

[NeoPa]

I can't think of anything more fun than working in Access for your spare time :-D