Access 2007-2013
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  
 
NEW Release: Access Advanced Level 2    dismiss
 
 

< Previous: Access Expert 4

Next: Access Expert 6 >

Access Expert Level 5

Expert Microsoft Access Tutorial - 1 Hour, 17 Minutes
 
 
This Microsoft Access video tutorial picks up where Expert Level 4 left off. This class focuses on creating a letter report that you can print, export, or email to your customers directly from your database. You will learn how to edit embedded macros, customize the Ribbon, work with Hyperlink parts, and more. Topics include:
 
  - Print a Letter Report
  - Edit Embedded Macros
  - Dealing with Dirty Form Records
  - RefreshRecord Command
  - Export to Word, Excel, PDF, more
  - Email Report via Outlook
  - HyperlinkPart Function
  - EmailDatabaseObject Command
  - Customize the Ribbon

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 5
Description: Access Expert Level 5
Versions: Recorded with Access 2013. Also use with 2007 and 2010.
Pre-Requisites: Access Expert Level 4
Running Time: 1 Hour, 17 Minutes
Cost: $19.99


This class picks up where Expert Level 4 left off. We begin by taking the contact management form we've built in previous classes and upgrading it to allow us to type our correspondence in rich text directly in a memo field.

 

Next we will design a report that is formatted to look like a letter that we can then print out and send directly to our customers. This way all of our correspondence is stored right in our database. No more searching for Word documents. We'll learn how to format the letter report properly, adjust paper size, margins, add a logo, set up a page footer, page numbering, use the can grow and can shrink properties, and lots more.

 

Next we'll learn how to edit Embedded Macros. When we create a button to open the report with the Command Button Wizard, Access creates an Embedded Macro for us. Once you learn how to work with these macros, you can add all kinds of new stuff to your databases. We're just going to scratch the surface today by learning how to refresh the current record in our contact form before opening the letter report. This makes sure that any recent edits are reflected before we print. You'll learn about the OpenReport command, the RefreshRecord command, build events, dirty records, and lots more.

 

Printing the letter is nice, but this is the 21st Century. We want to be able to email our reports! In this lesson, we'll learn how we can email a report with a single click. The Command Button Wizard can create an email button for us, but with a few changes in the button's macro, we can automatically populate the email address, subject line, and more. You'll learn about the EmailDatabaseObject command, the HyperlinkPart function, and more. Plus, we'll see how to export the report in several different formats: Excel, Word, PDF, etc.

 

Finally, we'll take a few minutes to learn how to customize the Ribbon. We'll see how to navigate the Ribbon using just the keyboard, add and remove buttons from the Quick Access Toolbar, and create our own custom Ribbon tabs and groups.

 

This is the fifth class in the Access Expert series. If you need to manage any kind of customer correspondence, send email from your database, print or export reports in different formats, or any of the above topics, then this is a good 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 5

0. Intro (06:52)

1. Contact Letter Query (9:51)
Make Notes Field Rich Text
Set fields to Rich Text
Open Report to Specific Record
Query Criteria Forms!FormName!Field
Run Query Without Form Open
Enter Parameter Value Prompt

2. Contact Letter Report (15:16)
Format of a Printed Letter
Create Blank Report
Bring in Contact Query Fields
Remove Borders
Long Date Format
Create FullName Field in Report
String Concatenation in Text Box
Can Grow, Can Shrink
Text Box and Section Grow / Shrink
Label for Return Address
SHIFT-ENTER for New Line
Paper Size
Margins
Put Logo on for Letterhead
Line and Web Address in Page Footer

3. Print Letter Button (11:45)
Command Button
Print Preview Report
Dirty Records
Refresh Data Before Opening Report
Edit Embedded Macro
Build Event
OpenReport Command
Add New Action
RefreshRecord Command
Change Order of Macro Commands
Match Size of Text Box with Letter
Page Numbering
Page N of M
4. Export or Email Letter (18:15)
Export to Excel
Export as Text File TXT
Export as PDF or XPS
Send as Email Attachment w Outlook
Export as Word Document
Export as HTML Document
Email Report Command Button
EMailDatabaseObject Command
Object Type
Object Name
Output Format
Anatomy of a Hyperlink Field
HyperlinkPart Function

5. Customizing the Ribbon (9:06)
Using ALT Keys
Minimize the Ribbon
Collapsing the Ribbon
Customize Quick Access Toolbar
Add to Quick Access Toolbar
Access Options
Customize Ribbon
Popular Commands
Commands Not in the Ribbon
New Custom Tab
New Custom Group

6. Review (5:54)

 


 
Keywords: Print Letters, Edit Embedded Macros, microsoft access tutorial, microsoft access tutorial, microsoft access training, access 2007, access 2010, access 2013, rich text, letter writer, can grow, can shrink, paper size, margins, logo, hyperlink, dirty record, refresh, embedded macros, build event, openreport, refreshrecord, export to excel, export as pdf, emaildatabaseobject, hyperlinkpart, customize the ribbon
 
 

Student Interaction: Microsoft Access Expert 5

Richard on 5/10/2013:  This is the fifth Microsoft Access video tutorial in the Expert series. It picks up where Expert Level 4 left off. This class is 1 hour, 17 minutes long and focuses on creating a letter report that you can print, export, or email to your customers directly from your database. You'll also learn how to edit embedded macros, customize the Ribbon, and lots more. Topics include: - Print a Letter Report - Edit Embedded Macros - Dealing with Dirty Form Records - RefreshRecord Command - Export to Word, Excel, PDF, more - Email Report via Outlook - HyperlinkPart Function - EmailDatabaseObject Command - Customize the Ribbon Click here for more information on Access Expert Level 5, 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.
Pamela Fugate on 5/13/2013: Access Expert 5: Email object
How would you mail one record at a time to the same email address that is not included in the database?

Reply from Richard Rost:

You can either use the email address that is in the database or type in a different one. If it's not in the database, Access can't use it.

Bruce Reynolds on 5/17/2013: What language is the embedded macros written in? When you create an embedded macro, does an Access DLL create VBA behind the scenes, or are these embedded macros comprised of another language, such as C# or .NET?

Reply from Richard Rost:

There's no special language that the macros themselves are written in. They're stored in a proprietary format inside the form. There is an option to convert the embedded macro code over to VBA if you want. We'll cover that in a future lesson.

Tore Svanlund on 6/10/2013: Hi, is it possible to include a link back to the form from where I send the email. For exampel I create a purchase request and I like a manager to approve that request by clicking the link.

Reply from Richard Rost:

Access cannot handle this alone. You would need to send him a link to a web page where you can collect the data that way.

Maggie on 6/10/2013: I have finished the expert 4. I am working on a project that requires to have one field to store more than one value. For example a book is classified under more than one category, ie nutrient, health and biology. Then, I also want to set a combo box with all categories to choose a specified category we want it to show all books related to that category. May I know which class you covered this topics? Thank you.

Reply from Richard Rost:

I recommend AGAINST using multi-value fields. They're OK for simple databases, but they are VERY hard to work with when you get into more advanced topics like SQL and recordsets.

The situation you mention requires a MANY TO MANY relationship which I cover in my Relationship Seminar. I'm also going to be covering it in the next Access Expert class (level 7) which will be out this week... so stay tuned.

Mary Franklin on 6/19/2013: Richard, Expert5 lesson3 time 4:46 when you open build event I get Microsoft Visual Basic for Applications and I think its because this database was developed in Access 2000 and I used your Workorder Seminar. But I have been using Access 2010 now for quiet a while. Is there a way to get it to show this macro window? I did look back on a refresh from Seminar lesson and you used Me.refresh and I used that and it seems to work but I would like to learn how to use the macro screen in the lesson.


Reply from Richard Rost:

If you created the database initially in 2000 and you're now using 2010, I would upgrade the database file format. Open it up in 2010 and save it as an ACCDB file (2007 format, actually).

If that doesn't work, you can create a new BLANK database and just IMPORT all of the objects.

Mary on 6/20/2013: I created blank database and it let me import everything accept my WorkOrderT. A message comes up (The Microsoft access database engine stopped the process because you and another user are attempting to change the same data at the same time)

Reply from Richard Rost:

Is anybody else connected to your system? Reboot the computer. Compact & repair the database.

Richard Rocha on 7/6/2013: around 3:21 you mention that if the field is "already" Rich Text when you create the field... How would that be?
Thanks, Richard

Reply from Richard Rost:

We set it as RICH TEXT in the table.

Iain Kilpatrick on 9/12/2013: Hi Richard, I have found your courses excellent and have nearly finished my database. I have one annoying problem which is when printing a report. I have a piano tuning database in which I have customers with lots of pianos. I have created a report to show which pianos have been tuned. This shows the pianos okay in a nice print preview page, then when I print it does not print one page, but as many records that are shown on the page. So 12 pianos the 12 pages all showing 12 records. I am sure there is a quick fix or something silly I have done. Please can you help? Also how can I put a print button on a report when in pop up mode, without it showing up on the printed paper?

Reply from Richard Rost:

You just need to filter the data in your report to show just the records you want. Use a query, as I show in class. Without seeing your database, it's difficult for me to tell you where the problem is.

Button on a report? We don't put buttons on reports, only on forms. :)

John Borrelli on 9/19/2013: Found it.... Data entry to yes. Thanks

Reply from Richard Rost:

:)

Saud S. Al Otaibi on 9/30/2013: How to send an email to my contacts at onetime and let access gets their emails address from my contacts table or form or query.

Reply from Richard Rost:

I'll be covering sending emails in a future seminar.

Nikki on 10/19/2013: I'm looking to Email same report to multiple recipients with only their jobs. I want to do this is a macro not vba? Is this possible if so, do you have a free video? If not, does this class cove that?

Reply from Richard Rost:

This will be covered in an upcoming seminar dedicated to emailing from Access, however I will be using VBA, not macros.

Joseph Crouch on 11/2/2013: It appears to me that Access 2007 only allows customize quick tool bar. I think you would have to do some XML defs etc. to do this in Access 2007.
Lynda Chase on 11/6/2013: Hi Richard, Can this be done in reverse, so you design different reports, to imitate standard letters, then somehow link that report to a customer for emailing?
BJ on 12/19/2013: Hi Richard, there doesn't appear to be a RefreshRecord command in Access 2007. Is there another way to accomplish this? Thanks!
James Coker on 2/1/2014: I am using Access 2013 and cannot seem to get the "Can Grow and Can Shrink" property to work in the lesson that deals will creating the ContactForLetterR. I even watched a video in your "Tips and Tricks" session to make sure there are no controls to the right of address block as wells as understanding that none of the fields can be overlapping and that the "Detail" section itself has its own properties. It reads as such in the report:

Richard Rost

PO Box 101
Amherst, NY 14226

The blank line is still in the report even after also have verified that I followed all the previous lessons and made sure that "Access Learning Zone" is no longer the that tables or customer forms.

What else do I need to check?

Sincerely,

James Coker
Austin, TX

James Coker on 2/4/2014: Richard,

I am having problems in Lesson 2 "Contact Letter Report" of Access 2013 Expert 5 at the 8:00 minute mark where the discussion on how to use the "Can Grow and Can Shrink" property begins. My report still has the white space where the company name was previously. I have done sever things to troubleshoot this. I have watched your other Tips & Tricks videos to make sure there is nothing to the right of the field(s) in question and that each one of the fields in the address block is aligned to the grid. Also, I have checked the "Details" section as we based on the beginner level classes. At that level, it worked in that lesson. I have also that the data in the "Company Name" field is NULL in both the table and form yet it still does not shrink in the report eventhough both "Can Grow" and Can Shrink" properties are set to "Yes."

Sincerely,

James Coker
Austin, TX

DilipKumar T on 2/6/2014: Sir..! When I design the report with more then 30 field ..I have got the problem that I cannot extend the design area not more then 55 x 48 cm How do I increased If I want to print the report in A2 size format or A1 paper size.?
vicki H on 2/7/2014: I can't seem to get below my letter body to enter the "Sincerely", ? If I press tab or enter it goes to the next field. If i put my pointer where I want to type it yells at me with just a ding.
Joe C on 2/26/2014: Access reports are limited to a specific area.

You can make your report a sub report, Then drag it into a report. In the report you drag it into, you will be able to go into the properties list and change the Height of the sub report to 2" or 1" or what ever is needed - then set the Can Grow property to yes.

You will need to do your editing in the sub report after that.

I have one Access report with 16 full pages in it.

Chris Thompson on 7/31/2014: Access 2013 Expert 5, Session 2, TS ~8:40

You discuss the "can shrink" / "can grow" options for the CompanyName textbox (and the other textboxes as well). You also show it working - as it should. Cool.

However, I have found that if you have an additional textbox "TB" (or any other object) that is placed in-line horizontally anywhere within the height of this textbox, the CompanyName textbox will not shrink if it is empty AND the TB textbox has contents.

In my observations both of these text boxes are set to yes for can shrink and can grow. Is this a known issue with Access? (using Access 2010)



Reply from Richard Rost:

You are correct, Chris. If you have two text boxes in line horizontally, then BOTH of them have to be empty for them to "shrink" up. Likewise, if you have a LABEL in that line, it will NEVER allow those boxes to shrink up. This is just how Access is designed, and you have to take it into consideration when building your reports. The only alternative is to use some VBA to manually hide fields in the section build event.


MICHAEL J on 8/14/2014: Hi Richard
In Expert 5 lesson 4 you show us how to send emails.I have followed your instruction and everything works great until I used my Gmail address to test it out. When I opened Outlook to send it my email address was not there. Does outlook not support Gmail?

Reply from Richard Rost:

Yes, Outlook does support Gmail as long as you set it up properly. Outlook 2013 handles it quite well. Older versions not so much. You have to make sure you have SSL enabled, are using the right port, and don't have two-factor authentication enabled.

Stefanie M on 8/24/2014: I am having difficulties Customizing the send email macro. I set the comand: =forms!customerF!email as described. However I am getting the message: The form referenced May be closed or may not exist in this database. DB may have encountered a compile error in a Visul module for the form. Please help

Reply from Richard Rost:

Well, in Expert 5 you shouldn't have any compile errors as we haven't done any VBA yet. Check the spelling of your form name and your criteria. Not to pick on you, but since you spelled a couple of words wrong in your Forum post, that's most likely the problem. :)

Stefanie M on 8/26/2014: Thank you Richard, I had the form named CustomerDetailsF instead of CustomerF.

Reply from Richard Rost:

Spelling errors are the problem 90% of the time. Like I said, I don't mean to pick on you, but whenever I get a question emailed to me with ANY spelling errors in it, that's the first thing I suggest (and, no, I'm not perfect with my spelling either, and it has caused me MANY nights of hair-pulling trying to find the error, too).

Ramiro Rangel on 10/20/2014: A minute in into Expert 5 Lesson 3 you instruct to input a button command into a report (design view). I've checked the wizard option is on, however upon inserting the button the wizard does not run? What am i doing wrong?

Thanks

Betti Baldan on 11/28/2014: When I try to put in the email address, I don't get the helper window to select the name of the form, etc. If I type in =Forms![CustomerF!][Email] I get an error message, "Access can't parse the expression" What am I doing wrong?

Reply from Alex Hedley:

You've got the bracket around the !
It needs to be =[Forms]![CustomerF]![Email]

Betti Baldan on 11/28/2014: Please ignore last email today. I closed the database and everything worked when I reopened it.
Kenny Nelson on 2/26/2015: Rick, I have created an unbound listbox of customer orders that is populated by assigning the following SQL statement to the Data.RowSource:

SELECT OEORDHDR_SQL.ord_no, OEORDHDR_SQL.cus_no FROM OEORDHDR_SQL ORDER BY OEORDHDR_SQL.ord_no;

The user clicks on the orders he/she wants to preview in a confirmation report, then clicks a "Preview" button that has the following VBA code:

Dim strwhere As String
If (IsNull(List9.Value) And List9.ItemsSelected.Count = 0) Then 'NO ORDERS SELECTED
strwhere = ParseWhere
Else
Dim I As Variant
strwhere = ""
For Each I In List9.ItemsSelected
strwhere = strwhere & "ord_no = '" & List9.ItemData(I) & "'" & " OR "
Next
strwhere = Left$(strwhere, Len(strwhere) - 3)
End If

DoCmd.OpenReport "Order Confirmation", acPreview, , strwhere

Now I want to create an email to send this confirmation to the customer. Do I use the SendObject command, and if so, how do I pass the parameters to it?

Reply from Alex Hedley:

Follow this Tip.
Or Expert Level 5 for a full explanation.
Or if you really want to use Emails to their full potential use the Access Email Seminar.

Lynda Chase on 7/26/2015: Hi Richard,
I use gmail for my emailing, when I click on the external data tab, the email option is greyed out. Is there any way I can still email the CommunicationForLetterR?

Reply from Alex Hedley:

The Email Seminar shows how you can connect to an SMTP server like your GMail account.

Robert Mossing on 9/2/2015: I guess I thought this session was going to show how to send only the report that was open in the form. This session shows how to send the report - which will send the entire report. How do I send only the page of the report that is specific to the record open in the form?

Reply from Alex Hedley:

Rich shows this in a Tip.
You can create a Query that filters your dataset down to a single record then open the Report.
Or Filter the Report in the OpenReport arguments.

Derek Kong on 9/26/2015: Which course shows how to create the contact form with the "Extend info" and "show all contacts" buttons?

Reply from Alex Hedley:

There's a Topic Index you can search, this shows E4.

Derek Kong on 9/26/2015: I am referring to your form title ContactF. I have never been able to figure out how to create this form.

Reply from Alex Hedley:

I'm not sure what your question is Derek?

Derek Kong on 9/29/2015: How does the incoming email come into the data base?

Reply from Alex Hedley:

Expert 20 shows how you can link to Outlook folders.

Thomas Szypulinski on 12/26/2015: when I followed your steps I was able to send the form but it was blank. What am I missing?

Reply from Alex Hedley:

What was blank, were there records in the db?

Joe Beniacar on 1/23/2016: If the FirstName and LastName field aren t actually on the ContactLetterR, where is the concatenated field FullName obtaining it s data from? Is the Report s Record Source like a Query s Table or the Query fields you bring into the Field: row to make calculated fields off of (even if you don t bring them into the Report directly)?

Reply from Alex Hedley:

ContactLetterR has a RecordSource of ContactForLetterQ
ContactForLetterQ is a Query using two Tables: CustomerT and ContactT.
CustomerT has both FirstName and LastName

There is a Textbox on the Report called: FullName that has a Control Source of :=[FirstName] & " " & [LastName]

Joe Beniacar on 1/23/2016: Is it always better to leave the AutoNumber field in your Forms and Reports (in case you need it)?

Reply from Alex Hedley:

There's debate over this.
If it exists in the RecordSource of the Form/Report then it can be used in Macros/VBA code.
There was a time when it had to be on a Form/Report to be used but I think this goes back to a very old version of Access.
You can always add it to the Object but hide it

Matthew Pattison on 3/23/2016: Rick,


There is more Keyboard shortcuts for access than you might think.

Reply from Alex Hedley:

Care to share any Matthew?

Logan Cutshall on 4/20/2016: Question, in Lesson 1 my query consistently keeps pulling up info from the same ContactID, in my case #8 in my sample data and only from one single person. My Forms!ContactF!ContactID formula seems correct and I've been able to keep up so far, not sure what's gone wrong here.
Regards to all.

Reply from Alex Hedley:

Would you not want CustomerID => Forms!CustomerF!CustomerID to filter the Contacts by Customer?

Anonymous on 4/21/2016: Never mind, figured it out, sorry to waste anyone's time.

Reply from Alex Hedley:

Could you share with the class what you did to fix it in case others do the same thing?

TAMMIE MC on 6/2/2016: What lesson covers posting mass mail report information posting to the customer contact tables?

Reply from Alex Hedley:

The Email Seminar does.

Stuart Jones on 6/8/2016: Richard,
When I hit the build event button in any of the proerties areas for a form, querry or report I am presented with the VBA editor and compiler by default. How do I get the friendly macro editor as the default editor to build an event?
I am using access 2013 at home and access 2010 at work. I have the same issue in both versions.

Reply from Alex Hedley:

Tools | Options
Forms/Report tab
Deselect the Always Use Event Procedures check box and then click OK

 

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

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
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