Help - Filter Master form based on sub-form criteria

[Rschoenb]

Hi,



I hope someone can help me with because I am struggling. I have a Master form which includes tabs within the tab I have subforms. I am looking for a way to filter my master form based on data found within my sub form.

I have a check box within one of the sub-forms called "softwareInstall" if yes the box would be checked. I want to filter all customers within my main form with a check box in my main form to turn on and show all customer who has this box check within the subform SoftwareInstall=True.

I am not sure if I am explaining this correctly. Hoping someone has the answer I have been searching all over!

Thanks,

Ruth

[suttilld]

Are you saying that you have a checkbox in your main form, and if you tick it, you want to see all the customers in the main form that have a software install chackbox ticked in their sub form?

[Rschoenb]

Yes! That is exactly what I am saying.. I can't figure out how to do it! I am hoping it can be done.

[suttilld]

How is your data organised? Does the main form reference the same table as the sub form or a different table? Or do the forms reference queries?

[Rschoenb]

I have my main customer table which is the table used for the main form called "CustomerTracking" the sub form uses another table called "Software" both tables have a relationship on a field called "CustID". The sub form only shows one record which pertains to the customer shown within the Main form. I can navigate through all customers and view each sub form entry but it would be nice to filter the main form based on the "Software" installed check box that are set to true or checked.

[suttilld]

Right, sorry for all these questions, but just trying to get it clear in my mind.
You said that the sub form only shows one record, is that because a customer can only have one type of software installed? Or does the software only show one record at a time, but you can scan through subform records for each customer to see other pieces of software?

[Rschoenb]

No, thank you ! I am just looking for help and don't mind. Each customer has one record within the software table which has several fields about the software one of which is "SoftwareInstalled" check box means True of course . So there should only be one record per customer within this table and sub form. Within the main form I can navite or move through the records with the navigation arrows or use my the simple filter's I have set within the main form.

I am just not sure how to set a filter to evaluate and change the filtering on the main form to only show the customer's who have "softwareInstalled" which is in my other table and viewable within the subform.


I created the form's this way to make data entry easier for my team.

THanks!

[suttilld]

Interesting. I can't immediately see a way to filter as you describe, but I will think. In the mean time, because you have a one to one relationship, i.e. you're only ever going to have one record from the customer table and one record from the software table visible on a form at a time, have you considered a single form based on a query which combines the two tables?

The benefit of a subform is that it can display one to many relationships. As you don't have that, it introduces unneccessary complications.

[Rschoenb]

I have more tables within tabbed sub forms which have a one to many relationship but maybe you are right I guess it would be possible to bring the fields related to software into my customer database. It will just make my customer table larger. I don't believe a query would work because this is used for data entry and I am not sure if a query can be updated. I thought I read somewhere if you use a query it's more for looking up rather then entry.

Thanks for your help

[suttilld]

No worries, queries are fine for data entry. If for some reason you need to keep information in separate tables then it's perfectly OK to use a query to bring the info together in a query and reference that query from a form. Have a look at some of the MS database templates. There are a lot of examples where they do that.