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

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

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Leslie Isaacs wrote:
> > 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
> >
> >

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?

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
> 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
>

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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?
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Leslie Isaacs wrote:
> > 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
> >

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.

--
Steve Schapel, Microsoft Access MVP


Jack Isaacs wrote:
> 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" <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
>
>>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?
>>
>>--
>>Steve Schapel, Microsoft Access MVP
>>
>>
>>Leslie Isaacs wrote:
>>
>>>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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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.
>
> --
> Steve Schapel, Microsoft Access MVP
>
>
> Jack Isaacs wrote:
> > 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" <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> >
> >>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?
> >>
> >>--
> >>Steve Schapel, Microsoft Access MVP
> >>
> >>
> >>Leslie Isaacs wrote:
> >>
> >>>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
> >>>
> >
> >
> >

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

--
Steve Schapel, Microsoft Access MVP

Jack Isaacs wrote:
> 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

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] ([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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Jack Isaacs wrote:
> > 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.

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.

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
> 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] ([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
>