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

By Richard Rost


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


First Printing 9/20/2004
Copyright 2004 by Amicron Computing
All Rights Reserved


Welcome

Welcome to the 599CD Microsoft Access 103 Handbook. This class follows Microsoft Access 102.

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 103. 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
Lesson 1. Introduction 3
Lesson 2. Main Menu Form, Part 1 4
Lesson 3. Main Menu Form, Part 2 15
Lesson 4. Customer List Form 34
Lesson 5. Tracking Lead Sources with Combo Boxes 53
Lesson 6. Review 68




Lesson 1. Introduction

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




Objectives for today’s class:

· Main Menu Form
· Customer List Form
· Lead Source Table


In case you haven’t been building a database with the class videos, a copy of the database from Access 102 has been stored on your computer in the following folder:

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

Lesson 2. Main Menu Form, Part 1

In this lesson we’re going to build a main menu form which will be a friendly interface for our users to navigate through our database. From your main database window, click on Forms and then New.




Now select Design View, but don’t pick a data source. Yes, you can have forms with just buttons and other “stuff” on them without actual data in them.





A blank new form opens. Open up the Toolbox.




The first thing we need is a command button to open our customer form. Click on the command button icon.





Place a command button anywhere on your form.




The wizard starts. Select Form Operations, Open Form.





Which form do you want to open? Let’s pick the Customer form.




Do you want to open the form and find specific data to display, or do you want to show all of the records? I’ll select the latter.





Do you want text or a picture on the face of your button? I’m going to select text and type in “Open Customer Form” for the caption that I want on my button face.




Let’s give this button a name. I’ll call it CustomerButton.





Click on Finish, and you’ll see your command button on the form.




Save your form as MainMenuF.





Close the form now and reopen it.




Click on your button, and the customer form should open.





Now run through the steps again, but this time create a button to open your Employee form.




We have no records in this form, so we don’t need the record selector at the side of the form, nor do we need the navigation buttons at the bottom. I would also like to change the caption at the top of the form – in the title bar. Let’s open up the properties for the form. Double-click on the little gray square where the rulerbars meet. A form property window will open.





Make sure you’re on the All tab (older versions of Access don’t have these tabs). Find the caption property. Type in “PCResale.NET Main Menu” for the caption property and then press TAB.




Switch back to Form View and notice that the caption is now on the title bar of the form window.





Return to design view. Find the properties called Record Selectors and Navigation Buttons and set these both to No.




We don’t need scroll bars either. Let’s set the Scroll Bars property to Neither.





Save the form. Close the property window. Close the form, and then reopen it.




Notice how you have a nice clean window with no record selectors, no navigation buttons, no scroll bars, and a meaningful caption.


Lesson 3. Main Menu Form, Part 2

Let’s change the background color of our main menu. Click on the form and notice that the Detail band is now highlighted. Remember when we worked with the Report builder in Access 102 and there were different sections – Page Header, Detail, and Page Footer sections? Forms can have different sections too, and we’ll see how these work in a future lesson. Just recognize this for now. Go ahead and use your paint can to change the background color.




Aaah. Much better. Now let’s put a label on the form. Go to the label control on your toolbox.





Click on the form where you want the label to go.




Now just type in what you want the label to say. I’ll type in “MAIN MENU.”





Let’s change the font to Arial, 24 point. Notice that the label text gets bigger, but the size of the “container” it’s in is unchanged.




Now, just resize the label to make it bigger.





Let’s make the label bold, and a little big larger – maybe 26 point. Let’s change the foreground color to dark blue, and the background color to light blue. I’ll also click on the align center button so the label text is centered inside the label container.




We’ve talked about some of these topics in previous classes. We also go over the Formatting Toolbar in great depth in our Microsoft Word class. It’s essentially the same. You can use the Border Color button to change the color of the label’s border.





There is also a border thickness button.




There is also the border special effect button – we used this earlier when we changed the border style of our memo fields on the form.





Now we can move stuff around a little, resize our label, adjust the size of the form, and it looks nice and neat.




Let’s put a picture on each button instead of the text. Right-click on a button and select Properties.





Find the Picture property. Click on the little builder button next to the field (the button with the … in it). You might need to click in the picture field to see this button.




I’m going to now pick the Happy Face button picture from the list. Click OK.





Notice the button has changed on the form.




Now, just click on the other button and the property window will show the properties for that button. No need to close and reopen it. Pick a picture for that button as well. I’ll pick the Coffee Mug picture for my employees (not that I’m trying to say anything negative about my employees.)





Now let’s put a couple of labels on the form to identify those buttons. Drop a label on the form, but instead of just clicking to place the label, click and drag the label tool to create the label box (container) in the shape you want it.




I’ll type in “Customers” for my label text, change the font to Arial, 16 point, bold. Change the background color to yellow, and give it a border style.





Now I want to move the button to the right side of the label and have it hang over it. Click and drag the button and notice it goes behind the label when I drop it.




Click on the label and click on Format > Send to Back. This will move the label behind the button.





And now the button is on top of the label.




Let’s create another label for the Employees button.





To copy the format of the customers label, click on the customer label, click on the Format Painter…




Notice the mouse pointer has changed. Click on the employees label.





Notice the format has been copied.




Let’s resize the label, move the button over the left side of it. Bring it to the front by clicking on it, selecting Format > Bring to Front, and then aligning the text of the button to the right. Change the background color to green.





Now close the main menu and reopen it. Voila!




Now, let’s make this main menu form open up automatically when we load the database. Click on Tools > Startup…





The Startup Options menu appears. I’m going to type in an application title, “PCResale.NET Customer Database.” This will show up on the status bar at the bottom of your screen (on the Windows TaskBar) instead of “Microsoft Access.” Also, from the “Display Form/Page” dropdown box, pick the MainMenuF as our startup form.




Also, if you don’t want your users seeing the Database Window (with your lists of tables, queries, forms, and reports) check off the “Display Database Window” box.





Hit OK. Close Access. Reopen your database. Notice your startup form is displayed, and there’s no database window.




Now for you to get to the database window, click on Window > Unhide.





Click on the Customer Database window and click OK.




Your database window will reappear. Let’s learn another shortcut. Close your database down again. From Windows, open My Computer and go to your My Documents folder. Find your database file, right-click on it and select Copy.





Now, right-click on your Desktop and select Paste Shortcut.




This will create a shortcut to your database on your desktop (good so that novice users can easily find your database).





For the purposes of class, I’m going to go back into Tools > Startup and turn my Database Window back on. You’ll find that while you’re designing a database, you’ll want to have this visible. When you’re done with your database, turn it off for your users.





Lesson 4. Customer List Form

Right now it is difficult to find a customer in our database. I want a form to easily select a customer. We’ll begin by creating a query that holds the data for our customer list.

Go to Queries. Click on New. Select Design View. Add just the CustomerT table to the query. Bring the following fields into your query: CustomerID, LastName, FirstName, CompanyName, Phone.




Save this query as CustomersSortedQ. Sort this query by LastName and then by FirstName.





Run the query now and make sure it’s what you want.




Save your query again, and exit it. Now we can build our form. Click on Forms, and then New. Select Design View. Select the query we just made for our data source (CustomersSortedQ). Bring all of the fields from the query onto the form.





Close the Field List. Let’s move the labels above each of their text boxes. Using the Finger pointer (remember, that upper-left corner of the text box or label gives you a finger), move the label and text box as shown…




Now, using the same technique, line the rest of the labels and boxes up as shown…





You can shrink the form up by dragging the bottom.




Save this form as CustomerListF. Now take a look at the form in Form View.





Notice you’re only seeing one record at a time. I want to see all of the records so that I can scroll down through them. This is called Single Form view. Go back to Design View. Bring up the form properties (remember, double-click on the little gray box where the rulerbars meet).




Change the Default View property from Single Form to Continuous Forms.





Close the property window. Go back to Form View. Notice you have one record after another now – in a continuous fashion. You can scroll down through them with the scrollbar.




Let’s go back into Design View and squeeze some more space out of our form by shrinking the detail section even more.





That looks a little better.




Notice the labels are appearing once for each record. We want them to only show up once at the top of the form. We need to turn on the Form Header. Go back to Design Mode. Click on View > Form Header/Footer.





You will now see two additional sections appear on your form.




Let’s highlight all of the labels now and move them up into the form header. Click in the left rulerbar directly to the left of the labels. Notice they are all highlighted.





Now, cut them out (CTRL-X).




Click somewhere inside the Form Header. You’ll notice the section bar goes dark.





Now paste (CTRL-V) the labels into the Form Header.




Let’s shrink up the Form Header section just a little bit by dragging up the Detail section bar.





Now, move all of the text boxes up to the top of the Detail section, and shrink the Detail section up so we’re not wasting space in here.




Now, save your work, and look at it in Form View. Beautiful…





Back in Design View, highlight all of your text boxes. On the formatting toolbar, select the Border Special Effects button and pick the Flat style.




Let’s also give them a Black border color.





Now, slide them all right up next to each other so there’s no space between them.




Slide them up as far as they’ll go inside the Detail section. Bring the bottom of the section right up against them.





Now take a look at it in Form View.




Go back into Design View. Shrink up the labels and boxes to more propertly fit the data in them. ID doesn’t need to be that big. Adjust your labels accordingly to look good to the users. Left align your ID field.





Now we need a button so that we can click on a Customer, click on our button, and then open up that customer’s record. Go back to Design View. Open up your toolbox. Select a command button and drop it in your Form Footer. Select Form Operations > Open Form. Click Next.




Select the CustomerF form to open. Click Next. Select Open the form and find specific data to display. Click Next.





Click on CustomerID in the left column, and CustomerID in the right column. Click on the little button between them to create a Matching Fields Link. Click Next.




Put text on your button: “Open Selected Customer.” Click Next.





Name the button CustomerButton and click Finish. Your button is now placed on your form.




Go to Form View. Click on a record to select it. Click on your button. That customer’s record should open. Try it with a few different customers. Notice at the bottom of the form it says “Record 1 of 1 (Filtered).” This means you’re not seeing all of the records. The wizard is filtering your results.





There is an AutoForm that you can use to create a similar type of form. I’ll show you briefly how to use it now. Go to the main database window. Select Forms. Pick your data source of CustomersSortedQ. Select AutoForm: Tabular from the list of available form types.




Once you hit OK, your form is basically built for you:





Why, you ask, did we just spend 20 minutes doing this the old fashioned way when there is a wizard to do the work for us? My goal here is to teach you how things work as well as why they work. I wanted to show you from the ground up how continuous forms work, and hopefully now you have a better appreciation form them, than if I had just taken 10 seconds to show you a wizard.

Along with the Tabular AutoForm, the Columnar AutoForm creates a traditional form, like we’ve used in the past:





Lesson 5. Tracking Lead Sources with Combo Boxes

In this lesson we’ll use a combo box to track a lead source for our customers. A lead source is basically where the customer found out about us… for tracking our advertising. First, let’s go into Design Mode for our CustomerT table, and add a field called LeadSource.




Save the table and close it. Now let’s add the field to our customer form. Open your CustomerF in design mode. Let’s put a combo box on the form. Open your toolbox, select a combo box, and drop it on your form under the notes section.





The wizard starts. Select I will type in the values that I want. Click Next.




Now, type in a list of the different types of lead sources you expect to have:





On the next screen, select Store that value in this field and pick LeadSource from the list of fields.




On the next screen, type in “Lead Source:” for the label. Click Finish. Your LeadSource box is now on the form.





Save your work and go to Form View. Go to a customer, and select a Lead Source for that customer. Do the same think for each of your customers.




Notice that this list is not easily updated, nor can your end-users add options. Instead of making our combo box static (not changing) let’s put these lead sources in a table. Close the CustomerF form. Go back to the database window. Go to Tables, New, Design View. Create a basic LeadSourceT table. We’ll just create one text field: LeadSource.





Save this table as LeadSourceT.




When asked if we want to create a primary key, I’ll just say no for this example.





Now open the table for data entry and put in your list of lead sources.




Save it. Close it. Go back to our Customer Form. Open it in Design Mode and delete the old Lead Source combo box. Create a new one. The wizard will start. This time, select I want the combo box to look up the values in a table or query. Click next.





Where is your list of lead sources? Pick the LeadSourceT as your data source.




Which fields from the table do you want in your combo box? Well, we only have one. Click on it, and click on the little single-arrow pointing to the right to move it over.





Next you see your data. You can resize the column width of you want. Click Next.




Now we want to store that value in our LeadSource field.





Type in the label for the box: “Lead Source:” and click Finish. Now try your new LeadSource box.




The main benefit of this technique is that you (or your users) can easily update this list and add new items to it without modifying the design of your form just by going back to your LeadSourceT table and adding the item to the list.





Notice the new item on the list.




Note that you will have to close the CustomerF, add the item to the list, and then reopen the form so it refreshes the list. We’ll learn how to add items on the fly in an advanced class when we cover VBA programming.

Now, we need to add buttons to our Main Menu for both our Customer List and our Lead Source table There is nothing new to learn here for the Customer List button – use the same techniques we used earlier to create the Customer and Employee buttons.





One problem, however, is that we don’t have a Form for the Lead Sources. Let’s throw one together real quick. This is where the wizards do come in handy from time to time. From the database window, click on Forms > New > Form Wizard. Select LeadSourceT as your data source.




Click on the right-arrow button to bring over the one field we have on this form.





Let’s pick Tabular for the layout of this form.




Let’s pick Industrial for the style.





For the form title, type in “Lead Sources.” Click Finish.




There we go. There’s our real basic lead sources form.





The wizard named our form the same as the title we selected for it. Let’s rename the form to LeadSourcesF.




Now you can use the command button wizard to add a button to open the lead sources table to our Main Menu.





Let’s also add a button to shut down the database. Create another button using the command button wizard. Select Application > Quit Application from the list of button options.




Save it, close it, reopen it. Click on your buttons to test them.





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