Convert 'Open Report' commands to 'Output to' commands

[Leslie Isaacs]

Hello All

I am using A97 & Windows 2000.

I have four macros that each contains about 40 'Open Report' commands, each
with a particular 'condition', and with the View parameter for each set to
Print.
I need to convert all these to 'Open Report' commands, each with the same
'condition', to generate the corresponding report as a Snapshot file, with
the Output File parameter left blank so that it can be specified by the
user.

The condition for each command relates to the value in a corresponding
yes/no field in the current form - i.e. only print/output the report if the
corresponding value = True.

Can the conversion of all these commands be done automatically with VBA, or
must I go through them all manually?

Alternatively, it has occurred to me that the whole process could possibly
be done by a VBA function. Part of the difficulty I would have with this
would be cycling through all the possible reports - and all the
corresponding yes/no fields in the current form. Is this an avenue I should
go down, amd if so any help with the VBA would be extremely welcome!

Hope someone can help.

Many thanks
Leslie Isaacs

 

[Steve Schapel]

Leslie,

I think you could only automate one aspect of the process of making
OutputTo macros with similar arguments to your OpenReport macros. This
is setting up the basic structure of the macros, with the Conditions in
place. The way to do this would be to copy/paste the existing macros.
After this, though, you would have to change all the OpenReport actions
to OutputTo actions, and enter all the arguments "manually".

If I understand you correctly, to do the same process in a VBA procedure
is also likely to be somewhat tedious, but can be done something like
this...
If Me.NameOfFirstCheckbox Then
DoCmd.OutputTo acReport, "FirstReport", "SnapshotFormat(*.snp)", ,
False
End If
If Me.NameOfSecondCheckbox Then
DoCmd.OutputTo acReport, "SecondReport", "SnapshotFormat(*.snp)",
, False
End If
..... etc, repeated for each checkbox/report combination

 

[Leslie Isaacs]

Hello Steve

Thank you for your reply.

I am under the impression (for some unknown reason!) that it would be better
to run the process as a VBA procedure than as a macro: would you agree?
Assuming so, if I named all the checkboxes "1", "2", ..."200" (say), could
the procedure cycle through them with some kind of 'While' loop, or a 'For
count = 1 to 200' loop? Perhaps I need to create a table of the report names
and corresponding checkbox numbers, to refer to? Or , if the checkboxes
themselves were named exactly the same as their corresponding reports, could
I somehow use that name to output the corresponding report - if, of course,
the value is True?

I'm casting back here to the days when I learnt a bit about programming (in
Basic, and AlgolW I think it was), and I seem to think that all this should
be reasonably 'do-able'. I would be very grateful for your ideas and any
help with the coding.

If I were to use the code you suggested, how exacly would I refer to the
checkboxes - would I need to include the form name, or would
Me.NameOfFirstCheckbox be enough?

Many thanks
Les

 

[Steve Schapel]

Les,

Is the requirement here to only output one report at any given time,
i.e. the one with its corresponding checkbox ticked? Or is the
requirement to output multiple reports, i.e. all with their
corresponding checkboxes ticked, so in theory all 200 reports could get
outputted at once?

 

[Jack Isaacs]

HelloSteve

The requirement is to output multiple reports - so yes, in theory, all 200
reports could get outputted at once. In practice, however, no recipient is
likely ever to get more that 10 reports: 5-8 is normal.

I have tried to come up with the code to output all the reports as multiple
attachments to a single email, but this has proved too difficult (so far) so
at least for the present time I will output each of them as individual
attachments to individual emails. I know this means that the recipients will
receive multiple emails, but if it's only 5-8 they shouldn't mind!

Hope that makes it clearer!

Thanks for your continued help.
Les

 

[Steve Schapel]

Les,

I definitely wouldn't have 200 checkboxes on a form, so that a user can
tick 5-8 of them in order to select which reports to print. There are
multipe alternative approaches, and deciding between them requires a
deeper understanding of what you're doing that what I've got right now.
But here are a couple of ideas that would be relatively easy to
implement without major changes to your overall concept. First, make a
table to list the names of all the reports, and then put a multi-select
listbox on your form with this table as its Row Source. Code to
retrieve the reports from the selections on the listbox is fairly
standard. This is still clunky, but at least you would take up less
space on the form. Another idea is to use a continuous view subform,
with a combobox based on the ReportNames table, for entry of the names
of the reports for output. Because of the combobox's AutoExpand
capability, the entry of the report names will be quick. Hope that helps.

 

[Jack Isaacs]

Steve

You appear to be my 'mentor' on various fronts at the moment, and I am
extremely grateful!

Having said that there are up to 200 reports available, some basic
investigation reveals that about half of them are not in use, and I will
consider removing them from the system (essentially they have been replaced
by improved versions, and my housekeeping has been poor!). Of the reports in
use, they logically divide into 5 very distinct groups, and so the check
boxes are displayed on 5 pages of a tab control. Given this - and my
intention to prune the list - I think I will keep to the check-box selection
format. I do understand and appreciate your multi-select listbox suggestion
though.

I am keen to establish whether it would be possible to cycle through the
check boxes, with a single command in the loop that would (if the check box
value was True) Send (i.e.email) the relevant report. It seems to me the
check box name would have to include a 'count' element (to facilitate the
loop process) and also the name of the corresponding report (in order to
'feed' the argument of the Send command). So, in English (as opposed to
VBA!) I see something like:

Reports named:
rpt summary
rpt details
etc

Checkboxes named:
1rpt summary
2rpt details
etc
(only report-selection check boxes would start with a number)

Then:
For count = 1 to 200 (soon to be 100!)
If checkbox count&"*" = True then Send (some expression that removes the
'number' part from the check box name!)
Next count

Can this be done??

Many thanks once again.
Les.

 

[Steve Schapel]

Les,

Here is an idea to get you started...

Dim ctl As Control
Dim ctlName As String
For Each ctl In Me.Controls
If ctl.Properties("Name") Like "rpt*" Then
ctlName = ctl.Properties("Name")
If Me(ctlName) = -1 Then
DoCmd.OutputTo acOutputReport, ctlName, "SnapshotFormat(*.snp)"
End If
End If
Next ctl

 

[Leslie Isaacs]

Steve

Many thanks for that - I'm sure I can use that code.

Is it possible for the code to specify that only those controls that are on
a specified tab control should be checked for their value and name?
The reason I ask is that unfortunately not all the report names currently
start with "rpt" (not my fault - I inherited this!), and if I have to rename
them all I will also have to edit the click-events of lots of buttons, and
other code elsewhere, that call up the reports in other ways. If the 'For
Each' statement can be restricted to the controls on the tab control (all of
which are checkboxes) I won't have to do any of this - I will just have to
ensure that the checkboxes are named exactly the same os the corresponding
reports (which they already are).

Also, I need to use the VBA equivalent to the SendObject command (as opposed
to the OutputTo command), and in so doing I need to specify the following
parameters:

Object type = Report
Object name = (presumably) ctlName
Output format = Snapshot
To = [Forms]![frm practices]! ([email] is the name of the text box
containing the email address)
Cc and Bcc (none)
Subject = [Forms]![frm x main]![month name] &" -" & ctlName
Message = "If you are unable to open the attached file, download Snapshot
View from
http://www.microsoft.com/downloads/details.aspx?FamilyID=b73df33f-6d74-423d-
8274-8b7e6313edfb&displaylang=en"
Edit message = No
Template file (none)

How would I specify all this in the VBA DoCmd line?

Thank you very much for all this help: I'm learning a lot from it (I hope!).

Les

 

[Steve Schapel]

Les,

No, you can't identify the controls according to which tab control page
they are located.

As for specifying the arguments for the SendObject method, when you type
DoCmd.SendObject ,space> into the VB Editor, the "intellisense" should
kick in and show you where to put what. Or, you can highlight the
SendObject and then press F1. Give it a go, and then post back if you
need specific helpo with any aspect.