How to suppress fields with no data & save space in report 2

[ZollerWagner]

Hi,

I'm creating an Access 2000 report that lists organizations, their contact info, and the services they provide. There are separate fields for each of the different categories of services.

Most of the organizations don't have services in every category, so I want to be able to print out a report that doesn't use space for fields that have no data. Is this possible?

This feels like overkill, but I created a separate Table for each separate category, related to the main table with the organizationID. Was that necessary?

Next I created a SubReport for a single category field using the Wizard. It works fine.

Then I've tried adding the following VBA Code to the Detail's OnPrint Event for the SubReport (was that the right place to put it?):

Me![my report name].Visible = Me![my report name].Report.HasData

I've been struggling with this for two days and still get errors. What am I doing wrong?

Which object name gets put in the code where I have "[my report name]"? How can I determine what the real object name is?

TIA for helping end my frustration. I'd really like to know the best way to approach this.

 

[dhookom]

You must hide a subreport from code in the Main report's section containing the subreport.

Me.subrptA.visible = Me.subrptA.Report.HasData

I might question your table structure(s) if I knew what they were.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ZollerWagner]

Thanks, Duane. That really works!

The structure is, in fact, causing me problems.

I thought that to use SubReports, I'd have to use separate tables to get the data to the SubReports. Is that not so?

The information on each organization's services tends to be different, so it's a 1-to-1 relationship. Putting most of that data in the same table would make sense and make the rest of my work easier.

 

[dhookom]

You haven't provided enough information regarding your structure and the "services tends to be different".

Using subreports does not suggest that you need separate tables. reports and subreports should be based on queries. You can create hundreds of queries from a single table. This is only a point of information and I am not suggesting that you place all of your fields in one table.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ZollerWagner]

That's exactly what I needed to hear.

I don't see any reason to have additional tables given the data. The services each organization offers are comments transcribed from a survey. They are specific to each organization.

If the subreports don't require separate tables, I'm axing them.

Thanks again, Duane.

 

[matethreat]

If you have a subreport that contains no data then it should not appear in your main report when it is previewed. It will shrink or grow automatically in the main report depending on the amount of information.

 

[ZollerWagner]

Matethreat, how do you set your properties (can grow, can shrink, auto resize, etc.) so this works? I'm getting very inconsistent results.

It's not clear to me where to set these properties (on the subreport, on the detail of the subreport, on the field in the detail of the subreport, etc.)

 

[ZollerWagner]

After more experimentation, it seems to me that part of the problem could be that the setting for the TOP property is set so that a subreport begins at a specific spot on the form.

Maybe I should have them all placed at the same spot on the main report? In other words, should I set the TOP property all the same? Maybe I could set the property to be the highest possible point on the report, 1" or something like that? I'm guessing that if these sub reports printed, they overlap each other and Access might not know what order to place them.

Is there a way to make it relative to what has occurred before that subreport?

 

[matethreat]

Sorry, I don't know if I can help you there. All of the subreports I have used have queries based around a common field. If there are no items in that field of the subreport then the subreport does not show up in the main report. I have never had to change any setting to get it to work.

One thing to note is that my subreports do not have any informattion in the Report or Page headers.

 

[ZollerWagner]

With the help of another person, I've finally found the answer. You have to create a function that tests the data for IsNull, sets up the text for the text box, and adds any needed spaces.

Here's an example:

Function AddressBlock$ (AName, Addr1, Addr2, City, State, Zip)

Dim A1$, A2$, A3$, A4$, CR$

CR$ = Chr(13) & Chr(10) 'Carriage return and line feed.

A1$ = IIf(ISB(AName),"",AName & CR$)
A2$ = IIf(ISB(Addr1),"",Addr1 & CR$)
A3$ = IIf(ISB(Addr2),"",Addr2 & CR$)
A4$ = City & ", " & State & " " & Zip

AddressBlock = A1$ & A2$ & A3$ & A4$ 'Concatenate the strings.
End Function

Function ISB (V) As Integer
If IsNull(V) or V = "" Then ISB = True Else ISB = False
End Function


Then in the Properties > Data > Control Source, you call the function with a statement like this:
=AddressBlock([LastName],[Title],[Address],[City],[Region],[PostalCode])

Works great.

For more info, see:

http://support.microsoft.com/default.aspx?scid=kb;en-us;210589&Product=acc