How to Change the Source Object for a Subreport with a CountThem Control

[doyle60]

I have a report with photographs. If there are fewer than five
photographs to display in a group, I want a subreport to open that has
large photos. If there are five or more photos, I want Access to
switch to another subreport to open that has smaller photos. So the
single report will switch back and forth for the printing, depending on
the count in each section.

I placed a subreport called SelfPrintNew in a section called "GroupID"
and created a text box in the same section that adds up the number of
photos. I called it "CountThem" and put "=Count([StyleNumber])" in the
Control Source, keeping the Running Sum property set to No. (I checked
the results out without the code and it surely works correctly.)

I put this in the OnOpen property of the form:

If CountThem < 5 Then
Me.SelfPrintNew.SourceObject = "Report.SelfPrintNewrpt02sub"
Else
Me.SelfPrintNew.SourceObject = "Report.SelfPrintNewrpt01sub"
End If

Nice try, but it doesn't work. I've done this sort of thing many times
and can't quite figure out what to do correctly. I experimented quite
a lot but can't seem to fall on the solution.

I get the following error:

Run-time error '2427':
You entered an expression that has no value.
The expression may refer to an object that has no value,
such as a form,a report, or a label control.

But it is a value. It is surely not a form or report or label. If I
put a "Me." before the CountThem in the code, the same problem occurs.

When I move the code to the OnPrint of the GroupID section, I get an
error stating that it can't set this after printing has started. But I
pretty much think I need it in this GroupID section for the report to
work as I want it to.

Anyway, what is the best way to handle this? Simply put, if the
records add up to such and such, use this source object, if they add up
to that and that, use that source object.

Thanks in advance,

Matt

 

[doyle60]

Thanks for letting me down easy.

I'm thinking I can solve this problem by creating several reports. I
can have one report for a certain number of photos, and other reports
for other numbers. The user will just have to correlate them after
they print---not a big deal as we won't be printing hundreds at a time
and there will be only 15 to 20 pages at the most.

To do this, let me know what you think of this plan. I'm not good with
VBA, so I like to get everything in a query, if I can. The way I see
it, all I have to do is get three new fields in the query.

1) I need a field that numbers the photos, beginning with one. (I
don't need this if I just go with columning the photos as I
successfully do now. But for five photos, that odd number, I may want
to put two large photos side by side above three smaller photos side by
side---making the page look rather neat filling up all the space). The
numbers would help me place the photos. Order is not important.

2) Another field that gives the number of items in each section
(group). The reports will filter correctly based on this number.

3) Another field is needed for the page numbers. There are two fields
that create the order already (CategoryNumber and GroupNumber). A
query can be built to number them sequentially.

Now, as far as you know, this is possible, right?

If so, I need help with number (1). The unique field is GroupID.

Thanks,

Matt

 

[Dick D]

This may work. Include a linked query in the data that drives the report.
Let this query be a totals query that counts your pictures. Now, you have
the data on how many pictures are included. If the report must be printed
switching back and forth depending on the count, you could use the OnFormat
event to make the appropriate subreport visible. Remember to set the CanGrow
and CanShrink properties of the subReports to Yes.

 

[doyle60]

Marshall Barton seems to be saying I still can't make it one report,
however. If I understand him correctly, it will not matter if the
count is in the query or if it is a text control (as it was in the
first failed attempt). Mr. Barton?

For printing five pictures, I want to have two photos over three
photos, like this:

[|||||||] [|||||||]
[|||] [|||] [|||]

I believe it would be helpful to first create a query that numbers the
results in each section (Group).

So, if I have this in the query (GroupID is/was a unique autonumber):

GroupID Style
1876 S100
1876 S101
1876 S107
8238 P111
8238 P111
5211 W500
5211 W500
7772 JS103
7772 JS103
7772 JS105
7772 JS105
7772 JS105

How do I get a field to return this?

GroupID Style SeqNo
1876 S100 1
1876 S101 2
1876 S107 3
8238 P111 1
8238 P111 2
5211 W500 1
5211 W500 2
7772 JS103 1
7772 JS103 2
7772 JS105 3
7772 JS105 4
7772 JS105 5

I googled this issue and could not find anything. I'm sure it is out
there, though. Thanks,

Matt

 

[Marshall Barton]

What Dick is suggesting is a bit different than what I
thought you were asking for, but now that I think about it
some more, you may get the effect you want. It may be kind
of slow, but it should work.

The difference from what you asked about is that you will
include both/all the subreports in the main report at the
same location and use the format event to make all but one
of them invisible.

If you want to get carried away with this concept and don't
have any speed requirements, you can use a different
subreport for each different number of pictures in a group.

 

[Marshall Barton]

Thanks for letting me down easy.

I'm thinking I can solve this problem by creating several reports. I
can have one report for a certain number of photos, and other reports
for other numbers. The user will just have to correlate them after
they print---not a big deal as we won't be printing hundreds at a time
and there will be only 15 to 20 pages at the most.

To do this, let me know what you think of this plan. I'm not good with
VBA, so I like to get everything in a query, if I can. The way I see
it, all I have to do is get three new fields in the query.

1) I need a field that numbers the photos, beginning with one. (I
don't need this if I just go with columning the photos as I
successfully do now. But for five photos, that odd number, I may want
to put two large photos side by side above three smaller photos side by
side---making the page look rather neat filling up all the space). The
numbers would help me place the photos. Order is not important.

2) Another field that gives the number of items in each section
(group). The reports will filter correctly based on this number.

3) Another field is needed for the page numbers. There are two fields
that create the order already (CategoryNumber and GroupNumber). A
query can be built to number them sequentially.

Now, as far as you know, this is possible, right?

If so, I need help with number (1). The unique field is GroupID.

From yourexample data in another post, you do not have a
unique fied that can be used to order the records. If you
did have a unique field that could be used to produce a
unique sort order, then you can use a claculated field in
the report's record source query:

Seq: DCount("*", "thetable", "sortfield<=" & sortfield)

OTOH, if all you want is to identify which detail record you
are currently processing, then use a RunningSum text box
with the expression =1

You can easily determine the number of records in a group by
adding a text box with the expression =Count(*) to the
gtroup header section.

I have no idea what you want to do with the page numbers.

 

[Dick D]

Doyle,
When the count is more than five, how does this change your picture layout?

You can pick up the seq no with a running sum field over the group.

 

[doyle60]

Let's begin at the beginning. My current report prints out photos
using a the column option in a subform. The subform has one image
control in it. It looks simply like this:

[|||]

On printing, one photo looks like this:

[|||]

two photos like this:

[|||] [|||]

three photos like this:

[|||] [|||] [|||]

four photos like this:

[|||] [|||] [|||]
[|||]

five photos like this:

[|||] [|||] [|||]
[|||] [|||]

and so on.

As one can see, printing four and five photos looks ugly, and wastes
space. I want the largest photos possible. With four, I could easily
do a two by two grid, using bigger and flashier photos. (I tried
creating code to switch to another subform when four was printing but
it did not work. See above, where I am told I cannot do this. In the
past, I have used the count and running sum properties to make certain
sections of reports visible or not.)

What I desire to do is as follows:

with one photo (one large photo 8 inches across):

[ ]
[ 1 ]
[ ]

with 2 photos (each photo 3 7/8 inches across):

[ 1 ] [ 2 ]

with three photos (one larger photo 5 1/3 inches with two smaller
photos 2 2/3 inches to the right):

[ 1 ] [ 2 ]
[ 1 ] [ 3 ]

with four photos:

[ 1 ] [ 2 ]
[ 3 ] [ 4 ]

with five photos (two photos 4 inches over three photos 2 2/3 inches):

[ 1 ] [ 2 ]
[ 3] [ 4] [ 5]

with six photos (one larger photo nested in five smaller ones):

[ 1 ] [ 2 ]
[ 1 ] [ 3 ]
[4 ] [5 ] [6 ]

with seven photos:

[ 1 ] [ 2 ]
[ 3 ] [ 4 ]
[ 5] [ 6] [ 7]

with eight photos:

[ 1 ] [ 2 ]
[ 3] [ 4] [ 5]
[ 6] [ 7] [ 8]

and nine and more will just print out three rows at a time.

How should I go about this? Is it worth the trouble? (I got the time.)
The report will be only 15 pages or so and will be printed one or two,
three sets at a time, so correlating is not an issue. (When it is, I
can simply keep the plain version as an option.)

So, what is the best way to do this? I only know how to do simple VBA
things but get some great VBA from this group.

Immediately below the pictures the report numbers need to print out.

Thanks so much for your time,

Matt