Access 203 Handbook

Access 203 Handbook
By Richard Rost

16 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 203
Course Handbook Supplement

By Richard Rost

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

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

Welcome

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

This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 203. 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. Extended Customer Table 4
Lesson 2. Option Group, List Box, Customer Type 6
Lesson 3. Toggle Buttons 14
Lesson 4. ActiveX Calendar and OLE Controls 21
Lesson 5. Link Forms Together, Tab Control 28
Lesson 6. Object Frames, Image Control 36
Lesson 7. Review 51

Introduction

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

Objectives for today’s class:

· Extended Customer Data
· Option Groups, Option Buttons
· List Boxes, Toggle Buttons
· ActiveX Calendar Control, Tab Controls
· Bound v. Unbound Object Frames
· Image Controls, Page Breaks, Lines & Boxes

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

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

Lesson 1. Extended Customer Table

We have additional information we would like to be able to store on our customers, but we don’t want to waste room storing it in our customer table. Let’s say you’re storing personal data on your customers, and you have 50,000 customers – but you only have personal info on 500 of them. That’s a lot of wasted space in your main Customer table… so we’ll create a second, supplemental table to store this information.

Let’s create a new table. Click on Tables, New, Design View. Create the following fields:

· CustomerID Number (Long Integer)
· Nickname Text
· Spouse Text
· Children Text
· Email Text (100 characters)
· WebPage Hyperlink
· Gender Number (Long Integer, Default –1)
· Birthday Date/Time
· CustomerTypeID Number (Long Integer)
· Hardware Yes/No
· Software Yes/No
· Service Yes/No
· Training Yes/No
· WebSite Yes/No
· ServiceTechID Number (Long Integer)
· Picture OLE Object

Save your table as CustomerExtendedT.

You’re going to be asked to define a primary key. For this table, we’re going to have a one-to-one relationship with our main CustomerT table, so we really don’t need a separate ID for this table. It wouldn’t hurt anything to have one, but for this example, I’m going to say No.

Lesson 2. Option Group, List Box, Customer Type

Click on Forms, New, Design View. Select your CustomerExtendedT as your data source. From the Field List, bring in your CustomerID, Nickname, Spouse, Children, Email, and WebPage fields.

Change your form background to light blue. Find the Option Group object on your toolbox. Click on it.

Click and drag on your form to draw an option group frame.

A wizard starts. Enter in the options you would like to have available in your group.

On the next screen, select the option you would like to have as your default value. Make sure to pick Unspecified. Click Next.

Type in the values you would like to have stored in the table to represent each of your options. Remember the little key we created earlier? Type in those values here. Again, you could have any values you wanted in here.

Next, make sure to pick the Gender field as the field you wish to store this value in.

Next, select what kind of controls you want to have in your box. You can select from option buttons, check boxes, or toggle buttons. I’ll pick option buttons. You can also select from several optional styles.

Type in a caption to go at the top of your option group.

Click Finish. There is your option group.

Save your form as CustomerExtendedF and preview it. See your option group in action. I’ll also throw in a CustomerID of 5 and a nickname of Joey. I don’t know offhand which customer this is, but it’s not important at this point.

If you examine your CustomerExtendedT table right now, you should see the values stored in there.

Next, let’s create a Customer Type table to store our different customer types in it. Let’s click on Tables, New, Design View. Create the table with the following fields:

· CustomerTypeID AutoNumber
· CustomerType Text

Save this as CustomerTypeT. Set the CustomerTypeID as your primary key. Put in some data…

Now, back in the CustomerExtendedF form, let’s use a List Box to pick the customer type. A list box is essentially an always-open combo box.

You will find that list boxes are almost exactly like combo boxes. Drop one on your form. A wizard starts. Here are the steps:

· I want the list box to look up the values in a table or query
· Select the CustomerTypeT table as our data source
· Bring over both fields
· Hide the key column, and resize the other column as needed
· Store the value in the CustomerTypeID field
· Give it a label: Customer Type
· Finish

Delete the label that comes with it… we really don’t need it. Slide it up next to the gender option group. Resize it as needed.

Save your form and preview it to see it in action.

Lesson 3. Toggle Buttons

I want to use Toggle Buttons for my types-of-products-and-services-that-this-customer-buys fields (hardware, software, service, etc.) Note when I drag these fields onto my form from the Field List, they come in as check boxes. I could use check boxes, but I want toggle buttons instead.

Note that I could right-click on the check box, select Change To and then Toggle Button.

Notice that it changes the little box into a toggle button – but the size is all strange.

That’s OK. We can just resize it and delete the label. Now, click on the button and just type in a caption:

Save your form and preview it to see how it works. Notice how you just click on the button to set it ON or OFF (yes or no).

Let’s make another one. Select the Toggle Button on your toolbox (not to be confused with the Command Button).

Click on the toggle button, drop it on your form.

Double-click on the button to bring up its properties. Set the Control Source to the field you want to bind it to. Pick Software as the control source. Also give the button the name “Software” as well. Don’t just leave it as Toggle18 or whatever its set to on yours. Set the caption property to Software as well. The caption is what shows up on the button face.

Now, resize the button and move it up next to the hardware button. Let’s just copy and paste the software button to make more (remember CTRL-C and CTRL-V to copy and paste).

Now just change the properties for this third button for the next button… Service.

Do the same thing for all of the remaining buttons of this type.

Save your form and preview it to see it work.

Let’s move these buttons up and to the right side of the form.

Note you can change the background colors for the Option Group and its label too.

Here’s a neat trick… change the caption for your Hardware button to: &Hardware.

Notice when you click off it, it says: Hardware. This will now become a hot-key when you switch to form view. Your users will be able to hit ALT-H on their keyboard to “push” this button. Change your other buttons similarly:

· Software > &Software > Software
· Service > Ser&vice > Service
· Training > &Training > Training
· WebSite > &WebSite > WebSite

Notice when you preview it, the ALT-key combinations should work.

Lesson 4. ActiveX Calendar and OLE Controls

For the ServiceTechID field, let’s create a Combo Box.

· Look up the values in a table or query
· Get your values from the EmployeeT table
· Bring over the fields: EmployeeID, FirstName
· Hide the key column, and resize the other column as needed
· Store that value in the ServiceTechID field
· Label it “Service Tech:”
· Finish

There’s our Service Tech combo box.

Save this and close it. We also need to put a Sales Rep field in our table for each customer. This is important, however, so let’s put it on the main CustomerT table.

Now put a Sales Rep combo box on your main Customer Form.

· Look up the values in a table or query
· Get your values from the EmployeeT table
· Bring over the fields: EmployeeID, FirstName
· Hide the key column, and resize the other column as needed
· Store that value in the SalesRepID field
· Label it “Sales Rep:”
· Finish

Save your work. Close it. Preview it. Notice you how can now assign a sales rep to each customer. We’ll do more with this field in the future when we get into our order entry tables and forms.

Let’s drop the picture field on the form. Notice how a huge OLE object control comes into the form.

Let’s move it down to the bottom and make it a little smaller.

We used a picture control in a preview lesson when we created our Employee form. Do you remember how to copy a picture and paste it into the form? If not review, that lesson!

Now, for the Birthday field, we’re going to use the ActiveX Calendar Control. ActiveX controls are control objects that are designed by Microsoft or other third-party developers that basically can be plugged into your database, or even a Visual Basic program. The point of an ActiveX Control is that it’s a piece of portable code that can be used in multiple projects. In other words, you can create something like a Calendar, or a spiffy new kind of Command Button that can be plugged into different projects or databases, and can be shared with other developers.

So, open up your toolbox and click on the More Controls button.

From the list that appears, find the Calendar Control 8.0 object (note, if you have a different version of Access, you might have a different version number for your calendar control).

Now, click on your form to place a Calendar Control on your form.

Right-click on the calendar control and select Properties.

Change the control source for the calendar to Birthday. This calendar is now linked to the birthday field in our table. You can also change the name of the calendar to Birthday as well.

If you double-click on the calendar, you’ll bring up its custom properties window. You can change the font, the color options, and additional properties. Feel free to play with these at your leisure.

Save your work and preview your form. Notice if you click on a date to select it, that value is stored in your table.

Let’s add another extended customer record. Set this customer’s birthday to August 8, 1961.

Now for the purposes of class, I am going to Delete the calendar control and replace it with a simple text field. There are a few reasons why I don’t want to keep this calendar in the form… but don’t worry about that now. Feel free to keep your calendar control, and to experiment with it for other uses.

Lesson 5. Link Forms Together, Tab Control

Let’s open our CustomerF form for design view. Create a command button to open up our CustomerExtendedF form so we can essentially link these two forms together.

· Drop a command button on your form
· Form Operations, Open Form
· Open the CustomerExtendedF
· Open the form and find specific data to display
· Link together the CustomerID fields
· Place text on your button: Extended Customer Data
· Name the button: OpenExtendedCustomerButton
· Finish

Save it, close it, open it, click on the button. Notice the correct customer’s data appears.

Let’s go to a different customer… one who doesn’t have any Extended data yet. I’ll click on Benny (customer ID #8). When his form opens, it’s blank and the CustomerID is 0.

I want this ID to automatically default to his ID – and let’s make this a combo box. Design your CustomerExtendedF. Delete the CustomerID field. Drop a combo box here.

· Look up the value in a table or query
· Get your data from the CustomerT
· Bring over: CustomerID, CompanyName, FirstName, LastName
· Hide the key column, resize the others.
· Store the value in CustomerID
· Label: Customer
· Finish

Now, open up the properties for the combo box. Set the name to CustomerCombo and set the Default Value property equal to:

= Forms ! CustomerF ! CustomerID

Now, save this form and close it. Go back to your customer form and click on Benny’s button. Notice how his ID defaults into the combo box.

Fill in some data for Benny. Notice how the WebPage field is automatically a hyperlink.

Let’s now consolidate some space on this form by using the Tab Controls. Go to design view. Find the Tab Control on your toolbox, and then click and drag a tab control frame box out on your form.

A tab control will appear with two tabs on it.

Now, highlight all of the text fields on your form except for CustomerID. Cut them all out (CTRL-X).

Click on Page29 (or whatever your first tab page is) and hit Paste (CTRL-V).

Notice the text boxes are now all in your tab control. Now select the other controls on the form (gender, customer type, and the toggle buttons). Cut them out. Click on the other tab (in my case, Page30). Paste them in.

Now, right-click on the first tab (Page29 for me) and change the name and caption as shown:

Now change the other tab properties:

Now, resize your tab object, resize the form, and squeeze everything up nice and tight.

Save your form, close and and preview it.

Lesson 6. Object Frames, Image Control

The OLE objects we placed so far (like the one on our Employee Form) have been Bound Object Controls – they’re bound to a field in the underlying table. There are two additional types of controls like this on the toolbox: Image and Unbound Object Control.

If I copy and paste a picture into a Bound Object Control like the one on my Employee form, it will stay in the table with the record. Let’s test it… I’ve got some images stored in Microsoft Paint. I’ll copy one…

Now I’ll paste it (CTRL-V) into the picture field.

I’ll move to the other employee record, and paste the woman’s picture in here.

Notice as I move between the two records, they change – the pictures are stored in the table… bound to the records. Now, what we’ve done here is to actually store a copy of the image in the table. Unfortunately, pictures can be huge, and can waste a lot of space in your tables. You might be better off (if you have a lot of pictures) storing a Link to the image on your hard drive. This way the actual picture isn’t store in your table.

Delete the picture for Joe (click on the picture and hit delete). Now, right-click on the picture field and select Insert Object.

Let’s select the file from one already stored on our hard drive. Select Create From File.

Now click on the Browse button to browse for your file (just pick any picture anywhere on your system – it doesn’t matter for class).

Now, after selecting the picture, make sure you click on the Link check box. This will save a reference to the picture’s filename in the table – NOT a copy of the picture itself.

Click OK to finish. Notice you can still see the picture in your form. The benefit is that the actual picture isn’t taking up space in your table. The down-side is that if this file is ever moved, deleted, or renamed, you will lose your link – so make sure you keep all of your pictures safely in a folder that won’t move.

What good is an Unbound Object Frame?

Unbound objects are stored in the Form, not in the table. The same object will be displayed for every record on the form. One example I like to use for this is a document storage form. Let’s say you have a couple of Word documents or Excel spreadsheets that you use often, and you’d like to make them easily accessible via your database.

Let’s create a new form. Click on Forms, New, Design View. Don’t pick a data source table. From the toolbox, drop an Unbound Object Frame on your form. Select Create From File and browse to any Word document on your system.

Make sure to save a Link to it… we don’t need to actually embed a copy of this document in our form.

Click OK… notice the document now shows in the object frame.

Let’s resize this box to make it smaller. Bring up its properties (right-click, properties). Change the Size Mode to Stretch.

You could even place a label over it…

Save this form as your FrequentDocumentF form. You can place more than one document on here if you want. Notice the document is bound to the Form itself, and not any table. It’s technically unbound. Now you can put a button for it on your Main Menu to open this form (you should know how to do this by now).

Save your work. Close it, and open it. Click on your Frequent Documents button.

Notice, if you try to click on the document object itself, nothing happens. When you place an unbound object on a form, Access locks it. Bring up the properties for the object, and change the Locked property to No. This will unlock the object. Also, you need to set the Enabled property to Yes.

Save it, close it, open it, double-click on the object and it will open up in Microsoft Word (assuming you have Word on your computer).

In addition to bound and unbound objects, we also have Images. Images are good for placing static (never changing) pictures on your forms… great for logos. Let’s design our Main Menu and delete the big nasty “Main Menu” label on the top of the form. Open up your toolbox and find the Image control.

Browse your hard drive for a picture file (GIF, JPG, BMP, etc.) I happen to have a logo for PCResale on my system. I’ll select that (you can select any picture).

Once the picture comes in to my form I’ll bring up its properties and set the Size Mode to Stretch.

There. That looks a lot nicer.

Perhaps I’ll play with the options a little… set the Shadowed special effect. Now, find the Line tool on the toolbox and place a line on your form (just like the line tool in the report builder).

You can use the formatting toolbar to change the color and line thickness.

Watch this trick. Draw a rectangle around the group of buttons at the top of the menu.

Now, set the box background (fill) color to light gray. Notice how it’s covering the buttons. That’s OK. Click on Format > Send To Back.

And now behold your greatness. I don’t like the gray. I’ll switch it to dark blue.

Save it. Preview it. See how cool it looks.

Remember how we talked earlier about how you could let your users add more than one Extended data record for your customers? You can force them to only have one and only one extended record for each customer by making it so they can’t scroll between records on the CustomerExtendedF.

Design this form. Bring up the Form properties. Set the Navigation Buttons property to No. While you’re at it, you can also turn off the scroll bars and record selectors. You don’t need those either.

Now open the CustomerExtendedF and see that once they open a customer’s details, they can no longer go to a blank new record – however if they pick a customer that has no current record, they’re on a blank one so they can still add details.

Note: after recording this lesson, I discovered a way the user could cheat. They could create a record for a different customer and then change the Customer Combo Box on the second record. This messes everything up because now you have two Extended records for the same customer. You can fix this loophole by changing the Locked property for the combo box to Yes. Just like the Unbound Object Frame, if you Lock a control like a combo box, the user can’t change the value.

There’s one more control we haven’t talked about… Page Breaks. I almost never use these.

Drop a Page Break object on your form right here…

Notice how it looks like a little dotted line…

Save your form and reopen it. Now, notice how your form appears to have been shortened up.

Now, you can either scroll down using the scrollbar, or hit PAGE DOWN (PGDN) on your keyboard.

I’m going to go back into design mode and Delete that page break control. I don’t use them often.

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

Subscribe to Access 203 Handbook
Get notifications when this page is updated