Step through records?

  • Thread starter Thread starter Leslie Isaacs
  • Start date Start date
L

Leslie Isaacs

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.
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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

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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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

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 said:
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 said:
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"
Click to expand...
Click to expand...
 
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

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 said:
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 said:
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)
Click to expand...
Click to expand...
 
Leslie said:
... The report is in fact
opening OK in preview - it's the next action (GoToRecord) that is failing.
Click to expand...

Maybe because the focus is now on the report preview, and no longer on
the form, and the GoToRecord doesn't make sense within the context of a
report preview?
 
Steve
OK, I understand that. Pesumably then I need to change the focus back to the
form - is this possible?
Thanks again
Les
 
Les,

Yes, it is possible. But we're slowly disappearing down a burrow here.
Trying to workaround something that you don't even intend to do in the
finished scenario. Forget the print preview. Set up the thing to do
what you want it to do, and handle any problems that arise with that.
 
Steve

OK, I take your point.
I ave now done exactly as you said.

The first macro, called [send letter], has:
a.. the SendObject action. The object is a report that takes as a parameter (in fact the email address) the value in one of the fields in [form search cat] - this is the form that is open in continuous form view, with textbox 'ReportsSent' in the form footer.
b.. the GoToRecord action, with the Record argument set to Next
c.. the SetValue action, with Item = [ReportsSent] and Expression = [ReportsSent] + 1
The second macro, called [send all letters] has:
a.. the RunMacro action, with the macro name = send letter and Repeat Expresssion = [Forms]![form search cat].[ReportsSent]<=[Forms]![form search cat].[RecordsetClone].[RecordCount] (without the [Forms]![form search cat]. before the [ReportsSent] on the left hand side, I got a message about not being able to find the name [ReportsSent] that I entered in the expression)
When I run the second macro [send all letters] the report corresponding to the first record in [form search cat] is emailed OK, but then I get the message about not being able to use the GoToRecord action or method on an object in design view. Not surprisingly I get the same result when I run the first macro [send letter].

Do you think I do need to set the focus back to the form - and if so, how would I do this.

Many thanks for your continued help: this really will make a diference here if I can get it to work!

Les
 
Steve

I've cracked it!
I found the SelectObject action, which sets the focus back to the form, and
after adding the [Forms]![form search cat]. before the [ReportsSent] in the
SetValue action everything worked!

Many thanks for all your help - I've learned a bit more from this.

Les
 
Les,

I'm very happy to know that it is working for you. It is still a
mystery to me why you need to use the SelectObject action there, or to
specify the [Forms]![form search cat]. It works fine for me without
these steps. The macro is being run from an event on the [form search
cat] form, right? But anyway, as long as it works, it has to be progress.
 
Back
Top