By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,871 Members | 2,368 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,871 IT Pros & Developers. It's quick & easy.

Macro that e-mails one report using a different query for each rep/e-mail

P: n/a
I have created a database that brings in a new table weekly. I then
made a separate query for each sales rep and a separate report for each
rep. (The reports are all identical but call different queries.)
Lastly, I created a macro that sends the reports to the respective rep
via e-mail with the push of a button.

I am pleased with myself for getting this far but I realize the next
step is figure out how to use one report (so I don't have to change 10
when I need to make changes)that calls the different queries.

I would be happy if the macro had ten lines in it that call the report,
each time with a different query, obviously then sending it to the
appropriate rep.

It seems to me there must be a simple solution.

Nov 13 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
On 21 Dec 2004 12:06:31 -0800, "Chasing Gates" <ja***@dolomiteusa.com>
wrote:
I have created a database that brings in a new table weekly. I then
made a separate query for each sales rep and a separate report for each
rep. (The reports are all identical but call different queries.)
Lastly, I created a macro that sends the reports to the respective rep
via e-mail with the push of a button.

I am pleased with myself for getting this far but I realize the next
step is figure out how to use one report (so I don't have to change 10
when I need to make changes)that calls the different queries.

I would be happy if the macro had ten lines in it that call the report,
each time with a different query, obviously then sending it to the
appropriate rep.

It seems to me there must be a simple solution.

Hi
You can call the openreport macro with a where condition which selects
the rep the report will be for, see the help file if you are lucky
enough to have one, here is an extract
Where Condition: A valid SQL WHERE clause (without the word
WHERE) or expression that Microsoft Access uses to select records from
the report's underlying table or query. If you select a filter with
the Filter Name argument, Microsoft Access applies this WHERE clause
to the results of the filter.To open a report and restrict its records
to those specified by the value of a control on a form, use the
following expression:[fieldname] = Forms![formname]![controlname on
form]The fieldname argument is the name of a field in the underlying
table or query of the report you want to open. The controlname on form
argument is the name of the control on the form that contains the
value you want records in the report to match.
Note The maximum length of the Where condition argument is 256
characters. If you need to enter a more complex SQL WHERE clause
longer than this, use the OpenReport method of the DoCmd object in
Visual Basic instead. You can enter SQL WHERE clause statements of up
to 32,768 characters in Visual Basic.

David

Nov 13 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.