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 204 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 204
Course Handbook Supplement

By Richard Rost



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


First Printing 10/14/2004
Copyright 2004 by Amicron Computing
All Rights Reserved


Welcome

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

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 204. 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. Contact Letter Query 4
Lesson 2. Contact Letter Report 8
Lesson 3. Macro Basics 14
Lesson 4. Refresh Macro 21
Lesson 5. Formatting the Letter 24
Lesson 6. Review 37




Introduction

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





Objectives for today’s class:

· Letter Writer
· Making a report to print a letter from our contact management system
· Using a query to limit the data to the currently selected record
· Using Macros - Macro Basics
· Using a macro to refresh the records in your form


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

C:\Program Files\599CD\Access 204\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, Windows 101, 102, or 110. Word 101 and Excel 101 are also recommended.

Lesson 1. Contact Letter Query

In today’s class we’re going to create a letter writer based on our contact management system. Type in a letter into a contact form, and print it out as a letter.




One thing I just noticed that we forgot to do after the last class is to put in the Default Value property for the Customer combo box on this form. Let’s make sure to set this now. Open up the properties for the combo box and set it to: =Forms!CustomerF!CustomerID.





This will make sure the combo box gets its ID from the currently open customer. Now, we need a report that’s going to open and only show specific records – the currently open contact. The command button wizard does not allow us to do this. Forms allow you to show specific records, but reports do not. So we need to make a query to limit the data for us. This query can then feed our letter report.

From the database window, go to Queries, New, Design View. Bring in the CustomerT and ContactT tables.

Bring in the following fields from the Customer table: CustomerID, FirstName, LastName, CompanyName, Address, City, State, ZIP, Country.

From the Contact table we need: ContactID, DateTime, Notes.




Save this as ContactLetterQ. Run it to see what we get.




OK, it looks good, but we’re getting all of the data. We haven’t limited it yet. We have no criteria in our query yet. Go back to design view. Now, just as a matter of personal preference, I like to move fields that have criteria over to the left – so they’re all out front.




Set the criteria equal to: =Forms!ContactF!ContactID




Remember, Access will put brackets around everything for you automatically – that’s OK. If we had used spaces in our table or field names (which we did not) then we would have to remember to put those brackets in ourselves. Whew!

Save your work (CTRL-S) and run the query. Now it shows just the currently open contact.




Note that if you go to your contact form and switch to a different contact, and then rerun the query, you’ll see it has the current contact in it. Now we can use this query to feed our report. Close this query. Note, if you close your contact form and try to run this query, you’ll be asked for the value.




Now, open up the contacts form and have one contact open for the duration of this course… so the query and report has something to read. Otherwise, you’ll be asked for the ID (above).

Let’s go to Reports, New, Design View. Select the ContactLetterQ as our data source. A blank report opens. Now we’re ready to start building our report.





Lesson 2. Contact Letter Report

Try to imagine (or even draw on paper) what you want your finished report to look like. You are going to have your company logo and return address at the top, the customer’s name and address in the middle, followed by the text of the letter and closing at the bottom of the page. (Yes, cheezy drawing in Paint, I know – but you get the point.)




Now, bring the following fields into your Detail section: FirstName, LastName, CompanyName, Address, City, State, ZIP, Country, DateTime, Notes.




Close the field list. We don’t need any of the labels. Delete them.




Arrange and resize the text boxes as would be appropriate for a letter.





Save your report as ContactLetterR. Preview it.




Looks like it’s working – although it’s not pretty. We’ll worry about how it looks later, so long as it’s working functionally now. Close this report and go back to your contact form. Let’s place a button on this form to open our letter report.





Using the command button wizard…

· Report Operations > Preview Report
· ContactLetterR
· Text: “Print As Letter”
· Name: PrintLetterButton
· Finish

Go to Form View and click on your button.




Your report should open properly.




Now, go to a blank new contact in your form. Type in a new letter…




Now, notice when I click on the Print As Letter button, my report is blank! What happened?





Notice the pencil on your form.




The pencil indicates that this record is Dirty, which means it’s still in the process of being edited, and the data has not yet been written to the table. Any queries or reports based on this record won’t work because the data doesn’t exist yet as far as the table is concerned. The only way this button is going to work right now is if we close and reopen the form or move to a different record and then back again. Notice we get an arrow if the record is Clean.




We need some way of forcing this data to be written to the table before we print the report. This is called Refreshing the record. In order to do this, we need a Macro – a basic way of programming multiple tasks to occur when a button is pressed. In order to do this, we’ll need to learn about macros…


Lesson 3. Macro Basics

Macros are one step below Visual Basic programming. They give you the ability to perform a series of automated tasks – such as refreshing your records and then opening a report. From the main database window, click on Macros and then New. The Macro builder window opens.




Let’s do something simple. Drop down the box in the Action column in the first row, and select Beep.




Save your macro. You can leave the name Macro1. Then, run the macro (just like running a query, click on the explanation point).





You should hear your computer beep at this point (if not, turn your speakers on – ha ha ha). You can also close the macro builder and run it from the database window.




Wow! That was an exciting first macro. Let’s do something else. Design the macro. Change the Beep command to OpenForm. Now, down on the bottom, drop down the Form Name box and pick CustomerF.




Save it. Close it. Run it. Your customer form should open.

Let’s add some more commands to our macro. After the initial OpenForm command, add a Beep, then an OpenTable command. Open up your contacts table.





After that, pop a message up. Pick the MsgBox command. Set the Message to “I’m done now, Master!”





Now, save the macro. Close it. Run it. You should see all of these things happen automatically – one after the other.




Let’s delete this macro. We don’t need it anymore. Let’s now make a macro to open up the Customer record for the current customer on our Contacts form… so if we’re browsing through contacts, we can double-click on the customer combo box and open up that customer’s record.

Go to design view in the ContactF form and bring up the properties for the CustomerCombo combo box. Click on the Event tab. Notice all of the On… events. The one we’re interested in is On Dbl Click. This is an event that runs when this field is double-clicked on. We can tie a macro to this event.





Go back to Macros > New. The first action should be OpenForm. The Form Name is CustomerF. Now for the Where Condition we need to specify the current customer on the open contact form. Let’s try this: =Forms!ContactF!CustomerID.




Save the macro as OpenCurrentCustomerM. Now, let’s run it and see if it works. Looks like it’s working OK. Now let’s add the event to our form. Let’s make it so when we double-click on the combo box, the macro runs. Open up the form for design view. Bring up the properties for the combo box. Set the On Dbl Click property equal to OpenCurrentCustomerM.





As a matter of personal preference, whenever I tie an event like that to a field, I like to set the field background color to light blue. This just lets the user know he can double-click on it for something to happen.




Save the form. Close it. Open it. See if it works. It appears to work fine for the customer Pharmacon. Find a record with a different customer it. Double-click and… oops… it’s not working! What happened. Let’s check our macro…

Unfortunately we missed a step. Unlike what we’ve done so far with forms, queries, and reports, the Where condition in a Macro needs to be a full equation. The macro doesn’t know what field we’re trying to relate to, so it wants to know. In this case, we need the CustomerID on the form we’re opening (CustomerF) to be equal to the CustomerID on the contact form. So, the complete Where condition must read:

CustomerID = Forms ! ContactF ! CustomerID




Now the macro should be able to tell what we want to do – otherwise, it has no idea which field on the form we’re opening has to be equal to the customer ID on the contact form. This is one of the common mistakes I make when building with macros – and my students always get stumped on this in class. So, I felt it would be good to show it to you this way (you learn by making and seeing mistakes).


Remember, also, to test stuff like this with multiple records! Functions that appear to be working properly with one record might not work with another one. Now, go ahead and save this query and re-run it by double-clicking on some different customers’ records, and you should see it works fine now.







Now we know enough about macros to be very, very dangerous. Just kidding… now we know enough to fix our little button to open the report up. Let’s do that in the next lesson.


Lesson 4. Refresh Macro

Let’s create a macro that will first refresh the data in our contact form, and then open up the contact report. Click on Macros > New. The first action is going to be RunCommand. From the list of commands on the bottom, pick Refresh from the huge long list of commands.




Now, for the second action, pick OpenReport, and set the report name to ContactLetterR. Set the View property to Print Preview.





Save this macro as ContactLetterOpenM. Go back to the Contact form. Delete the current button that we have and drop a new command button in its place. The wizard will start.

· Miscellaneous > Run Macro
· ContactLetterOpenM
· Text: “Print As Letter”
· Name: PrintLetterMacroButton
· Finish





Go to Form View. Go to a new blank record. Type in a new record. Don’t save the record (don’t move to a different record – leave it still in the edit mode with the pencil).





When you click on the button, the macro runs – it first refreshes the record and then opens the report in print preview mode. Perfect!





Lesson 5. Formatting the Letter

Let’s go ahead and make the letter look good now. The first thing I notice is that my address is a mess because there are different text boxes spaced out weird.




Go to design mode. Drop a new unbound text box on your report in the detail section.




You can go ahead and delete it’s label portion. We don’t need it.

Now, open up the properties for this text box. Set the control source equal to:

=FirstName & “ ” & LastName

Almost like we did in our queries when we created the FullName field. In fact, let’s name this box FullName too.




Let’s preview it and see what we get.




Looks good. Now, we can delete the other name fields, and do the same trick for our city, state, and ZIP code fields. Name the field CSZ and set the control source equal to:

=City & “ ” & State “ ” & ZIP

Delete the existing ones and slide this new one into place.





Take a look at it in preview mode…




Looks good. Let’s format the Date/Time now. Let’s open up the properties for this field and change the Format property to a Long Date. Also, align the text to the Left of the field.




Preview it…





Now, the font that we’re using is real small. The default font is Arial, 8 point – which is tiny for people to read a letter at. Let’s highlight all of our fields and change the font size to 12 point.




Notice how the text is now too big for the spacing we have. That’s OK. Click on Format > Vertical Spacing > Increase.





Notice how they’re all more spaced out now.




Now, let’s resize them all vertically just a little bit so that the boxes will fit the text in them. If you resize any one of these boxes, it will resize them all – since they’re all still highlighted (selected).




Now we’ve got…





Now at this point, let’s just resize them by hand, slide them all together, and make them look good…




Now, you can shrink up the notes field in design view, because we have the Can Grow and Can Shrink properties which will resize the box dynamically when the report is generated.





Don’t forget to also set the Can Grow and Shrink properties for the Detail Section to Yes.




I’ll also set the Can Grow and Shrink properties for the Country field to Yes – since the country will usually be blank. Perhaps you might want to Bold the person’s name so it stands out.




Save it. Preview it.





Notice my date got a little chopped off. That’s OK. We’ll just make the text box wider. Notice also how I’m not quite utilizing the whole page. We’ve got 8.5” to work with horizontally, and my report is only 5” wide.




You can see the width of the report in design mode.





If I have an 8.5” sheet of paper, and my report has 1” margins on the left and right sides, that means I should be able to drag the size of the report out to just short of 6.5 inches. Let’s do that.

Note: I didn’t mention it in the video, but you can change the page margins by clicking on File > Page Setup. We’ll cover this in a future class in more detail as well.




Now, you can resize your Notes field so it takes advantage of the entire width of the page.




Save it. Preview it.





It looks good. You’ll be able to see it better if you actually put a few paragraphs of text in your contact.




And then preview it…





Now let’s format this page with our logo. We could drop a new image in the page header, but we already have the image on our main menu form. Let’s copy and paste it. Design your main menu. Copy the logo…




And then paste it into the Page Header section of your report…





On the right-hand side, drop a big label. Click and drag it out so it’s a large box.




Now, type in “PCResale.NET” and then CTRL-ENTER to go down to a blank line. Finish typing in your return address and phone number, hitting CTRL-ENTER between each line.





Right-align it, change the font to Arial, 12 point. Bold it. Preview it. Looks good.




You can resize and move the customer’s address block manually to fit your window envelopes. This is just a matter of trial and error.





Lesson 6. 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