Report - Filter/Visibility Question

[bhipwell via AccessMonster.com]

Hello,

Have three tables...

1) Company
2) Products
3) CompanyProducts

I have a company report with a number of subreports. Each subreport contains
data specific to a particular product.

I only want the subreports for the products offered by the company to be
visible. I understand the visibility coding involved, it is how to tell the
report which subreports to make visible I am stumped on.

B

 

[Allen Browne]

Create a query based on tables 2 and 3 (products and companyproducts.) Use
this as the source for your subreport.

The main report is based on the Company table, so it has the CompanyID. With
the main report open in design view, right-click the edge of the subreport
control and choose Properties. Set these:
Link Child Fields CompanyID
Link Master Fields CompanyID
Now the subreport will only show the products for the company on the main
report.

Pressumably the CompanyProductsID table has fields:
- CompanyID relates to Company.CompanyID
- ProductID relates to Products.ProductID

 

[bhipwell via AccessMonster.com]

Thanks,

What is the code to get the visibility to work? For example, my query would
have the following:

QryProducts
- CompanyProductsID
- ProductsID
- Company
- Product

With this query I would end up with data that would look like the below
(filtered for one company):

1234 9876 ABC Company Peaches
1235 9875 ABC Company Plums
1236 9874 ABC Company Bananas

On the on open event I need some code similar to...

If xxx then
me.subrptpeaches.visible = true
end if

It is the XXX part I am not getting. Thanks in advance.

 

[Allen Browne]

What code?

If you use the LinkMasterFields/LinkChildFields properties of the subreport
control, the subreport will show the products relevant to the company.

 

[bhipwell via AccessMonster.com]

Perhaps I wasn't clear. I have multiple subreports (about three). One
subreport has to do with apples, one with peaches and one with bananas. I
only want the subreports visible if the company offers that particular
product. Make sense?

BH

 

[Allen Browne]

Perhaps I wasn't clear. I have multiple subreports (about three). One
subreport has to do with apples, one with peaches and one with bananas.
I only want the subreports visible if the company offers that particular
product.

Sorry: I have absolutely no idea what you are talking about, so I can't
help.

 

[bhipwell via AccessMonster.com]

Let me try again...

Three tables. TblCompany, TblProducts and TblCompanyProducts. Basic table
set up. There can be infinite number of companies and infinite number of
products that each company can offer.

I then have a report called RptCompanyInfo. On this report there are only
three subreports called SubRptApples, SubRptBananas and SubRptPeaches. The
visibility setting on the three forms is set to no.

If the company does offer the product Apples as per the TblCompanyProducts, I
would like to make SubRptApples visible. But I am unsure how to do this.

Thanks again for your patience.

B