Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  

News      User Comments     History     Notify Me

5/28/2013 9:59:39 PM
Microsoft Access Expert 6
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Microsoft Access Expert Level 6 is 1 hour, 18 minutes long and focuses on sending mass mail from your Access database. We'll learn how to print collection letters (with separate letters for those who are 30, 60, or 90 days late), generic letters to all active customers, letters to customers based on what category they're in, and more. Topics include:

- Send Mass Mail from Access
- Collection Letters
- Automatic Date Ranges
- New Report Properties
- Force New Page
- Multiple Letter Templates
- Letters to All Active Customers
- Allow Edits vs. Locked Fields
- Send To Customers by Category

Click here for more information on Access Expert Level 6, including a course outline, sample videos, and more. This course was recorded using Access 2013, but is also valid for Access 2007 and 2010 users. This class follows Expert Level 5.

Permanent Link
Course Link: Microsoft Access Expert 6
Keywords: access expert 6 letter writer mass mail collection letters force new page
Page Tag: whatsnew
Post Reply

LetterMarketingR Comment from Mike Holmes @ 5/13/2016
I have made a LetterMarketingR that will have a choice of 6 letters to choose from.  When I add my button to my LetterF to generate the preview report of the marketing letter, it generates all 6 letters for all clients.  I just want to have it generate the letter that I have showing on the form.  Where should I look to limit the report to the letter shown on the LetterF?

Reply from Alex Hedley:

You need to create a Query that looks for 1 record then open your Report based on that Query.
Show Just This Thread        Post Reply
Zip Code Comment from Mike Holmes @ 4/16/2016
If you add a missing address information, like a zip code, on this query, will it be found across all instances of that customer's table, forms, and queries going forward?

Reply from Alex Hedley:

I'm not sure what your question is, do you have an example?
Show Just This Thread        Post Reply
Could you have also made the LetterActiveR with an Comment from Joe Beniacar @ 1/23/2016
Could you have also made the LetterActiveR with an SQL statement based on the CustomerT with a WHERE condition about IsActive being True (If you didn t want to make a separate Query)?

Reply from Alex Hedley:

Yes you could, but it's sometimes neater to have a saved Query, and easier to see what you have set, if you have to go into the RecordSource and look into the SQL it gets a little bit fiddly.
A Query doesn't take up much memory and although your db can grow in size with the number of objects, Rich does teach in later classes how to use SQL strings or VBA to slim down the number of objects in your Navigation Pane.

LetterActiveR is based on CustomersActiveQ which is CustomerT with a Criteria of IsActive=True
Show Just This Thread        Post Reply
Table Design Comment from Joe Beniacar @ 1/23/2016
Whenever you build a new Table - do you just think about the fields that relate to its main topic?   What about the notion of relationships, and possibly bringing in fields from other tables that could be foreign keys - and also, potentially sending fields you make in this new Table BACK to other tables?   Now that I have a slightly better grasp on Access   this seems to be a pretty daunting process  - How do your approach building a new Table?

Reply from Alex Hedley:

The Relationships Seminar would be a handy one to take to get a better understanding of this.

In Beginner Level 1 Rich discusses Database Design and advises that you should step away from the computer and write down what you need on Index cards.
You can then plan out what data you have and how it relates to each other.
You have to take into account what data you want to store, and see how it relates to other items in your db, do you have an example we could run through of what you want make?
Show Just This Thread        Post Reply
Control Source Comment from Joe Beniacar @ 1/23/2016
How do calculated fields or text you add in a Form/Report control's "Control Source" property not also become new fields in the Table you built it off of   if that s kind of part of the Control Source s definition...?

Reply from Alex Hedley:

See this MS Article

Setting | Description
A field name
The control is bound to a field in a table, query, or SQL statement. Data from the field is displayed in the control. Changes to the data inside the control change the corresponding data in the field. (To make the control read-only, set the Locked property to Yes.) If you click a control bound to a field that has a Hyperlink data type, you jump to the destination specified in the hyperlink address.

An expression
The control displays data generated by an expression. This data can be changed by the user but isn't saved in the database.

Since you use the "=" for an Expression Access then knows it's not a BOUND Column which saves data back to your Table.
Show Just This Thread        Post Reply
Force New Page Comment from Joe Beniacar @ 1/23/2016
Is setting Force New Page: "After Selection" in the Detail section equivalent to making a Report a  Single Report  (since there doesn t seem to be this type of option for Reports)?

Reply from Alex Hedley:

What do you mean by Single Report? i.e. a dataset that only contains 1 record?
Rich wrote a Tip on this as it didn't have this option in the Wizard.
The idea is to create a Query that returns on 1 record then base your Report on that Query, therefore you only print 1 record and not them all.

Beginner Level 1
11. Customer Reports (14:08)
Report Wizard for Single Report
Show Just This Thread        Post Reply
Gap Comment from Norma Cerpa @ 12/3/2015
How come on the video, on the second letter, in the address section, the company name has been omitted, yet there is no blank space in its place (4:27-4:30). The gap is closed. I got the blank space.

Reply from Alex Hedley:

Which video is this?
Show Just This Thread        Post Reply
Move Controls Comment from Cheryl @ 10/30/2015
(At about 8:35) How do you move the OrderID, AmountDue,and DueDate control as a group without the labels moving?

Reply from Alex Hedley:

They might not have their labels attached to the controls anymore.
Show Just This Thread        Post Reply
Report Comment from Tim Florio @ 6/12/2015
Greetings Richard, thanks for the great learning experience. Expert Level 6 Lesson 4, Everything works as indicated in your video with exception of the >30 and >60 day reports. The <30 report is as expected. The "BeginDaysAgo" and "EndDaysAgo" indicate the proper number and the "Begin Date" and "End Date" indicate the proper dates. Just having a problem with the >30 and >60 day reports.No customers, addresses or order information. I checked "OrderT" and the dates seem okay.

Reply from Alex Hedley:

Is there data that goes that far back?
Show Just This Thread        Post Reply
Track Users Comment from Ben Eynon @ 4/26/2015
I need to track who enters information into the database, as multiple users will have access to it and the information is very sensitive. Do you have a class on this? If not can you help me? Thanks

Reply from Alex Hedley:

Check out the Security Seminars

Show Just This Thread        Post Reply
Print only one selected record Comment from Ben Eynon @ 4/26/2015
How can you print only one selected record from a form and/or do the same thing on a report? I need to do this because lots of information I'm designing is personal and should not be printed in lists. This is also needs to print specific information so it can be stored in an individual's personal file. Thanks.

Reply from Alex Hedley:

Create a Record that filters to your single person and base your Report on that.

Show Just This Thread        Post Reply
Default Value Comment from Alexander Adamson @ 4/14/2014
Hi Richard,

My question relates to lesson 3, expert 6, time index 09.00.

Could you please supply further clarification as to why the default value property has to be used instead of the control source property to attach a formula to the 'BeginDate' text box.

I don't understand the message supplied by Access 'Control can't be edited, it's bound to the expression'Date()-30'-can you explain what this means please.

Many thanks

Reply from Richard Rost:

Default Value allows you to START with one value, but change it if needed. If you set a Control Source value, you're stuck with just that... and get a "control can't be edited" message.

Show Just This Thread        Post Reply
Microsoft Access Expert 6 Comment from Chris S @ 2/28/2014
Hi Richard,
This video was really helpful. I'm actually creating collection letters myself and have designed my letters based on what I've seen here. I've also included a section for each customer that lists what invoices are past due and their amounts, but I'm not sure how I get this list added onto my report. Any suggestions?
Show Just This Thread        Post Reply
Advanced Access Lessons Comment from Lynda Chase @ 11/10/2013
Hi Richard, this is probably a ridiculous question, but where are the advanced access lessons?  are they still in creation?


Reply from Richard Rost:

RIGHT NOW once you finish the Access Expert series, you can move into the 300-level Access classes. They're for Access 2003, but 90% of the material is perfectly valid.

I'll be recording NEW Advanced lessons soon, as soon as I finish a few more Expert classes.
Show Just This Thread        Post Reply
Goof in video Comment from Laurie Jones @ 9/5/2013
Just so you are aware, at marker 3:36 "Due Date" is referenced. Then at 3:47, it disappears and reappears.

Reply from Richard Rost:

Thanks for pointing that out. It's just a video goof. I recorded my lessons in short segments. If I mess up, I stop recording, delete the video segment file, and then start over. Looks like I forgot to delete that segment, reset (got rid of that field), and then started over. Not the first time I've done that. One time I said something wrong, said "oh shit" in the video, and didn't catch it. That video has since been edited. :)
Show Just This Thread        Post Reply
Automating Emails Comment from Fobsester C @ 8/31/2013
Hi Richard,

Any course yet in Access 2010 to which covers automation of emails, i need to send 300 reminder emails per week in OutLook? Can this be done in Access SQL?

Reply from Richard Rost:

I do cover bulk emails in Access 329, but I'll be revising this very soon for Office 2013 in a separate seminar.
Show Just This Thread        Post Reply
Figured it out Comment from Janet Gangl @ 8/26/2013
Hi Richard - your tutorials are fantastic btw.  My last post (and only post thus far) on Expert Level 6 - lesson 3 @ 10:58. - I've figured it out so no need to comment on it.

Reply from Richard Rost:

Thanks. :)
Show Just This Thread        Post Reply
Collection Letter Report Comment from Janet Gangl @ 8/26/2013
To further my last question - I downloaded from the database bank for Lesson 6 and replaced mine. when i run the OrderCollectionLetterR I get the same error #Type in the data fields.  I am running Access 2010.

Reply from Richard Rost:

Ah yes... this is because we BROKE the collection letters in a previous class. We created the collection letters using simplified data. We have since upgraded our order entry system and now those collection letters don't work any longer. Don't worry - we'll be fixing them soon.
Show Just This Thread        Post Reply
Type Error Comment from Janet Gangl @ 8/26/2013
This is probably a simple fix but I can't see it.  I am at 10:58 in the video.  My report comes up with "#Type" for the Address, Dear [FirstName} and Days Late.  To see if the query was working, I ran the OrderCollectionLetterQ without the "Between" Function in the DueDate and it produced values in a table - when I put in the Between Function - it produces nothing.  My table has due dates that range from July 1 - Sep 5.  My Form date range is Jan 1 to Aug 26 yet the fields won't populate in the report.

Reply from Richard Rost:

Janet, without seeing your database it's very hard to troubleshoot this kind of a problem.
Show Just This Thread        Post Reply
Report not working Comment from Larisa Kiseleva @ 6/22/2013
I created letter-report form with command button which call report to send all customers letter. The text in report from text from form, like you show in your lesson. It works fine when I open form itself. After I added this form to the main navigation tap, it not working, I open form then click on report and text from form not populate text in report. What I did wrong? Thanks, Larisa

Reply from Richard Rost:

Larisa, it's impossible for me to tell what's wrong without seeing your database. Sounds like you've got something named wrong (a control or form name perhaps).
Show Just This Thread        Post Reply
Letterhead from Word to Access Comment from Carolyn Cwik @ 6/17/2013
Can formatted letterhead done in Word be imported to Access for use?  I would like all our letters to be consistent in format.

Reply from Richard Rost:

Mmm... not really. You'd have to re-create it in Access. If you want it to be EXACT, take a screen capture of it in Word and just drop it into your report header as an image.
Show Just This Thread        Post Reply
Email without Outlook Comment from Larisa Kiseleva @ 6/10/2013
hello- can you add to your future expert classes how to send email not from outlook. I know you have already seminar,
but I like to learn a small portion instead to buy a whole seminar instead.


Reply from Richard Rost:

Yes, when I cover sending BULK mail in the advanced/developer lessons, I'll show you how to send email from Access using Gmail.

Show Just This Thread        Post Reply
Automated Mass Mailings Comment from Pamela Fugate @ 6/4/2013
Richard, thank you for helping to view my class. In part 5 of the class you said that you would cover automated mass mailings in future classes. You also said it would cover macros to loop through the data and VB Code. Has this been covered in any of the previous classes? We are working on this at work, I would love to be the one to introduce it to the group. If so, can you let me know which class?

Reply from Richard Rost:

Yes, I did cover automated mass mailings in my Access 329 class. It's a very advanced class that uses a recordset loop to process the emails. You can try just that lesson if you want, but it me be difficult for you to follow along with unless you have the recordset basics that are covered in the rest of the 320- series. I am planning a separate seminar on JUST sending email from Access, however that's at least a month away yet.
Show Just This Thread        Post Reply
Put notice on calendar Comment from TAMMIE MCCONNELL @ 6/4/2013
I would like to put a notice on the calendar when its time to send particular and mass mailers,  what lesson in addition to expert 6 would help with that.  Thank you

Reply from Richard Rost:

Which calendar are you using? Outlook? I haven't covered automating Outlook using Access in any of my classes yet. I plan to soon, but it involves VBA programming. I do have a Calendar Seminar that teaches you how to create your own calendars in Access alone, however.
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
  Your Name:  Required
  Your Email:  NOT Public


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 8+2:
  Notify me when the News is updated.
  Remember Me for my next comments
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard



You may want to read these articles from the 599CD News:

6/20/2019TIP: Validation Rules in Access
11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP