SendObject Macro

[waldridge]

My sendobject macro only sents the first record in my table. I've got the
DCount ("Amount","name of query")>0 on each line of my macro. Why won't it
go to the next record it find in the table? I'm sending a report which in
turn points to the query of the table. Any help is appreciated.

 

[Steve Schapel]

Wanda,

I assume you mean that the DCount("Amount","name of query")>0 expression is
in the Condition for the macro? Therefore, what are the Actions in the
macro?

It sounds like the query is only returning one record. Can you go to the
design view of the query, select the SQL view, and copy/paste what you see
there into your reply here? Thanks, that will help us to understand the
problem.

 

[waldridge]

Thanks Steve. You are correct about the condition in the macro. I've got
for each query of vendors, this condition for each vendor just like this one.
The SQL view of the query is below. Thanks so much for your help.

SELECT sheet1.[Remit Vndr], *
FROM sheet1
WHERE (((sheet1.[Remit Vndr])="0000003154"))
ORDER BY sheet1.[Remit Vndr];

 

[Steve Schapel]

Wanda,

Thanks for the further information.

The other thing that would help is if you could let us know what are the
Actions in the macro. Thanks.

So, the query you showed selects the records that are related to vendor
0000003154. Do you mean you have a separate query set up in your database
for each vendor? So what exactly is your macro supposed to be doing?

 

[waldridge]

Steve,

That's right. I've got one query for each vendor and a report for each
vendor that points to the query. I've got two actions for each vendor.
First is "openreport". The second is "sendobject". It appears that when it
reaches the first vendor that does not have information, the macro stops. I
do have an event procedure on "no data" in the property sheet of the report
that says: Cancel = true. I thought this would go to the next record, am I
wrong?
--
Wanda

Wanda,

Thanks for the further information.

The other thing that would help is if you could let us know what are the
Actions in the macro. Thanks.

So, the query you showed selects the records that are related to vendor
0000003154. Do you mean you have a separate query set up in your database
for each vendor? So what exactly is your macro supposed to be doing?

--
Steve Schapel, Microsoft Access MVP

Thanks Steve. You are correct about the condition in the macro. I've got
for each query of vendors, this condition for each vendor just like this
one.
The SQL view of the query is below. Thanks so much for your help.

SELECT sheet1.[Remit Vndr], *
FROM sheet1
WHERE (((sheet1.[Remit Vndr])="0000003154"))
ORDER BY sheet1.[Remit Vndr];

.

 

[Steve Schapel]

Wanda,

From your description, I see no purpose for the OpenReport actions in your
macro. Unless I am missing something here, these actions can be removed.

The Cancel=True on the No Data event of the report will have no bearing on
the SendObject action. As I have never done anything quite like this
before, I am not sure why it is not proceeding to the next action. My
suggestion here is to put a Condition in your macro for each SendObject
action, that looks like the equivalent of this:
DCount("*","NameOfYourQuery")>0
This will ensure that the SendObject action for each report will only be
executed if there is data in the report.

 

[waldridge]

Thanks so much, Steve. I already have this condition in my macro. It's
DCount("amount1","A&B Solutions")>0 with "amount1" being the voucher
amounts and "A&B Solutions" being my query. When I take out the OpenReport
command, it gives the error "this refers to a report that is misspelled or is
not open". Now the strange thing is, if I put the vendors that have records
up at the top, it will go from one to the next so it would appear that it's
the vendors that do not have records that are causing my issue. Also, would
it make a difference if the table is a linked excel spreadsheet? Thanks so
much, Steve.

 

[Steve Schapel]

Wanda,

You would have to put the Condition for *every* SendObject action in your
macro. And the query you used in the Condition expression would need to be
the query related to the vendor whose report you are sending with that
specific action. What is the "A&B Solutions" query? Is that the query that
the report is based on? You told me you had a separate report and separate
query for each vendor (which is not the way I weould do it, incidentally,
but I decided to just run with ewhat you have). So yes, I realised it is
the vendors with no records that is causing the problem - and the Condition
to only run that action if there are records for that vendor should take
care of that.

I am flummoxed by the error message you are getting about the report. I
can't see at the moment what that refers to.

Maybe we should take a step back for a moment, and get you to give a full
listing of all actions, with details of the arguments, for your macro.

 

[waldridge]

Steve,

I'm not near the expert you are, I can only wish. . . I do have this
condition for every SendObject condition for every vendor. The query for
this particular one is "A&B Solutions". The "amount1" in a field in every
query so my conditions for each vendor only vary according to the appropriate
query for that particular vendor. I'm not sure why I get the error message
either. Basically, I have a linked table which changes daily and I'm wanting
to send emails only to those in the list. I have a query for each vendor
with the criteria being the vendor number which is unique for each vendor but
there could be more than one record in the table for a vendor. The report is
using the query to filter only those records pertaining to that vendor. The
Macro has an "OpenReport" action and the "SendObject" action which has the
condition above. That's basically it. The only other wierd thing I"m doing
is on the "SendObject" action, I have the following: =[Reports]![A&B
Solutions]! to get the URL from the report. (The Query and report have
the same name)

 

[PieterLinden via AccessMonster.com]

Steve,

That's right. I've got one query for each vendor and a report for each
vendor that points to the query. I've got two actions for each vendor.
First is "openreport". The second is "sendobject". It appears that when it
reaches the first vendor that does not have information, the macro stops. I
do have an event procedure on "no data" in the property sheet of the report
that says: Cancel = true. I thought this would go to the next record, am I
wrong?

Wanda,

[quoted text clipped - 20 lines]

.

Wait. You have a completely different report for each vendor? Or you're
just trying to open the same filtered report for each vendor with records
for the report and then e-mail that report to the vendor? If that's the case,
then you can't do it the way you are proposing. You need a recordset to do
that.

Here are a few links that should help:
http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm
(Sorry, I thought Allen Browne had one... but Tony's should answer all your
questions...)

 

[waldridge]

Ok. I finally got the SendObject to work. Evidently I had some spaces where
they shouldn't belong. However, I still have to do the issue of the
"OpenReport". If I don't have this before each "SendObject" action, I get an
error. Anyone know why? --
Wanda

Steve,

That's right. I've got one query for each vendor and a report for each
vendor that points to the query. I've got two actions for each vendor.
First is "openreport". The second is "sendobject". It appears that when it
reaches the first vendor that does not have information, the macro stops. I
do have an event procedure on "no data" in the property sheet of the report
that says: Cancel = true. I thought this would go to the next record, am I
wrong?

Wanda,

[quoted text clipped - 20 lines]

.

Wait. You have a completely different report for each vendor? Or you're
just trying to open the same filtered report for each vendor with records
for the report and then e-mail that report to the vendor? If that's the case,
then you can't do it the way you are proposing. You need a recordset to do
that.

Here are a few links that should help:
http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm
(Sorry, I thought Allen Browne had one... but Tony's should answer all your
questions...)

 

[Steve Schapel]

Wanda,

Do you have any other actions in the macro aside from the OpenReport
actions and the SendObject actions?