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
 
 
Courses - Microsoft Access 204
Description: Intermediate Microsoft Access 4
Running Time: 65 minutes
Pre-Requisites: Access 203 very strongly recommended
Previous Lesson: Access 203
Next Lesson: Access 205
Main Topics: Letter Writer, Macro Basics, On Double Click Event, Refresh Macro, Report Formatting
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 1/20/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 204
Intermediate Access 4

Designing a Letter Writer, Access Macros, Events, OnDblClick Event, Refresh Macro, Much More. 65 Minutes.
 

AC204 Major Topics

  • Letter Writer
  • Macro Basics
  • On Double Click Event
  • Refresh Macro
  • Report Formatting

In this class we will begin designing our Letter Writer system. With our letter writer, we'll be able to actually type letters and other correspondence into our contact management system and print them out in a letter or memo format to mail or fax to our customers.

 

We'll begin by developing a query that only shows the current contact record. We'll use this query to feed our report that lets us print out the letter. We'll use the technique we learned in a previous class to get the Contact ID value from the open contact form.

 

Now that we have the query built, we can build the Contact Letter Report. We'll format this report to look like a normal letter, with the customer's name and address and the body of the letter ready to mail.

 

Next we'll place a Print As Letter button on our form so that we can print the letter right up.

 

We'll learn about what it means when records are Dirty - or in the process of being edited, and how this hurts our database... we can't directly print a letter now until we manually save the data to the table. We'll need something more powerful.

 

In lesson three, we'll begin to learn about Macros. We'll make a couple of real simple macros just to teach you how they work - and how we can make them repeat automated tasks. We'll make a simple macro just to beep at you.

 

Then we'll make a macro that's a little more complicated. It will open a form, beep, open a table, and then tell you "I'm done now, Master!"

 

Next, we'll make a macro that will allow us to open up the current customer record from the contact form. We will assign this macro to the On Dbl Click event (on double-click) in our customer combo box. This means the user just has to double-click on the combo box to open up that customer. Real powerful stuff!

 

Now that we've learned about macros, we can actually make a macro to refresh the current contact record before printing it - assuring us that the data is saved in the table before printing. We'll learn how to make a command button that runs a macro.

 

Once we're done with the macros, we'll actually get into making our letter report look good. We'll start by making some new fields to concatenate our name fields and address fields together.

 

We'll learn how to automatically adjust the vertical spacing of fields on our forms and reports.

 

We'll talk about the margins of the page, and making sure you set the report at it's optimal width.

 

We'll put the company logo and our return address in the page header...

 

This class begins to take our contact management system and use it for correspondence as well. We'll do more with this in coming classes - including a mass-mailing form and letter as well. We also begin to learn about Macros, which are the stepping stone to Visual Basic programming. Don't miss this course!

 

Access 204 Outline
 
0. Introduction 

1. Contact Letter Query
Build a query that shows only the current contact
Getting the ContactID from the form into our query

2. Contact Letter Report
Designing our Contact Letter Report
Placing the Print As Letter button
Dirty records - data needs to be saved

3. Macro Basics
Constructing basic macros
Beep macro action
OpenForm macro action
OpenTable macro action
MsgBox macro action
On Dbl Click event
OpenForm WHERE condition

4. Refresh Macro
RunCommand, Refresh macro action
OpenReport macro action
Creating a new button using the macro

5. Formatting the Letter
Creating new name and address fields
String concatenation in a new text box
Formatting as a long date
Format > Vertical Spacing > Increase
Report width, page width, margins
Placing a logo in the page header
Making a multi-line label

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 204

Richard on 1/1/2007:  Designing a Letter Writer, Access Macros, Events, OnDblClick Event, Refresh Macro, Much More. 65 Minutes.
sasson cohen on 4/13/2008: do we have an index/list for reviewing each theme on each class ( maybe to support our design )?
Richard Rost on 4/16/2008: Sasson, you can find a searchable index of all of my Access classes at www.599cd.com/Access/Index
Margaret  Cattarin on 9/3/2008: Richard,
I have tried everything that I can think of to stop decimals from rounding out to whole integers. Is there hidden code somewhere?

Richard Rost on 9/8/2008: Margaret, that all depends on what you're doing. I need more information to go on. What type of FIELD are you working with? Number? If so, did you format it as an INTEGER or LONG INTEGER? If so, that's your problem.
 Elsa on 10/27/2008: Hi Richard, How do I format only a few words within a concatenation in a report textbox? Example: "Your balance is:" & "[Balance]" I want [Balance] to be in bold and formatted to ##,###.00 while the rest of the concatenation stays the same.
Richard Rost on 10/27/2008: Use the FORMAT function. Here are some examples:

="Your balance is " & Format(Balance,"##,###.00")
="Your birthday is " & Format(DOB, "mm/dd/yy")
="You owe " & Format(Balance, Currency)

I cover this in Access 311

Elsa on 10/28/2008: Hi Richard,
Thank you for your help. I am currently at 223 but will keep going.

Elsa on 11/18/2008: Hi Richard,
Does MS Access 2003 and earlier have the functionality to bold a portion of a concatenated string in a report textbox?

Richard Rost on 11/23/2008: Elsa, nope. You can't change the format of PART of a textbox string.
Greg Paradise on 2/18/2009: I'm using 2000 version and the Refresh from the run command will not run, I get an error that says this version does not support the function. Do I need to have my cd in?
 Rick on 8/13/2010: Hi Richard

I have been doing the Access course with you at 599cd which is helpful. I have a question.

I have a table that I have imported from an excell sheet. Only 2 fields. (Stock code and Suppliers part number.
The data came in well but I have duplicates which I want to sort out. (there are a lot of records in this table.) and generally only one or two duplicates for each entry, if any,
Some are a 100% duplicate (eg stock code and Suppliers pat number) and others have the same stock code but no suppliers part number. I want to keep the record with the most amount of information, Eg both stockcode and suppliers part number. Eventually I want to make a new table out of the sort.
I was thinking of using a query or a macro but not sure how to go about it. Can you point me in the best way to do it? Thanks Richard.

Robert Fitzgibbons on 2/8/2011: I cannot open http://www.599cd.com/access/204. I get the "page cannot be found error meaasge". Any suggestions?

Reply from Richard Rost:

Robert, is that a link in the video? If so, and you're looking for the student databases, they've been moved here.

Robert on 2/23/2011: I have written a query which sorts my database according to appointment time. But the report I have developed based on this query sorts differently which is very annoying. I can’t seem to tell the report how do sort properly. Any advice?

Reply from Richard Rost:

A report will override (or sometimes ignore) your query's sort. You have to use a SORTING AND GROUPING LEVEL in your report to force the sort you want. That's covered in Access 104.

 Frank on 8/18/2011: Access 204 video 1

What is the reason for adding the default value in the customerID combo box?

 Alyson on 3/21/2012: Hi. I'm using Access 2010 and when I click to create a report, it pulls up tables automatically...not queries...the only way I've found to use a query to feed a report is if I click the query and use the report wizard. Is there a way around this?

 Alyson on 3/21/2012: Sorry for so many questions...I built my query and report like you did...now when I go to the query, it won't show my information...it looks like a blank page, but when I go to design view, it's all there...what is going on?
Alyson on 3/22/2012: Ok, to say my problem correctly (lol) I built my query and then a form from the query. When I change from design mode to form view, it appears as a blank page, but when I go back to design view of the form, everything is there. I can't move on from this point due to this problem. I hope it makes more sense this time :) Please help!
Finola Bromley on 11/12/2012: Hi Richard I have hopefully quick questions. First one is that I seem to have missed how to leave 'Country' blank if it is the US. Could you let me know how I do that please? The second, more irriating problem is that I can't get rid of empty fields in addresses. For example when I'm sending a generic letter to someone in a company, I try to include their job title in the address. But I don't always have a job title for an individual and so where that field is empty, I want Access to ignore it. I've tried changing the CanGrow CanShrink options to Yes in the field properties and in teh Detail properties, but nothing seems to change and the empty fields are still in the address. Is there a way around this please? Kind regards
Finola

Reply from Richard Rost:

You have to make sure that you set the CanShrink property for the CONTROL and the SECTION the control is in to YES. You also have to make sure that there aren't any other controls on the same horizontal line with them in your report. For example, if you place a label or a vertical line that is next to them, that will also keep the report from shrinking.

Finola B on 11/12/2012: Thanks Richard, I did that but it doesn't get rid of the entire field, I still have an empty line in the middle of an address. Access is able to do it on its Labels Wizard, ie move lines up so that there are no empty gaps, but I don't know how to do it on my generic letter.

Also, you had mentioned about being able to leave the 'Country' field blank if the country was the U.S. You said you covered it in a previous class but I've not come across it and it would be useful to be able to do. Could you let me know the critera for leaving a particular country blank in a query? Thanks

Reply from Richard Rost:

I would have to see your report to tell you exactly what the problem is, but that's all you SHOULD have to do.

As far as leaving a country blank... just don't type anything into the field. There isn't any special trick. Then, if it's on a line by itself in your report, and its CanShrink property is set to yes, it should effectively disappear.

vannak on 12/30/2012: Richard,
I'm getting this error message while trying to save the Macro(there is no time index listed); "An expression you entered is the wrong data type for one of the arguments... blah blah .." I'm using Access 2010. I then clicked on the OK button and came up to the screen "Macro Single Step." Am I doing anything wrong? Thanks.


Reply from Richard Rost:

I need more context. What exactly are you doing?

Vannak Hou on 12/31/2012: richard,

I'm getting an error message on Windows 7 Proffessional 32 bit. It has been working okay but the past few days, when I tried to run any of the downloaded file (AC2* series) it's giving this error message "Your MS Office Access (2007) database or project contains a missing or broken reference to the file 'comdlg32.ocx' version 1.2." I googled and so far no luck. Any suggestion. The file opens but when I click on any of the menu buttons from the PCResale.NET Main Menu or any Menu button from within the Form file does not work (meaning the button does not open" or do anything at all. Any suggestion? It was working at the beginning for the past 2 weeks.

Reply from Richard Rost:

You may have a different version of COMDLG32.OCX on your system. You could try to download an updated version, but here's an easier fix... instead of messing around with files, just create a blank new database in 2007 and then IMPORT all of the objects from my download databases (tables, queries, etc.). That should then use YOUR comdlg32.ocx file. Let me know if this works.

Vannak Hou on 12/31/2012: Index Time: 04:50-05:08. "The expression On Click you entered as the event property setting produced the following error: Ambiguous name detected: PrintLetterButton_Click." I even changed/modified to a different and still get the same error message." Any suggestion?

Reply from Richard Rost:

You've got the same name used twice in your database.

Vannak Hou on 12/31/2012: Using Access 2007. Where is the Format option in the design menu. Time Index: 04:48-05:35. I do not see the "Format" option on under the menu. I'm using a computer at work that has Access 2007. At home, I have Access 2010. Most of the time I am accessing the site from my work place computer.

Reply from Richard Rost:

I have to admin that I don't still have a copy of Access 2007 installed on any of my machines, but in both 2010 and 2013, when you select objects on a report in design view, you get a REPORT DESIGN TOOLS section of the ribbon. All of the commands I mention in the video are available on those tabs. You can also right-click on the report objects.

vannak on 12/31/2012: Hmmm ... I thought I had given a different name file as well but I'll do it again.
vannak on 12/31/2012: Yes, I did download a few of the comdlg32.ocx off the sites (Microsoft and a few others, still no luck). But, i will try with your suggestion and let you know.
vannak on 12/31/2012: Hey Richard,

Once again,

I'll let you know because that computer or machine is a once in a while I do get my hands on it. But I will definitely save your suggestion on my e-mail and when I get my hands on it, I'll do it. As a suggestion for others as well, you might want to post this suggestion that you wrote and post on the blog somewhere. Unfortunately, I haven't used any of the blog on this forum yet. Once again thanks, Richard!

vannak on 12/31/2012: Thanks for the reply. Didn't expect you would reply this soon. Sorry to bother you on the late night e-mail. lols. I'll find out later where the FORMAT option is located and I'll let you know. Thanks again Richard.

Reply from Richard Rost:

It's no problem.

Robert Fitzgibbons on 1/6/2013: I have an access database which keeps track of patients and referring physicians. There are 3 tables, and numerous other objects to manipulate them. The primary key on the contacts table (referring Physicians) is an auto number that links to the patients table( patient demographic data) and the patient encounter table (each individual appointment) Several months ago, I made some changes to the database to improve is functionality and renamed the file new office management. Unbeknownst to me for some strange reason my secretary continued to use the old database for referring physicians adding new contacts and editing old while using the new one for everything else. . This comes under the heading of not being able to win for loosing. The simple solution would be to delete the contacts table in the new database and then export the old table into the new database. However when I try to delete the contacts table in the new database, access warns me about the relationships I might be destroying. My question is, will it work if I simply duplicate the relationships as they currently look in the new database?

Reply from Richard Rost:

You can go into the GLOBAL RELATIONSHIPS, delete the relationships, import the new table (replace the old one if needed) and then re-create the relationships. This will not cause any loss of data. It will work provided that all of your IDs match up. You probably have a CASCADE DELETE in place that's causing the warning.

Matt Pattison on 1/17/2013: Richard I love your classes

Reply from Richard Rost:

Thanks, Matt!

Robert Fitzgibbons on 1/17/2013: This worked great. I did it about a week ago and have held off on my reply to make sure there were no bugs. But the database is working perfectly. Thanks a lot.

Reply from Richard Rost:

Awesome. Glad to help.

Richard Shafer on 3/2/2013: The macro apparently does not work in Access 2013. When I follow all that you do, it only opens the first customer of the customer form. I am putting in =[Forms]![ContactF]![CustomerID] and no matter what contact record I am on on the ContactF, it only opens up the ver first customer on the CustomerF. Is this different in Access 2013 or am I doing something wrong?

Reply from Richard Rost:

I'll be addressing this issue shortly.

Rick Shafer on 3/3/2013: Bah... No need to address this. I got cocky and thought I could follow along as I was watching the video. It was the part where you intentionally only put in the part of the equation... =[Forms]![ContactF]![CustomerID] so that you could show that the macro field needs both sides of the equation [CustomerID]=[Forms]![ContactF]![CustomerID]. If I had just watched a few more seconds I would have been given the answer. I was just caught up on why it was not working for me. :) Ooops. Learned my lesson to watch FIRST, then try and re-create the information. However, I won't soon forget that macros require the full equation.

Reply from Richard Rost:

Ah. You're right. I should have remembered that, but it's been almost NINE YEARS since I recorded that video (release date Sept 2004). I'll be revising it soon... working on Expert 2 now which is the equivalent of 202.

john Edwards on 3/10/2014: Hi, Most amazing course I havce ever seen. Question Access 2003 Ingtermediate 204. Setting up letters. 1-5. Our business uses emails almost exclusivly. Can this be adapted to send the notes section into an outlook email program

Reply from Richard Rost:

Yep, of course. I show sending email using Outlook in a couple of other classes, plus I have an EMAIL SEMINAR coming up soon which shows you how to send email without using Outlook.


 

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