Three formats for one report

[BruceM]

I have constructed a Vendor database. At intervals we need to send
information requests to certain vendors (the Approved Vendors). These
requests are faxed, so I have constructed a report to be printed. I have
based the report on a query that includes only the Approved Vendors and
filters for a date range (or for vendors from whom information has already
been requested, but who have not responded). A user-defined function in a
standard module is the Control Source for a text box on the report; it tests
for dates and other conditions, and produces text accordingly. For
instance, the text is "Information due soon" or "This is the second
request", as needed. If the first condition is met, an integer named
intStatus is assigned the value 1; if the second condition is met, its value
is 2, and so forth. There are about twelve conditions, with Select Case to
specify the text string to use for each condition:

Select Case intStatus
Case 1
strFirstStatement (defined elsewhere in the function)
Case 2
strSecondStatement
etc.
End Select

This works as it should, after generous assistance from the good folks here.
The final piece (I think) is about the three formats I mentioned in the
Subject line of this posting. In some cases the information request is a
single page explaining the information being sought; in another case it is
the explanation page, with a survey on the second page; the third condition
is the same as the second, but with a different survey. My idea is that I
will use subreports as the surveys, and will hide them or show them as
needed.
If this is a reasonable approach, does it make sense to use a Select Case
statement in the function to specify whether to show a subreport? If so,
would I need to use the Reports!MyReport!MySubreport naming system (since
the function is in its own module rather than in the report's code module)?
Or does an entirely different approach suggest itself?
I have tried to provide enough information to explain the situation, without
getting bogged down in excessive detail. I can certainly provide additional
information as needed, in case I left out something important.

 

[Marshall Barton]

I have constructed a Vendor database. At intervals we need to send
information requests to certain vendors (the Approved Vendors). These
requests are faxed, so I have constructed a report to be printed. I have
based the report on a query that includes only the Approved Vendors and
filters for a date range (or for vendors from whom information has already
been requested, but who have not responded). A user-defined function in a
standard module is the Control Source for a text box on the report; it tests
for dates and other conditions, and produces text accordingly. For
instance, the text is "Information due soon" or "This is the second
request", as needed. If the first condition is met, an integer named
intStatus is assigned the value 1; if the second condition is met, its value
is 2, and so forth. There are about twelve conditions, with Select Case to
specify the text string to use for each condition:

Select Case intStatus
Case 1
strFirstStatement (defined elsewhere in the function)
Case 2
strSecondStatement
etc.
End Select

This works as it should, after generous assistance from the good folks here.
The final piece (I think) is about the three formats I mentioned in the
Subject line of this posting. In some cases the information request is a
single page explaining the information being sought; in another case it is
the explanation page, with a survey on the second page; the third condition
is the same as the second, but with a different survey. My idea is that I
will use subreports as the surveys, and will hide them or show them as
needed.
If this is a reasonable approach, does it make sense to use a Select Case
statement in the function to specify whether to show a subreport? If so,
would I need to use the Reports!MyReport!MySubreport naming system (since
the function is in its own module rather than in the report's code module)?
Or does an entirely different approach suggest itself?

That's a reasonable approach. I assume that you are using a
text box for the information being sought. You can make
that invisible when you make one or the other subreport
invisible.

If your report is restricted to a single vendor, or to
vendors that get the same letter, you could use a single
subreport control. Then use the report's Open event to set
the SourceObject to whichever subreport.

 

[BruceM]

The information is obtained automatically, based on criteria in the records.
The source query looks at expiration dates on certificates and other
criteria. A calculated field combines the month and day from the
certificate with the current year, and compares that date to the expiration
date. If that date is within 30 days of today, the record shows up in the
query.
After the records have been filtered, the user-defined function takes over
to generate the text of the fax. If the calculated date and the expiration
date match, the certificate expires this year, and the message is "Expires
soon". If they do not match (e.g. because the certificate expires next
year), the message is "Confirmation needed". An option group selection on
the form is then marked to show the update has been requested. If it has,
and if the calculated expiration date is two weeks or more in the past, a
"Late" notice is generated. There also are situations in which the
certificate does not contain an expiration date, in which case the function
looks at the issue date. If the vendor does not have certification (in
which case the CertType field is "Survey") we send one of two survey forms,
depending on the nature of the vendor's business. There are provisions for
sending second notices, as with certificates.
As I said, it all seems to work as it should (I seem to recall you providing
some advice when I was setting this up). The report is run about once per
month, and contains messages to several vendors. There are two categories
of vendors without certificates who receive surveys instead or requests for
certificates; I will call them Supply and Service vendors. If the vendor is
a Supply vendor the appropriate survey subreport needs to be part of the
fax; same with the Service vendors, for whom a different subreport needs to
appear. If the vendor has a certifcate there is no subreport, but just the
text of the request.
The function uses Select Case to generate the appropriate message, and to
show one or the other subreports, or neither. The subreports (surveys)
occupy all of the second page of the fax, by the way. I mention all of this
because I think the situation may differ from your initial understanding of
it, and I would appreciate any additional thoughts on the matter, or
observations about potential pitfalls.
I had some trouble with a blank page (i.e. a blank space where the subreport
would be) when the subreport is not visible until I figured out that I need
to set the Can Shrink property of the subreport control to Yes (along with
the Detail section's Can Shrink property).

 

[Marshall Barton]

Ok, that rules out the source object appproach.

The only drawback to having multiple subreports and making
all but one invisible is the time it takes to format all of
them. If you are only running the report a few times a
month or it the data is fairly simple, then time won't be a
significant issue.

Bottom line, I think that the approach you are using is
fine. If you should run into a specific problem, then we'll
try to address it when we know what it is.

 

[BruceM]

I will only be running the report about once a month, there are only a dozen
or so fields between the function and the report fields, and except for once
a year (all of the surveys, about 40, go out the same month) there will
probably be an average of fewer than 15 reports each month. For this
combination of reasons the formatting time is not much of a consideration.
Thanks for taking a look at this.

Ok, that rules out the source object appproach.

The only drawback to having multiple subreports and making
all but one invisible is the time it takes to format all of
them. If you are only running the report a few times a
month or it the data is fairly simple, then time won't be a
significant issue.

Bottom line, I think that the approach you are using is
fine. If you should run into a specific problem, then we'll
try to address it when we know what it is.
--
Marsh
MVP [MS Access]

The information is obtained automatically, based on criteria in the
records.
The source query looks at expiration dates on certificates and other
criteria. A calculated field combines the month and day from the
certificate with the current year, and compares that date to the
expiration
date. If that date is within 30 days of today, the record shows up in the
query.
After the records have been filtered, the user-defined function takes over
to generate the text of the fax. If the calculated date and the
expiration
date match, the certificate expires this year, and the message is "Expires
soon". If they do not match (e.g. because the certificate expires next
year), the message is "Confirmation needed". An option group selection on
the form is then marked to show the update has been requested. If it has,
and if the calculated expiration date is two weeks or more in the past, a
"Late" notice is generated. There also are situations in which the
certificate does not contain an expiration date, in which case the
function
looks at the issue date. If the vendor does not have certification (in
which case the CertType field is "Survey") we send one of two survey
forms,
depending on the nature of the vendor's business. There are provisions
for
sending second notices, as with certificates.
As I said, it all seems to work as it should (I seem to recall you
providing
some advice when I was setting this up). The report is run about once per
month, and contains messages to several vendors. There are two categories
of vendors without certificates who receive surveys instead or requests
for
certificates; I will call them Supply and Service vendors. If the vendor
is
a Supply vendor the appropriate survey subreport needs to be part of the
fax; same with the Service vendors, for whom a different subreport needs
to
appear. If the vendor has a certifcate there is no subreport, but just
the
text of the request.
The function uses Select Case to generate the appropriate message, and to
show one or the other subreports, or neither. The subreports (surveys)
occupy all of the second page of the fax, by the way. I mention all of
this
because I think the situation may differ from your initial understanding
of
it, and I would appreciate any additional thoughts on the matter, or
observations about potential pitfalls.
I had some trouble with a blank page (i.e. a blank space where the
subreport
would be) when the subreport is not visible until I figured out that I
need
to set the Can Shrink property of the subreport control to Yes (along with
the Detail section's Can Shrink property).

"Marshall Barton" wrote