Query Help

[bhipwell via AccessMonster.com]

This is a repost as my first request fizzled out.

I have 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 SubRptPeachesThe
visibility setting on the three forms is set to no.

If the company does offer the product Peaches as per a query with three
tables, I
would like to make SubRptPeaches visible. But I am unsure how to do this.

Thanks!

BH

 

[Duane Hookom]

I assume you mean subreports rather rather than "three forms is set..." If a
subreport doesn't return records then it wouldn't display. You don't need to
set it to invisible.

 

[bhipwell via AccessMonster.com]

Yes, I meant the subreports visibility is set to no.

I didn't konw a empty query would not make the subreport run. I'll give that
a shot.

But what about a couple of page breaks I have based on whether a particular
fruit is offered by the company?

BH

 

[Duane Hookom]

You can make a PageBreak control invisible with code in the On Format event
module of the section containing the PageBreak control. Your code might look
like:

Me.pgbrkCtrlName.Visible = Me.srptOne.Report.HasData

Substitute your control and subreport names.

 

[bhipwell via AccessMonster.com]

That work for subreports. I need to throw something else out there, however.


All things being the same as before, what if I have an image that should be
visible only if Peaches is offered as a product?

My general thinking is a query could get this data. However, using the
tblCompanyProducts and tblProducts query, I cannot isolate down to a single
cell. The query will have many rows with available products.

Access would have to check each row until it found "Peaches" in the product
column. Is this the right way? If so, seems like I need some sort of search
loop.

Thoughts?

BH

 

[Duane Hookom]

Where is the image? Is Peaches a subreport?

You need to be much more specific if you want my help. I am not the great a
guession what you have and what you want.

 

[bhipwell via AccessMonster.com]

Starting with a clean slate...

tblCompany
- CompanyID
- CompanyName

tblProduct
- ProductID
- ProductName

tblCompanyProduct
- CompanyProductID
- CompanyID
- ProductID

qryProductsOffered
- tblCompanyProduct.CompanyProductID
- tblCompany.CompanyName
- tblProduct.ProductName

I need to be able to anything from the qryProductsOffered. As we know, the
qry will produce a list of all the companies and the products they offer. If
I filter for company ABC, the qry might look like...

id1234 ABC Company Apples
id1235 ABC Company Bananas
id1246 ABC Company Peaches

Let's say I have two images on a form. The images visibility is set to "No".
The first image is a picture of a Peach, the other a picture of Grapes.

If ABC Company offers Peaches, then the picture will be visible. As goes, if
ABC Company offers Grapes, then that image will be visible.

Since the form, set to the qry, will reference the first row, the form
doens't know Peaches are offered in the third row nor that Grapes aren't
offered at all.

How do I go about setting making the visibility settings contingent on my
query which may have many, many rows?

Thanks for your patience.

BH

 

[Duane Hookom]

You can count the number of records containing "pears" in the report header
with a text box and Control Source like:
=Sum(Abs([ProductName]="pears"))

You can then reference the value from the text box in your code to make an
image control visible or not.

 

[bhipwell via AccessMonster.com]

Duane,

Thank you! That will work just fine and this is the path I will take.

Do you know, however, if this is the most effecient way?

Anyhow, thanks for your patience. I've working on this database for sometime
and it can get a bit rough when your brain freezes up on something that
appears relatively simple.

BH

 

[Duane Hookom]

Creating an aggregate on the report's record source is generally quite
efficient.