Step through records?

Hello All

I have a fairly complex A97 mdb, which includes a macro which uses the
SendObject command to email a report to an individual. The report is based
on a query which takes as a parameter the value of a combobox selected by
the user - this is in fact the name of the individual that the report is
about. The individual's email address is also picked up from the current
form.

Is it possible for this macro to be run for a sequence of individuals in
turn - i.e. the report is generated and sent for one individual, then the
value of the combobox is amended to the next individual, and the report
re-generated and sent, and so on? The individual details are in table
[staffs], with the identifying parameter for the report's query being a
field called 'name' (yes, I know how terrible that is, but I inherited this
and it's firmly entrenched in the db and we seem to have got away with it!).
This table also includes a yes/no field 'current', and the report should
only be generated and sent to those individuals with a 'current' value of
True. So the 'step through' process could go in any order (say alphabetical)
but must be restricted to those records where 'current' = True.

I'm sure this can be done, but I can't see how to do it with a macro and my
vba isn't quite up to it.

Hope someone can help.
Thanks
Leslie Isaacs

Do you have the ability to change the email generating macro into a function?

I would then imbed the function into a simple query that list all "name"
where "current" = TRUE.



"Leslie Isaacs" wrote:

Hello All

I have a fairly complex A97 mdb, which includes a macro which uses the
SendObject command to email a report to an individual. The report is based
on a query which takes as a parameter the value of a combobox selected by
the user - this is in fact the name of the individual that the report is
about. The individual's email address is also picked up from the current
form.

Is it possible for this macro to be run for a sequence of individuals in
turn - i.e. the report is generated and sent for one individual, then the
value of the combobox is amended to the next individual, and the report
re-generated and sent, and so on? The individual details are in table
[staffs], with the identifying parameter for the report's query being a
field called 'name' (yes, I know how terrible that is, but I inherited this
and it's firmly entrenched in the db and we seem to have got away with it!).
This table also includes a yes/no field 'current', and the report should
only be generated and sent to those individuals with a 'current' value of
True. So the 'step through' process could go in any order (say alphabetical)
but must be restricted to those records where 'current' = True.

I'm sure this can be done, but I can't see how to do it with a macro and my
vba isn't quite up to it.

Hope someone can help.
Thanks
Leslie Isaacs

Hello Nixy

Thanks for your reply.

I do not know how to change the email generating macro into a function, but
even if I did surely then the report would combine the records for all the
names where "current" = TRUE (and how would it know which email address to
sent it to?), whereas what I need is individual reports (one per name)
emailed individually to the email address corresponding to each 'name'.

Apologies if I had not explained this well the first time!

Les


"Nixy" wrote in message
...
Do you have the ability to change the email generating macro into a
function?

I would then imbed the function into a simple query that list all "name"
where "current" = TRUE.



"Leslie Isaacs" wrote:

Hello All

I have a fairly complex A97 mdb, which includes a macro which uses the
SendObject command to email a report to an individual. The report is
based
on a query which takes as a parameter the value of a combobox selected
by
the user - this is in fact the name of the individual that the report is
about. The individual's email address is also picked up from the current
form.

Is it possible for this macro to be run for a sequence of individuals in
turn - i.e. the report is generated and sent for one individual, then
the
value of the combobox is amended to the next individual, and the report
re-generated and sent, and so on? The individual details are in table
[staffs], with the identifying parameter for the report's query being a
field called 'name' (yes, I know how terrible that is, but I inherited
this
and it's firmly entrenched in the db and we seem to have got away with
it!).
This table also includes a yes/no field 'current', and the report should
only be generated and sent to those individuals with a 'current' value
of
True. So the 'step through' process could go in any order (say
alphabetical)
but must be restricted to those records where 'current' = True.

I'm sure this can be done, but I can't see how to do it with a macro and
my
vba isn't quite up to it.

Hope someone can help.
Thanks
Leslie Isaacs

Les,

I don't know about amending the value of the combobox. But you could do
it like this....
- Make a continuous view form to list all of records where [Current]=-1
- Put an unbound textbox in the form footer section, with its Default
Value property set to 0 - let's say you name this textbox ReportsSent
- Put a GoToRecord/Next action in your macro after the SendObject action
- Put a SetValue action in the macro as well, with arguments set like
this...
Item: [ReportsSent]
Expression: [ReportsSent]+1
- Make another macro, using the RunMacro action to run the first macro
- In the Repeat Expression argument of the RunMacro action, enter:
[ReportsSent]=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

Untested, but it looks right to me :-)

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
Hello All

I have a fairly complex A97 mdb, which includes a macro which uses the
SendObject command to email a report to an individual. The report is based
on a query which takes as a parameter the value of a combobox selected by
the user - this is in fact the name of the individual that the report is
about. The individual's email address is also picked up from the current
form.

Is it possible for this macro to be run for a sequence of individuals in
turn - i.e. the report is generated and sent for one individual, then the
value of the combobox is amended to the next individual, and the report
re-generated and sent, and so on? The individual details are in table
[staffs], with the identifying parameter for the report's query being a
field called 'name' (yes, I know how terrible that is, but I inherited this
and it's firmly entrenched in the db and we seem to have got away with it!).
This table also includes a yes/no field 'current', and the report should
only be generated and sent to those individuals with a 'current' value of
True. So the 'step through' process could go in any order (say alphabetical)
but must be restricted to those records where 'current' = True.

I'm sure this can be done, but I can't see how to do it with a macro and my
vba isn't quite up to it.

Hope someone can help.
Thanks
Leslie Isaacs

Steve

Many thanks for this: I won't have time to try it out until tomorrow, but
I'll let you know how I get on.

Cheers
Les


"Steve Schapel" wrote in message
...
Les,

I don't know about amending the value of the combobox. But you could do
it like this....
- Make a continuous view form to list all of records where [Current]=-1
- Put an unbound textbox in the form footer section, with its Default
Value property set to 0 - let's say you name this textbox ReportsSent
- Put a GoToRecord/Next action in your macro after the SendObject action
- Put a SetValue action in the macro as well, with arguments set like
this...
Item: [ReportsSent]
Expression: [ReportsSent]+1
- Make another macro, using the RunMacro action to run the first macro
- In the Repeat Expression argument of the RunMacro action, enter:
[ReportsSent]=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

Untested, but it looks right to me :-)

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
Hello All

I have a fairly complex A97 mdb, which includes a macro which uses the
SendObject command to email a report to an individual. The report is
based on a query which takes as a parameter the value of a combobox
selected by the user - this is in fact the name of the individual that
the report is about. The individual's email address is also picked up
from the current form.

Is it possible for this macro to be run for a sequence of individuals in
turn - i.e. the report is generated and sent for one individual, then the
value of the combobox is amended to the next individual, and the report
re-generated and sent, and so on? The individual details are in table
[staffs], with the identifying parameter for the report's query being a
field called 'name' (yes, I know how terrible that is, but I inherited
this and it's firmly entrenched in the db and we seem to have got away
with it!). This table also includes a yes/no field 'current', and the
report should only be generated and sent to those individuals with a
'current' value of True. So the 'step through' process could go in any
order (say alphabetical) but must be restricted to those records where
'current' = True.

I'm sure this can be done, but I can't see how to do it with a macro and
my vba isn't quite up to it.

Hope someone can help.
Thanks
Leslie Isaacs

Steve

I have now tried this as you suggested, but when I try to run the 1st macro
I am getting an error with the SetValue action: with the following arguments
....

Item - [Forms]![form search cat]![ReportsSent]
Expression - [Forms]![form search cat]![ReportsSent] + 1

.... I get a type mismatch, even though my field [ReportsSent] on the form is
General Number.

I tried altering the expression to 3 and then I got "An error occurred while
referencing the object"

I have tried various other things but all to no avail.

Hope you can help
Many thanks
Les




"Steve Schapel" wrote in message
...
Les,

I don't know about amending the value of the combobox. But you could do
it like this....
- Make a continuous view form to list all of records where [Current]=-1
- Put an unbound textbox in the form footer section, with its Default
Value property set to 0 - let's say you name this textbox ReportsSent
- Put a GoToRecord/Next action in your macro after the SendObject action
- Put a SetValue action in the macro as well, with arguments set like
this...
Item: [ReportsSent]
Expression: [ReportsSent]+1
- Make another macro, using the RunMacro action to run the first macro
- In the Repeat Expression argument of the RunMacro action, enter:
[ReportsSent]=[Forms]![YourFormName].[RecordsetClone].[RecordCount]

Untested, but it looks right to me :-)

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
Hello All

I have a fairly complex A97 mdb, which includes a macro which uses the
SendObject command to email a report to an individual. The report is
based on a query which takes as a parameter the value of a combobox
selected by the user - this is in fact the name of the individual that
the report is about. The individual's email address is also picked up
from the current form.

Is it possible for this macro to be run for a sequence of individuals in
turn - i.e. the report is generated and sent for one individual, then the
value of the combobox is amended to the next individual, and the report
re-generated and sent, and so on? The individual details are in table
[staffs], with the identifying parameter for the report's query being a
field called 'name' (yes, I know how terrible that is, but I inherited
this and it's firmly entrenched in the db and we seem to have got away
with it!). This table also includes a yes/no field 'current', and the
report should only be generated and sent to those individuals with a
'current' value of True. So the 'step through' process could go in any
order (say alphabetical) but must be restricted to those records where
'current' = True.

I'm sure this can be done, but I can't see how to do it with a macro and
my vba isn't quite up to it.

Hope someone can help.
Thanks
Leslie Isaacs

Les,

I would recommend running the macro from an event (Click of a command
button perhaps) on the [form search cat] form. Is that possible? If
so, then you can use the Item and Expression as I previously suggested.

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
Steve

I have now tried this as you suggested, but when I try to run the 1st macro
I am getting an error with the SetValue action: with the following arguments
...

Item - [Forms]![form search cat]![ReportsSent]
Expression - [Forms]![form search cat]![ReportsSent] + 1

... I get a type mismatch, even though my field [ReportsSent] on the form is
General Number.

I tried altering the expression to 3 and then I got "An error occurred while
referencing the object"

Steve

I must have done something stupid, but now when I run the first macro it is
failing on the GoTo Next action: the error message is saying that I can't
use the GoToRecord action on an object in design view. But the form is not
in design view!

I have pasted below the module that I got when I saved the first macro as a
module (actually, the conversion initially put the '+1' on its own three
lines below the rest of the line where it belonged, so it was in red as a
syntax error. I had to delete the carriage-returns to bring it back to the
correct place)

Hope you can help
Les


Option Compare Database

'------------------------------------------------------------
' view_letter
'
'------------------------------------------------------------
Function view_letter()
On Error GoTo view_letter_Err

DoCmd.OpenReport "rpt selected letter step", acViewPreview, "", "",
acNormal
DoCmd.GoToRecord , "", acNext
Forms![form search cat]!ReportsSent = Forms![form search
cat]!ReportsSent + 1


view_letter_Exit:
Exit Function

view_letter_Err:
MsgBox Error$
Resume view_letter_Exit

End Function


"Steve Schapel" wrote in message
...
Les,

I would recommend running the macro from an event (Click of a command
button perhaps) on the [form search cat] form. Is that possible? If so,
then you can use the Item and Expression as I previously suggested.

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
Steve

I have now tried this as you suggested, but when I try to run the 1st
macro I am getting an error with the SetValue action: with the following
arguments ...

Item - [Forms]![form search cat]![ReportsSent]
Expression - [Forms]![form search cat]![ReportsSent] + 1

... I get a type mismatch, even though my field [ReportsSent] on the form
is General Number.

I tried altering the expression to 3 and then I got "An error occurred
while referencing the object"

Les,

I don't think you should be opening the report in Preview.

And as I said in my earlier post, what event are you trying to run this
on? Isn't it an event on the [form search cat] form? So why are you
not using the approach I suggested for the SetValue action?...
Item: [ReportsSent]
Expression: [ReportsSent]+1

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
Steve

I must have done something stupid, but now when I run the first macro it is
failing on the GoTo Next action: the error message is saying that I can't
use the GoToRecord action on an object in design view. But the form is not
in design view!

I have pasted below the module that I got when I saved the first macro as a
module (actually, the conversion initially put the '+1' on its own three
lines below the rest of the line where it belonged, so it was in red as a
syntax error. I had to delete the carriage-returns to bring it back to the
correct place)

Steve

Thanks for your continued help.

Ultimately I want to be able to send a series of emails to a series of
people, with each email having as an attachment a particular report that
would have to be regenerated for each one. The report would take a parameter
from [form search cat] - the first report would take the value of the field
called 'name' (!) from the first record, then the second report would take
this value from the second record, etc.

My reason for altering the action from SendObjet to OpenReport (in preview)
is simply because I do not have a supply of email addresses that I can use
to test this process: I realise I could just use my own email address, but I
didn't think that it would make a difference what action I wanted to perform
for each record in [form search cat]: why does it? The report is in fact
opening OK in preview - it's the next action (GoToRecord) that is failing.

Again, thanks for your help: hope you don't give up on me!
Les



"Steve Schapel" wrote in message
...
Les,

I don't think you should be opening the report in Preview.

And as I said in my earlier post, what event are you trying to run this
on? Isn't it an event on the [form search cat] form? So why are you not
using the approach I suggested for the SetValue action?...
Item: [ReportsSent]
Expression: [ReportsSent]+1

--
Steve Schapel, Microsoft Access MVP


Leslie Isaacs wrote:
Steve

I must have done something stupid, but now when I run the first macro it
is failing on the GoTo Next action: the error message is saying that I
can't use the GoToRecord action on an object in design view. But the form
is not in design view!

I have pasted below the module that I got when I saved the first macro as
a module (actually, the conversion initially put the '+1' on its own
three lines below the rest of the line where it belonged, so it was in
red as a syntax error. I had to delete the carriage-returns to bring it
back to the correct place)