473,701 Members | 2,384 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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
1 1899
On 21 Dec 2004 12:06:31 -0800, "Chasing Gates" <ja***@dolomite usa.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
4201
by: Steve | last post by:
I am using Access 2000 and I have a DB that is currently running four different queries from some Forms and subforms. These queries each have a date field that are seperate from each other that are from those said forms. Currently I have setup the criteria for these queries to prompt a user to enter a month # Query Field: Month() Criteria:
3
2590
by: Prakash Wadhwani | last post by:
Hi !! I have been browsing around but have not been able to find a simple, lucid solution ... or maybe I'm just too confused. I know this has been asked before by many ... but pls bear with me. Step 1: I want to place a button which outputs my report as a snapshot to a specific folder. What is he VBA code to acomplish this ? Step2: I want to place a button which emails the above generated report using
0
1191
by: Chasing Gates | last post by:
I currently have the tables set up and a separate query for each individual sales rep. I then created 10 reports and a macro that sends out the ten reports via e-mail to the correct reps. I need to clean this up and have only one report that uses each different query. Should I be using the Where clause? IF so how? I have tried a dozen different ways. The problem (and I knew this when I created the reports) is that one change then...
0
5381
by: ghadley_00 | last post by:
MS Access Create form / report with multiple pages using different background images Hi, Would like to have users fill out a multipage form, and then click a print button, which pulls up the info just entered for a particular record and print out multiple pages of forms, each page having a different image as background.
2
1466
by: kaosyeti | last post by:
hey... i have a quarterly report that i'm working on where each of the 12 or so pages is completely different from the other. all are based on basically the same info (ergo, the same query) but the layout of the controls are wildly different from each other. right now, i've created each page of this report as its own report, just to get me started somewhere. what's the best way to open these 12 reports so that they all show up at the...
4
3826
by: Jimmy | last post by:
I have a form with a command button on it that is supposed to open up a report and use a query to populate it. DoCmd.OpenReport "rptMailingList", acViewPreview, "qryMailingListChristmas" qryMailingListChristmas sql: SELECT DISTINCTROW tblClients.BillingName, tblClients.AccountType, tblClients.BillingAddress, tblClients.Contact, tblClients.BillingCity, tblClients.BillingState, tblClients.BillingZip,
2
1878
AccessIdiot
by: AccessIdiot | last post by:
Hello all, I have a report that is based on a crosstab query. One of the fields in the crosstab query is based on a query. It is a coded value. When I look at the crosstab query it displays the value. But when I look at the report I get the coded value. For example, in the table it is; 1 apple 2 orange 3 banana
1
3140
by: giovannino | last post by:
Dear all, I did a query which update a sequence number (column NR_SEQUENZA) in a table using a nice code (from Trevor !). 1) Given that I'm not a programmer I can't understand why numbering doesn't start always, running more times the update query, from the same starting parameter assigned (1000000000). It seems to me that it takes memory last number calculated and running prox update it starts from last table row updated. I need...
0
1651
by: saifulhaque | last post by:
Dear All How we can assign column of table to Crystal report using sql query. Means sq l query is varying w.r.t purpose. Ex: Some times select * from table or Some times select column1,column2 from table. Like that
0
8733
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
8975
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
7822
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6569
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5904
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4410
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
4662
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3102
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2398
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.