By 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 202
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 9/29/2004
Copyright 2004 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Access 202 Handbook. This class follows Microsoft Access 201.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 202. 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
Table of Contents 2
Lesson 1. Contact Button 4
Lesson 2. Setting Values Across Two Forms 8
Lesson 3. Sorted Customer Combo 11
Lesson 4. Callbacks 23
Lesson 5. Contacts Subform 29
Lesson 6. Miscellaneous 39
Lesson 7. Review 45
Welcome to Microsoft Access 202, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Contacts Button
· Setting Values Across Forms
· Sorted Customer Combo
· Callback List
· Contacts Subform
In case you haven’t been building a database with the class videos, a copy of the database from Access 104 has been stored on your computer in the following folder:
C:\Program Files\599CD\Access 202\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, Windows 101, 102, or 110. Word 101 and Excel 101 are also recommended.
Lesson 1. Contact Button
In this lesson we’re going to create a contacts button on our customer form so we can click on it and see the contacts for just the customer we’re on. Open your CustomerF form in design mode. Drop a command button on the form. The wizard will start. Select Form Operations, Open Form. Which form do we want to open? We want the ContactF form.
Here’s the new topic… on the next screen, select Open the form and find specific data to display.
On the next screen, we have to create a relationship between the form we’re designing and the form we’re opening – in this case, the customer form and the contact form. So, select the CustomerID from the list on the left, and the CustomerID from the list on the right, and click on the little button between the columns to create the relationship.
Let’s put text on this button instead of a picture. I’ll type in “show contacts” so the user knows what this button does.
Let’s name this button ShowContactsButton.
Save your form. Close it and reopen it. Go to any customer who has contacts and click on the Show Contacts button.
The contacts for this customer, and this customer alone, open up in the contact form.
Notice how the navigation bar says “Record 1 of 1 (Filtered)” at the bottom. This means this customer only has one contact, and the results here are filtered – so you’re not seeing all of the records (the other customers’ contacts). Try a different customer.
Notice how this customer has four contacts. Notice how you can use the navigation buttons to scroll between the contacts for this customer.
Lesson 2. Setting Values Across Two Forms
One of our customers just called, and we need to add a new contact to her form. Open up her contacts and hit the Add New button (or just scroll all the way to the end to add a new record on her form). Notice how there is no Customer value in the combo box.
I want this to be automatically populated based on the currently open customer instead of having to automatically pick the customer from the list each time. How do we get the value from one form to another? You get the value by referring to the full name of the field on the other form.
For example… if you look at the CustomerID field on the CustomerF form, the full name of that field is:
Forms ! CustomerF ! CustomerID
So in order to use that value on the Contact form, you would have to refer to it with that full name. This may seem confusing right now, but you’ll get it once you see it in action.
Go back to your contacts form and design it. Open up the properties for the customer combo box. Set its default value equal to the full name for the customer ID we want to get…
Don’t worry if Access puts square brackets around the individual components of this name. Access will change it to [Forms]![CustomerF]![CustomerID] That’s OK. Here’s one instance where not using spaces in our field names is helping us. If we had a space in either our form name or our field name, we would have had to remember to put those square brackets on ourselves! See… trust Rick. He knows what he’s doing.
Now, save your work and go back to the customer form. Click on the Show Contacts button to bring up the customer’s contacts. Click on the Add New button on the bottom of the form (in the navigation bar) and notice that the customer is now defaulted to the currently open customer record.
Notice when I first enter the contacts form from the customer form, the focus (where my cursor is) is on the ContactID field. I would like for it to place me in the Notes field automatically, so I could just start typing. Go to design view. Bring up the properties for the ContactID field. Set the Tab Stop property to No. Tab stop tells Access whether or not to stop on this field while tabbing through the fields. If the first field on a form is not on the tab stop, Access will start you on the next one (and so on).
Do the same thing for the Customer combo box and the DateTime field. The first field in the tab order then becomes the Notes box – which is where we’ll start now when the form opens.
Lesson 3. Sorted Customer Combo
Our customer combo box is not sorted. This can be a pain if we have hundreds of customers. We’ll learn two ways to sort it… using a query, and using SQL (Structured Query Language). First, notice that the combo box results are not sorted:
Let’s begin by creating a query that has the data in it that we want. Click on Queries, New, Design View. Bring in your CustomerT table. Add in our CustomerID and CompanyName fields. Let’s create the FullName concatenated field again (remember this from a previous lesson?)
Let’s sort this query based on the company name.
Run the query to make sure it’s what you want.
Let’s save this as CustomerForComboQ.
Now, rather than completely recreating the customer combo box on the contact table, let’s bring up its properties and edit it.
Just drop down the box in the Row Source property and select CustomerForComboQ from the list of options.
Now, notice when we view our results, the data is being filled from the query we created and is now sorted. Notice also that we have an empty column now. That’s because before we had FirstName and LastName as separate columns. Now, they’re together in one column, but the box is still expecting four columns (remember, the CustomerID is in the first, hidden column – so there are a total of four columns).
Let’s bring up the properties for the combo box. Change the Column Count property to 3.
Looking at the Column Widths property, let’s get rid of the measurement for the 4th column (just delete it off the end) and make the 3rd column wider – say 1”.
Now, save it, preview it, and notice the changes.
If you turn on the Column Heads property (to Yes) you will see headers above each column in your box. I seldom use this property.
For this next example, let’s go ahead and just quickly create another combo box using the Wizard so I can show you how that SQL (Select) statement works. Just run through the wizard again, and create the same combo box we had before…
· Start the Combo Box Wizard by dropping a Combo Box on your form
· Look up the values in a table or query
· CustomerT as the record source
· Bring in the fields: CustomerID, CompanyName, FirstName, LastName
· Resize the columns as desired
· Store the value in your CustomerID field
· Label it CompanyName
Double-click on the new combo box to bring up its properties. Notice the row source:
Click on the Row Source box and hit SHIFT-F2 to open the Zoom window so we have more room to work on it.
To make this statement easier to read, you can actually remove all of the [CustomerT] blocks from this statement. They just indicate which table to get the fields from, but since we’re only pulling fields from one table, they’re superfluous (not needed).
Basically, in its simplest form, an SQL statement looks like this:
SELECT Field, Field, Field, … FROM Table
So what we have is:
SELECT CustomerID, CompanyName, FirstName, LastName FROM CustomerT
This is a nice, basic, easy SQL statement to work with that will pull those four fields from our Customer table.
Now, if you want to sort this list of records using the SQL statement, all you have to do is add the following to the end of the statement:
SELECT Field, Field, Field, … FROM Table ORDER BY Field
Check out the combo box and you’ll see your results are sorted.
Yeah, this is just a temporary combo box, so I dropped it anywhere I felt like it. In fact, we’re done with it now, so you can go ahead and Delete it. In fact, let’s go ahead and delete both of the combo boxes on the form so I can show you something else new…
Let’s create this combo box one more time using the combo box wizard. This time, however, we’re going to pick the query we just built as our record source…
· Start the Combo Box Wizard by dropping a Combo Box on your form
· Look up the values in a table or query
· This time we’re getting our data from Query: CustomerForComboQ
· Bring in the fields: CustomerID, CompanyName, FullName
· Resize the columns as desired. This time, notice that you have no box that lets you hide the key column. You don’t get this box when you build your combo box from a query! You have to manually resize the column width to zero. Drag the column width way past where you think zero is to make sure you get it to zero (if not, you can manually edit the width in the properties like we did earlier).
· Now, since we’re using a query, Access has no idea which field is our key field, so it’s asking us which field we want to store. Select the CustomerID.
· Store the value in your CustomerID field in the contact table.
· Label it Customer: and click finish.
Now, resize this combo box to fit propertly with the other fields. Open up its properties and change its name to CustomerCombo. Also, remember to set the Tab Stop property to No. Also, remember to change your Tab Order – you can just click on the AutoOrder button. Save it, preview it, and check it out…
Let’s make our first visible column a little wider. Open up the properties for the combo box and change the column widths to: 0”; 1.5”; 1”
Now, look what happens…
Look how the columns are now wider than the box itself, and you’re actually getting a horizontal scrollbar. We need to make the list itself wider. Go back into properties and change the List Width equal to or greater than the width of the individual columns.
You can also change the List Rows property to change how many records are shown (vertically). I’ll change mine to four (4) so you can see the difference. Notice when you preview the form, you can see the width of the box is now correct, and I only see four records down.
Lesson 4. Callbacks
I want to make a list of everyone I need to call in the future. Let’s begin by modifying our contacts table. Open ContactT for design mode. Let’s add a field called MustCallBack which is a Yes/No field. Set the default value to No. Let’s also add a CallBackDateTime field which will be a Date/Time field.
Let’s add these two new fields to the ContactF form. You should know how to do this (hint: use the Field List).
Save your work, and now let’s go and set some callbacks. Give callback dates to a few of your customers…
Now, let’s create a simple callback query. Go to Queries, New, Design View, bring in ContactT and CustomerT. From the ContactT bring in the fields ContactID and MustCallBack. Set the criteria for MustCallBack equal to Yes.
Now bring in the CallBackDateTime field and sort that field ascending so we get the earliest callback date first.
Let’s also bring in the FirstName, LastName, CompanyName, and Phone fields from the CustomerT.
Save this query as CallBackQ. Run the query.
Our callback list is now complete, but now we can make a form to see a list of these people, click on the customer, and then bring up their customer form. To do this, we need to also add the CustomerID to this query. No problem. It doesn’t matter which field you bring the CustomerID in from – they’re both the same. Just make sure to only bring it in once.
Let’s make a contact callback form. Here are the steps (we’ve done this before with our customer list form, remember?)
· Click on Forms, New, Design View
· Use the CallbackQ we just made for our data source
· Bring all of the fields from the Field List onto your form
· Arrange all of your labels and text boxes horizontally – just like with the customer list form
· Resize your form to get rid of wasted space.
· Open up your form properties and change from Single Form to Continuous Forms.
· Click on View > Form Header/Footer to open up the header and footer.
· Cut and paste all of your labels into the form header.
· Resize your detail and form header sections accordingly.
· Save your form as CallBackF.
Let’s preview it…
Looks good so far. Now we just need a button to open up the current customer. We’ve done this before as well.
· Open up the toolbox.
· Drop a command button on your form
· Select Form Operations > Open Form
· Select the CustomerF form to open
· Select Open the form and find specific data to display
· Link together your CustomerID fields from both forms
· Place some text on your button: “Open Customer”
· Name the button: OpenCustomerButton
We can also do almost the exact same thing to open the specific contact form record as well! See if you can figure that one out.
Preview the form, and try clicking on your buttons. Test them both.
Lesson 5. Contacts Subform
Let’s use a subform to display the customer’s contacts right on the main customer form. A subform is a little form inside of another form that can be used to show related data. I like to create the subform (or the child form) first and then insert it into the parent (or main) form.
Let’s click on Forms, New, Design View. Pick our ContactT table for the data source. Bring in the ContactID, CustomerID, DateTime and Notes fields. Delete all of the labels from these text boxes. We don’t need them. Now, slide the DateTime field into the upper-left corner, and move the Notes field next to it, but shrink it up to fit on one line.
Now, I don’t need to see the ContactID or the CustomerID, but they need to both be on this form so I can use their values. So let’s shrink these fields up into tiny little boxes and move them up to the right of our Notes field. Then, shrink up the form to just fit these fields like this:
Let’s also set their border special effect style to Flat to get rid of the sunken effect they have (remember this from the formatting toolbar?) Maybe even set the border color to black.
Open up the form properties and change from Single Form to Continuous Forms so we see more than one at a time on the screen. Let’s also set the Scroll Bars property to Vertical Only. Set Navigation Buttons to No. Save your form as ContactChildF. Preview it to see what it looks like.
We don’t really need to see the time in our child form. Let’s open up the properties for our date/time field and format the field to only show a short date. Also, left-align the field.
Now we need to stick this form into the main CustomerF form. Let’s make the bottom of the form a little bigger…
Go back to your toolbox and find the Subform control.
Drop a subform object on the bottom of your form. The Subform wizard starts. First, select Use an existing form for our source. Select the ContactChildF form that we just created.
Now we have to define the field that links the main form to the subform. In this case, the CustomerID links the customer to the contacts. Access found this is an is suggesting it as the linked field. If that was incorrect, you could define your own relationship, but we’ll just take its suggestion and click Next.
Give the subform a name. Access called it ContactChildF. That’s fine. Click Finish. Notice your form.
Save your customer form and preview it…
I’m going to delete the little label that comes in with the subform. We don’t need it. Let’s also move the subform over to the left. I’m going to move the Show Contacts button underneath the subform and change it to say “Show Contacts Form.”
That looks better. One more change, however… I’d like to see the most recent contacts at the top of the list, and have them sorted descending after that.
Let’s go back to our ContactChildF and design it. First, let’s hide the ContactID and CustomerID boxes. We don’t need to see them. Highlight them both.
Bring up their properties (right click on them) and set the Visible property to No.
Now, I’m going to shrink them up even more…
…and I’m going to resize the Notes field so it covers them right up (right over the top of them).
We need their values, but we don’t need to see them.
Now, just so I don’t forget they’re there, I’m going to move the Notes field behind them. So, click on Notes and click on Format > Send to Back.
See how the Notes field is now behind them – and you can see the ID fields? Again, this isn’t too important, but it’s just something I like to do.
Notice you don’t see them if you preview the form.
Now, to sort our records, all we need to do is create a simple query. Go to Queries, New, Design View. Bring in the Contact Table. Bring in the fields ContactID, CustomerID, DateTime, Notes. Sort the query using DateTime, Descending.
I’m going to save this as ContactReverseOrderQ. Close it. Now go back into design mode for your child form and change the Record Source property to this query.
Open up your customer form, and you should see the right sort for your contact items.
Note that you can also type in new contacts into your subform at the last record.
If you leave the customer record and come back to it, you should see it sorted to the top of the list.
Lesson 6. Miscellaneous
Let’s begin by giving our Main Menu a facelift. Let’s get rid of the huge labels we have and just change our buttons to have text in them instead of pictures. Start by deleting the big yellow Customers label.
Open up the properties for the Customer form button (the smiley face). Make sure the caption says “Open Customer Form.” To remove the picture from the button, highlight the text that says (Bitmap) and hit delete. You’ll be asked if you’re sure you want to delete the graphic. Say Yes.
Now you should see a nice button with text on it in its place.
Do the same for the rest of your buttons. I’m going to Delete the button for the contact query. We really don’t want our end users messing around with the contact query directly.
Let’s add a button for our Callback List form (yeah, and I’ve moved a few things around and color-coded some of the button text).
Save your work and preview it.
Next let’s add an Autodialer button to your customer form. Note that this feature may or may not work on your computer, based on the version of Windows you have, and whether or not you have a modem in your computer.
Drop a command button on your form. Select Miscellaneous > Auto Dialer.
I’ll leave the picture of the phone on my button. Name the button AutodialerButton. Click Finish. Save your form, go to form view. Here’s how it works: click on the Phone Number text box, and then click on the autodialer button (it gets the number to dial from the last field you were on).
You will see the autodialer number confirmation window appear. Click OK to dial.
I am using Windows XP and this dialog box appears. The phone dialer itself is a feature of Windows, not Access… so if you are using a different version of Windows, these screens may appear different for you. You will have to (one time only) enter in the country/region you are in, your area code, and other information for dialing an outside line (if needed).
Click OK on the Phone and Modem Options screen that appears next. This just verifies that the number to be dialed is correct.
Now, depending on your version of Windows, the autodialer application will load and dial the phone for you. Mine looks like this:
Now, mine says Failed because this computer that I’m using doesn’t have a modem in it – which is required to dial the phone. But, if I did have a modem, it would pick up the line, dial the number, and wait for you to pick up the phone. Once you pick up the line, you click on Disconnect and the modem hangs up, leaving you on the line with the phone ringing. This is real handy if you do a lot of telemarketing or sales calls and you’re sick of dialing the phone.
Lesson 7. Review
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:
PO Box 1308
Amherst NY 14226 USA
You may want to read these articles from the 599CD News: