473,574 Members | 5,361 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 1895
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
4197
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() ...
3
2585
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...
0
1186
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...
0
5355
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
1463
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...
4
3820
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,...
2
1870
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
3120
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...
0
1646
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
7738
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8079
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8258
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7833
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...
1
5635
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...
0
3756
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...
1
2254
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
1
1359
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1081
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.