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  

< Previous: Access Expert 5

Next: Access Expert 7 >

Access Expert Level 6

Expert Microsoft Access Tutorial - 1 Hour, 18 Minutes
This Microsoft Access video tutorial picks up where Expert Level 5 left off. This class 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

Order Now

If you would like a preview of what's covered in this class,
click here to watch the first and last lessons of this course (free of charge), or scroll down for more information.


Access Expert Level 6
Description: Access Expert Level 6
Versions: Recorded with Access 2013. Also use with 2007 and 2010.
Pre-Requisites: Access Expert Level 5 strongly recommended
Running Time: 1 Hour, 18 Minutes
Cost: $19.99

This class picks up where Expert Level 5 left off. In fact, this class could be thought of as part two of Expert Level 5. We will be sending late / collection notices to our customers, so we'll need to put some orders in the database for them. This will require setting up an order table and a query to show which orders are late.


Once we have that in place, we can create a printable letter to send to all of those customers with past-due orders. We'll learn about some new detail section properties, how to force a new page between letters, and add the order information fields (due date, amount due, etc.) to the customer's letter.


It would be nice if we could have several different collection letters - not just the one. So, we will create a letter table to store multiple letter templates. We'll create a separate letter for those customers who have orders less than 30 days old, another for those between 30 and 60 days old, and a final letter for those over 60 days old.


It's a bit of a pain to have to manually enter in the dates when we generate each of our different collection letters, so we'll see how we can store date offsets (number of days) in the letter table, and have Access automatically change the begin date and end date for the letter date range when we generate each batch of letters, automatically.


Now we'll see how we can broaden the scope of our letter writing form. We can use it to send letter to all active customers, or to customers in a specific category. So we'll add the "IsActive" field checkbox to the customer list form (for easy selection). Then we'll make a separate letter report we can send to just those customers.


Finally, we'll create another letter report that we can send to all of the customers in a specific category. Remember the "Lead Source" combo box we added a few classes ago? We'll use that for our customer category list.


This is the sixth class in the Access Expert series. If you need to be able to print mass mailings from your Access database, then this is definitely the right class to take. Of course, if you have any questions about whether or not this class is for you, please contact me.


Complete Outline - Access Expert Level 6

00. Intro (7:10)

01. Collection Letters 1 (11:32)
Create OrderT Table
Create OrderCollectionLetterQ

02. Collection Letters 2 (12:17)
Copy current contact letter
Change record source
Detail section properties
Force New Page After Section
Turn off Alternate Background Color
Add order info fields to letter

03. Collection Letters 3 (13:29)
LetterT Table
LetterF Form
Enter multiple letters
Text on report set by form
BeginDate and EndDate for query
Can't edit with Date() in Control Source
04. Collection Letters 4 (8:55)
Put LetterF on Main Menu
Change date range based on letter
BeginDaysAgo, EndDaysAgo in LetterT
Set BeginDate and EndDate Automatically

05. Generic Letters (19:19)
Send letters by various criteria
Letter to Active customers
AllowEdits vs. Locked Field
CustomerActiveQ Query
LetterActiveR Report
Letters by Lead Source

06. Review (5:21)


Keywords: Send Mass Mail from Access, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, collection letters, date range, report properties, record source, force new page, after section, alternate background color, letter templates

Student Interaction: Microsoft Access Expert 6

Richard on 5/28/2013:  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.
TAMMIE MCCONNELL on 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.

Pamela Fugate on 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.

Larisa Kiseleva on 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.

Carolyn Cwik on 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.

Larisa Kiseleva on 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).

Janet Gangl on 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.

Janet Gangl on 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.

Janet Gangl on 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. :)

Fobsester C on 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.

Laurie Jones on 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. :)

Laurie on 9/6/2013: Too funny! Thanks for sharing. :)
Lynda Chase on 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.

Chris S on 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?

Alexander Adamson on 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.

Ben Eynon on 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.


Ben Eynon on 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


Tim Florio on 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?

Cheryl on 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.

Norma Cerpa on 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?

Joe Beniacar on 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

Joe Beniacar on 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.

Joe Beniacar on 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?

Joe Beniacar on 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

Mike Holmes on 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?

Mike Holmes on 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.

Mike Holmes on 5/14/2016: To follow up my last question, I went back and figured it out. I had to put in a criteria in my query that was "Forms!LetterT!LetterId" and that limited the letter generation to the one I have on the form.

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


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