Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Access 205 Handbook
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   14 years ago

This is the full text listing of one of our handbooks. There is a lot more to this handbook. The full-color screen shots have been removed for this page. This text is simply provided so that the search engines will index the course contents. This is so any customer searching for a topic can find what class it's covered in. If you are interested in more about information about our courses, click here for our complete course listing. For details on how to purchase a handbook, visit our handbooks page.






Microsoft Access 205
Course Handbook Supplement

By Richard Rost



Published By
Amicron Computing
PO Box 1308, Amherst NY 14226 USA
www.599cd.com


First Printing 11/16/2004
Copyright 2004 by Amicron Computing
All Rights Reserved


Welcome

Welcome to the 599CD Microsoft Access 205 Handbook. This class follows Microsoft Access 204.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 205. We recommend you use this handbook to follow along with the class videos. This handbook is not meant as a stand-alone study guide.

We do recommend that you watch the course videos one time through, paying attention to the lessons covered. Follow along with the course videos using this guide. Take notes on the pages where needed. Then, watch the videos a second time, practicing the examples yourself on your computer.


Table of Contents

Welcome 2
Table of Contents 2
Introduction 3
Lesson 1. Past Due Customers 4
Lesson 2. Letter Table & Report 10
Lesson 3. More Letter Report 17
Lesson 4. Generic Letter Report 27
Lesson 5. Post Cards 37
Review 49




Introduction

Welcome to Microsoft Access 205, brought to you by 599CD.com. I am your instructor, Richard Rost.





Objectives for today’s class:

· Continuing with Letter Writer
· Mass Mailings
· Collection Letter to all customers with past due balances
· Generic Mass Mailings
· Post Cards


In case you haven’t been building a database with the class videos, a copy of the database from Access 204 is available on our web site at www.599cd.com/Access/205. It has also been stored on your computer in the following folder:

C:\Program Files\599CD\Access 205\Help


In this class we will be using Microsoft Access XP (2002) and Windows XP, however this course is valid for any version of Windows, you should have no problems following this course if you are using Access 97, 2000 or 2003.

Pre-Requisites: Access 101, 102, 103, 104, 201, 202, 203, 204, Windows 101, 102, or 110. Word 101 and Excel 101 are also recommended.

Lesson 1. Past Due Customers

We want to be able to send past-due notices to our customers who haven’t paid their bills. We need to add some fields to our CustomerT table to store the amount due and the due date. Open your CustomerT and add the following fields: AmountDue and AmountDueDate.




Lets add these fields to our CustomerF forum.





I’ll take a moment to pretty things up a bit.




Save your form. Open it up and give some of your customers past-due dates and amounts.





Now lets make a query with all customers who have past-due balances. Create a new query. Bring in the CustomerID, CompanyName, FirstName, LastName, AmountDue, and AmountDueDate.




Now, let’s add a criteria to see just the past due customers.




Save this query as CustomerPastDueQ. Run it.





How about making this a parameter query instead? This way our user can type in the date manually.




Run it.




You should see the results you requested.





We need to add the address fields to this query now, so we can send out letters. Let’s do this instead: delete all of the fields except the AmountDueDate field. Now, add in the Wildcard field (the little star at the top of the field list).




This guy says, “hey, include all of the fields from this table in this query.” This prevents you from having to add all of those fields manually, and if you happen to add more fields to your table in the future, they will automatically be added to this query too. Run the query.




Notice I’ve got two fields for the due date: CustomerT.AmountDueDate at the front, and Field0 at the end.





This is because I have the same field twice in my query (once manually, and once with the wildcard). In order to fix this, simply HIDE the manual field by checking off the Show box.




And that fixes the problem.





Lesson 2. Letter Table & Report

We’ve already got a letter nicely formatted. So we don’t have to reinvent the wheel, let’s just copy it and change where it gets its data. Make a copy of your ContactLetterR from the last class. Call the copy our CustomerPastDueLetterR.




Open this new report up in design mode. Bring up the report properties. Remember how to do this? Double-click on the box where the rulerbars meet.






Change the Record Source to CustomerPastDueQ. This is what’s going to feed our report.




Save your report. Close it. Run it. Enter in a Past Due Date. Notice the query under the report is asking you for its parameter.




OK, next it’s looking for a DateTime value. What’s this? Remember the field we had on our ContactF form that supplied the date & time for the individual contact letters? That’s what its looking for. We’ll fix this in a minute.




It seems to run OK. There are some problems. First, notice how the next customer appears right at the bottom of the report. We’ll fix this in a minute too.




Alright – back in design mode, let’s open the properties for the DateTime textbox.




Let’s set the Control Source of this text box equal to: =Date()





Save it. Run the report. You should see today’s date in there.




Save and close this report. Now, let’s build a table and form to actually store our letter text, so we can quickly and easily change the text of our letters. Go to tables, new, design view. Add the following fields: LetterID, Description, LetterText.




Save this table as your LetterT. Say “yes” to create a primary key. Now let’s make a form for it. Forms, new, design view. Pick the LetterT as your record source. Bring all the fields into your form and make it look pretty. Save it as your LetterF. Put some data in it.




I’ll type in three letters: the normal collection letter, a past-due 30 days letter, and a really mean past-due 90-days letter. Now, let’s open our report and make the Notes field on that report get the text from this form. Open the report, bring up the properties for the Notes field.




Change the control source to: = Forms ! LetterF ! LetterText





Now we need a button on our form to open the report. Drop a command button on your form.




The wizard will start:

· Report operations > Preview report
· CustomerPastDueLetterR
· Text: Preview Letters
· Name: PrintLetterButton
· Finish






Save it. See if it works.




Looks good. The report is getting the text of the letter from the form.


Lesson 3. More Letter Report

Let’s begin by opening the properties for the CompanyName field on our report. Set the CanGrow and CanShrink properties both to Yes. This way, if there is no company name, a blank space isn’t left.




Next, let’s make each customer show up on their own page. Open up the properties for the Detail Section by double-clicking on the Detail band.





Change the Force New Page property to After Section. This will force a new page to start after each customer record.




Preview your report again. Looks good. Each customer is on their own page.





Now we need some kind of a greeting line, like “Dear Anna,” to appear on our report. Go to design view on your form. Let’s copy and paste our FirstName & LastName text box.




Change the Name of the box to GreetingLine. Change the Control Source of this new box to the following (I’ll replace the space character with an underscore _ so you can see it better):

= “Dear_” & [FirstName] & “,”




You can unbold the greeting line if you want. Save it. Preview it.




Let’s put the date on the form in a textbox so we don’t have to keep typing it in. Drop a new, unbound text box (ab) on the form.





Delete the label that came in with the box. Move it over and resize it so it looks pretty. Open the properties for it. Set its name equal to DueDate. Set its control source to =Date(). I’ll also put in custom format of mm/dd/yy.




Now we need to make our query get the value from that box instead of bothering us for it each time it runs. Open the query in design mode. Change the parameter to:

<= Forms ! LetterF ! DueDate




Remember, Access will put those square brackets in there for us, but since we don’t have any spaces in our field names, we don’t have to worry about it. Aren’t you glad I got you in that habit?

OK, save the query. Go back to your form and preview the report.




Now, design your report and open up your field list. Bring in the AmountDue and AmountDueDate fields.





I’m going to use the format painter and copy the format from my other fields onto these two new ones. Let’s resize them so they fit nice, bold them, and reformat them.




Save it. Preview it.





I’m going to put the closing remarks in the actual text of the letter.




There we go…





Now let’s generate late notices for people more than a month late. Uh oh… problem… I can’t change my date on the letter form. Why?




If you look at the properties for this box, I have the Control Source fixed at the current date. We need to change it so that the Control Source is nothing, and the Default Value is =Date(). This way the date starts out at today’s date, but can be changed.





Now you should be able to change it…




When you run your report now, you should see only people with due dates older than 10/13/04.

Lesson 4. Generic Letter Report

Let’s make it so we can send general letters (like sales letters) to customers that aren’t necessarily collection letters. Let’s add a new field to our CustomerT table called IncludeInMailings. This will be a Yes/No field and we’ll default it to Yes.




Let’s open up the table and include some customers in our mailings.





Let’s make a query. Queries, New, Design View. Bring in the customer table. Bring in all of the fields (*) and bring in IncludeInMailings. Set the criteria to True. Hide it so we don’t get duplicate field names.




Save this query as LetterMailingQ. Now, let’s take the letter we just made (CustomerPastDueLetterR) and copy it to a new report called LetterMailingR.





Open this new copy for design. Get rid of the AmountDue and AmountDueDate fields.




Change the Record Source for this report to LetterMailingQ, the query we just made.





Now, let’s design our LetterF form. Change the caption of your current button to say “Collection Letters.”




Watch this trick. Grab the Rectangle object from your toolbox and draw a box around the Collection Letters button and its text box.




Now, give it a background color, like blue. Then click on Format > Send To Back to put the box behind the button. Give it a special effect like Sunken.





Let’s add a new command button to the form for our generic letters.

· Place a new command button
· Report operations > Preview report
· LetterMailingR
· Text: Standard Letter
· Name: StandardLetterButton
· Finish




Create a new generic sales letter:




Click on the Standard Letter button. Looks good!




Now, let’s add the IncludeInMailings checkbox on our CustomerListF form. Open your CustomerListF for design mode. Take a look at its Record Source. We need to add the field to our CustomersSortedQ (where this form gets its data from). If you click on the little (…) button next to the Record Source field, it will open the query for you. This is what I call the Builder Button.





Delete all of the fields from this query that do not have criteria (everything except FirstName and LastName). Bring in all fields (*). Hide the other two fields so they aren’t duplicated.




Now save this and close it. Go back to your form. Add the IncludeInMailings field to your form.





Cut the label out and paste it into the form header. Move it over to the right over the checkbox. Change the label to say “Mail?”




Save the form. Close it. Reopen it.





Let’s try to make it a little smaller. Move the checkbox up as far as you can in the detail section. Slide up the bottom of the section against the text boxes. Turn off the sunken effect on the box. That should make it look a lot nicer.




Aaah… much nicer.





Now all we have to do is put a button for our LetterF form onto our Main Menu form. We’ve done this a whole bunch of times. You should be able to do this on your own.




One more thing you might want to do is put the IncludeInMailings checkbox on the CustomerF form.





Lesson 5. Post Cards

In this lesson we’ll learn how to make post cards by using Columns in our reports. Create a blank new report. Just go to Reports, New, Design View. Don’t pick any data source for it yet. You’ve got a big, blank, empty report.




Get rid of the page header and footer sections just by dragging their bottoms up so their height is zero.





Click on File > Page Setup.




Click on the Page tab and select Landscape.





Click on the Margins tab and set your margins to 0.25 on all sides.




Click on the Columns tab. Set the Number of Columns property to two (2).





Just so we can see what our postcards are going to look like, draw a rectangle in the detail section.




Save this report as PostCardR and preview it.




It looks OK, but our post card is really small. Go back into design mode and drag your detail section so it’s about 4 inches tall.




Resize your rectangle (again, this thing is just temporary so we can see what we’re doing).





Save it and preview it. Looks a little better.




Let’s pull up the properties for our report now. Set the Record Source equal to LetterMailingQ so we can actually get some data in this report.





Save and preview it. Notice how we’re getting three boxes in here now – one for each record in our query (one for each customer in the query).




OK, now we’re ready to put fields in here. Go to design view. You can delete the rectangle now. Let’s cheat and steal the fields from our other letter report. Open up your LetterMailingR and copy the return address label.





Paste it in your post card and left-align the text.




Go back to the letter report and copy the customer address block fields.





Paste those in your post card as well. I love cheating.




OK, save it and preview it.





Looks like we lost one of the post cards. It moved over on to page two because one of the fields expanded (can grow – probably a country field). Shrink down your detail section just a hair.




Preview it now. Perfect.





You’re now ready to print these out. You can then use your favorite word processor or graphics program to print something on the back side. This prevents you from having to use mailing labels (which look tacky). Print these out. Get out your handy paper cutter, and go to town.

Now, you could even place a neat looking “mail to” label on here:




I think this looks good.



Now just place a Post Cards button on your LetterF form. You should be able to do this yourself:





Review

Review topics.





Tell us what you think. Log on to www.599cd.com/Survey and take a short survey about this course.

Take your skills check quiz at www.599cd.com/Test. If you pass, you can print out a Certificate of Completion.

What’s next? Visit www.599cd.com/Access for our complete list of Microsoft Access courses.

Need Help? Visit www.599cd.com/TechHelp for Microsoft FrontPage assistance.

Make sure you’re on our Mailing List. Go to www.599cd.com/MailingList for details.

Contact Us. If you have any questions, go to www.599cd.com/Contact for information on how you can contact us by phone, email, or live online chat.





This course, handbook, videos, and other materials are copyright 2002, 2003, 2004 by Amicron Computing. All rights reserved. No portion of this course, handbook, videos, or other course materials may be reproduced, copied, edited, or otherwise distributed without the express written permission of Amicron Computing. Amicron Computing shall not be held liable for any errors or omissions in this document.

This document may not be used as part of a training course without express, written permission from Amicron Computing and the purchase of an Instructional License. For details, contact:

Amicron Computing
PO Box 1308
Amherst NY 14226 USA
www.599cd.com





Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

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

12/3/2021Access Beginner 2
12/3/2021Access Beginner 2 Lessons
12/3/2021Access Beginner 2
11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/28/2021Random
11/22/2021Currency Symbols
11/13/2021Access Developer 36
11/13/2021Access Developer 36 Lessons
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access handbook  PermaLink