By Richard Rost 15 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 206
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 12/2/2004
Copyright 2004 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Access 206 Handbook. This class follows Microsoft Access 205.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 206. 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. Setting up Tables 4
Lesson 2. Setting Up The Task Form 7
Lesson 3. Task List Form 13
Lesson 4. Synch Task List to Task Form 16
Lesson 5. More Date Buttons, Requery Button 25
Welcome to Microsoft Access 206, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Task Management System
· Task Table, Supporting Tables
· Task Form, Task List Form
· Synchronize Two Forms
· Buttons to Set Values with a Macro
· Macro Names
In case you haven’t been building a database with the class videos, a copy of the database from Access 205 is available on our web site at www.599cd.com/Access/206. It has also been stored on your computer in the following folder:
C:\Program Files\599CD\Access 206\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 thru 205, Windows 101, 110. Word 101 and Excel 101 are also recommended.
Lesson 1. Setting up Tables
In this lesson we will begin by setting up our TaskT table and some supporting tables. Begin by creating a TaskT table.
· TaskID AutoNumber, Primary Key
· PriorityID Number, Long Integer
· CategoryID Number, Long Integer
· CreatedDate Date/Time – Default Value =Now()
· DueDate Date/Time – Default Value =Now()
· Description Text
· Notes Memo
· Completed Yes/No
· RecurringID Number, Long Integer
Let’s set up our supporting tables. Begin with our priorities. Create a PriorityT.
· PriorityID Number, Long Integer
· PriorityName Text
Save your table as PriorityT. When you are asked if you want to create a primary key, say No, otherwise Access will add an AutoNumber called “ID” to your table. This is undesirable. Go ahead and click on the PriorityID field and manually click on the Primary Key symbol to make it our primary key.
Go ahead and fill in some values for your PriorityT table.
RICK’S NOTE: One thing I didn’t mention in the video, but it’s rather important to bring up… if you do decide to renumber your priorities, you will have to change all of the priority numbers stored in your task table too. In other words, if you change medium to 4, then you have to go through your TaskT table and change all of the records with priority 2 to priority 4. That’s why I mentioned briefly in the video that you might actually want to number your tasks by tens… 10, 20, 30, etc. This way if you decide you want to stick something between, say, low and medium, you just number it 15.
Now let’s do the same thing for a CategoryT.
· CategoryID Number, Primary Key
· CategoryName Text
Save it. Manually assign the primary key. Add some categories of your own.
Let’s next set up a RecurringT.
· RecurringID Number, Primary Key
· Description Text
Put some values in:
Save and close this table, and now we’re ready for the next lesson.
Lesson 2. Setting Up The Task Form
Now that our forms are set, let’s build a TaskF. Create a new form based on the TaskT table. Bring in all of the fields from the Field List. I’ve arranged my fields as shown. I’m moving the three IDs that are based on other tables (PriorityID, CategoryID, and RecurringID) off to the side, because we’re going to be replacing them with combo boxes.
First, let’s replace the PriorityID combo box. You’ve done this a million times by now. Nothing new:
· Delete the PriorityID text box
· Drop a combo box on the form from the toolbox (make sure your wizards are on)
· Look up the values in a table or query
· Get your values from PriorityT
· Bring over all fields
· Resize your column, hide the key field
· Store the value in PriorityID
· Label: “Priority:”
Bring up the properties for the combo box. Change the Name to PriorityCombo.
Now, let’s sort the values in the combo box. Click on the builder button […] next to the Row Source property box.
The SQL statement for the row source comes up. Sort these results based on PriorityID.
Close the SQL statement window. Save changes. Save your form as TaskF and open it up. See if it worked.
Looks good to me! Back to design view.
Now, do the same exact thing for the other two combo boxes (CategoryCombo and RecurringCombo). You should be able to figure both of these out. If not, I walk through them step-by-step in the videos. I’m not going to go through them here.
The only difference is that you might want CategoryCombo sorted by CategoryName and not by the ID.
Next, I’ll make some minor cosmetic changes to the form as follows:
Now, I would like to be able to double-click on any of these combo boxes and have that open up the accompanying table. For example, I could double-click on the PriorityCombo box and it would open up the PriorityT table so I could make changes. Let’s make a Macro to do this.
Go to Macros. Create a new Macro. The action is OpenTable. The table name is PriorityT. View is Datasheet. Data Mode is Edit. Save this macro as OpenTableM.
If you double-click on this macro from the database window. It should open up your priority table. Go ahead and test it if you wish.
Now, go back to your TaskF form. Open the properties for the PriorityCombo box. Click on the Event tab. Find the On Dbl Click event. Drop down the box and pick OpenTableM for the event.
Just to let the user know there’s something they can do with this field, I’m going to set the background color of this combo box to light blue. I do this in all my databases. It just visually tells the user they can double-click on it for “more stuff.” Once they know this, it enhances the usability of the database. (That’s where proper training comes into play!)
Let’s save our work and test it. If you double-click on it, your priority table should open up.
RICK’S NOTE: I didn’t really mention this much in the video, but one slight problem exists at this point. If you do decide to add or edit a value in your priority table, the changes won’t show up in your combo box until you close and reopen the form. It’s a minor inconvenience, and we will learn how to deal with it in a future class. Hint: you need an “on got focus” event that requeries your combo box. When we get into VBA, you can actually accomplish this with one line of code in the OnGotFocus event: PriorityCombo.Requery. More on this later.
Now, I want to do the same thing for my other two combo boxes, but I don’t want to create a new macro for each one. Wouldn’t it be nice if I could tuck them all inside one macro? Open up your OpenTableM macro in design mode. On the toolbar there is a button that has a little “XYZ” on the top of it. It’s called the Macro Names button. Click on that. Notice the new column.
You can use these macro names to create “groups” or “subsets” of macros. For the first group, put PriorityT under the macro name next to our existing OpenTable command.
Now, skip a line (just a Rick formatting tip) and create the next group. The macro name is CategoryT (you can call it anything you want – again, no spaces). The action will be OpenTable, CategoryT.
Do the same thing for RecurringT.
Save the macro. Open up your TaskF. Edit the properties for your PriorityCombo again. Change the On Dbl Click event to OpenTableM.PriorityT. Notice the different groups of macros (subsets of macros) that are available.
Now we can set up the On Dbl Click events for the CategoryCombo and Recurring Combo. Make them blue, and you’re all set. Test them at your leisure.
Lesson 3. Task List Form
I want to be able to see all of my tasks the screen in a nice, easy-to-read list. We’ll make a task list form that will be a continuous form showing each task, one per line. First, I have a couple of real quick editing changes to make to our form.
· Left-align all of the text boxes
· Auto-order the Tab Stops (View > Tab Order)
· Set the TabStop property to No for TaskID, CreatedDate, and Completed.
Now let’s put some data in. Add a couple of tasks.
I’m going to quickly move DueDate under the notes field. This way the first field is Description. Reorder by Tab Order.
Add four or five tasks. Close this form, and let’s make a Task List Form. Create a new form. Use design view. Base the form on the TaskT table. Instead of using the Field List, I’m going to cheat and copy and paste the field I want off of the TaskF form.
Copy the following fields: TaskID, Description, DueDate, Completed, PriorityCombo, CategoryCombo, RecurringCombo.
Now, rearrange the fields horizontally under their labels. We’ve done this before – we’re getting ready to make a continuous form.
I’ll make some other minor cosmetic changes: make the label text white, make the text box borders flat and black. Now, turn on the header and footer (View > Header & Footer). Cut out all of the labels and paste them into the form header. Squeeze out all of the open space.
Bring up the properties for the form and change it from Single Form to Continous Forums. Save this as TaskListF. Close it and open it up.
RICK’S NOTE: it’s interesting to note that had I been designing this form by myself without teaching it in class, I would have just copied the whole TaskF form, deleted the fields that I didn’t want, and then changed it over to a continuous form. Teaching this material, however, I become used to doing things “the proper way.” However, you don’t have to build this form from scratch… feel free to do it the quick way if you want.
The first thing I would like to be able to do is to hide all of the tasks that are due in the future. I don’t want to see them with the list of tasks that have to be done now. I would also like to be able to sort this list different ways. We’ll see how some of this stuff works in the next lessons.
Lesson 4. Synch Task List to Task Form
I want to make it so that if I click on one of my tasks, the TaskF opens up and shows that task. Let’s create a new macro. Go to Macros. New Macro. Action: OpenForm. The Form Name is TaskF. The Where Condition should be TaskID=Forms!TaskListF!TaskID. Save this as OpenTaskM.
Now go back to your TaskF form. Open up the form properties. Set the OnCurrent event to our new OpenTaskM macro. The OnCurrent event fires (runs) whenever you open the form, or move from one record to another.
Now, close all of your forms. Open up your TaskListF and notice that the TaskF opens with it automatically. When you click from one record to another, the appropriate TaskF record should open. They’re linked together!
RICK’S NOTE: If you move the forms where you want them on your screen and hit the Save button, their positions should be saved for the next time they open up. I say should because it doesn’t always work. Bug in Access? Maybe.
Let’s have some fun. It’s always important to interject some fun into your projects… especially if you’re getting paid by the hour. Just kidding (or am I…???)
Let’s add a button to our TaskF. I want a button that I can click on that will add a new task. Yes, I know we just need to click on the “add new record” button at the bottom of the form (one of the navigation buttons), but this one will do more for us. It will bring us to a new record and set the focus (where the cursor is) to the Description field so we can just start typing.
Let’s go to Macros. Create a new macro. The first action will be GoToRecord. For the arguments, select an object type of Form. The object name is TaskF. The record should be New (yeah, I know I said “next” in the video, but I correct myself a moment later.)
The next action will be GoToControl. The control name is Description.
Save this as GoToNewTaskM.
Go back to our TaskF. All of the buttons we’ve made so far have involved the Command Button Wizard. This will be our first button from scratch. So, open up your toolbox, and turn the wizards OFF. The little button in the upper-right corner of the tool box (looks like a magic wand) turns the wizards on and off.
Now, drop a command button on your form. Notice, no wizard starts.
Change the button caption to: &Add New >
Open up the button’s properties. Set the name to AddNewButton. Go to the Events tab. Find the On Click event. Drop the box down and pick the GoToNewTaskM macro.
Close the form. Save it. Open up the TaskF form again. Click on your button. Notice it goes to a new record and moves your focus to the Description field. This allows the user to just start typing. Normally, your focus would have been left on the button itself, and you would have had to click on the description field. It’s little things like this that make your forms much more user friendly.
OK. Time for more fun buttons. Let’s make some buttons to change the due date on our tasks. Let’s go to Macros. Create a new macro. Turn the Macro Names (XYZ) on. Call the first group SetToday. The first action will be SetValue. Next we need to know what the name of the item (field) we want to set. There is a little builder button […] next to this field that launches the Expression Builder. This will let us pick the name from a list of all available fields.
To be honest, I don’t use the Expression Builder much, but here’s how it works. Click on it. The Expression Builder opens. Double-click on Forms, double-click on All Forms.
Find TaskF and click on it. Notice all of this form’s properties, events, and fields show up in the boxes to the right. Double-click on the DueDate field in the middle column.
Now click on OK. Notice it drops the value into the field for you. We could have just typed this in, but if you don’t remember what the field name is (or you just don’t care to type) you can use the Expression Builder to grab it too.
RICK’S NOTE: I like to teach you how to do it the old-fashioned way first because I want you to get a better feel for how these things work. You know me… walk before you run. I don’t use the Expression Builder unless I have a ton of Windows open and I can’t remember the name of a field… then it’s OK to use it instead of poking through fifty windows to find the field name.
Now, what do we want to set the value of the DueDate to when this macro runs (when we click on the button). In this case, we want today’s date, so in the Expression box, put =Date().
Save this macro as TaskDateChangesM.
Let’s put a button on our form to run this macro. Shrink up the DueDate field. Copy and paste the AddNew button (why not?) and make it nice and small. Put a single “T” on it for “Today”.
Now, open up the properties for this button and change the On Click event to run the TaskDateChangesM.SetToday macro.
Save your form. Close it and run it by clicking on the button.
Oops. Something’s not right. We got 12:00:21 AM. What happened? Well, this is one of those cases where you have to be very careful about the syntax. The macro does not want the equals sign in front of the Date() function. Change it to the following and it will work OK.
RICK’S NOTE: Yes, this is one of those things that you’ll sit up until 2 AM pulling your hair out over. I know I have. In fact, when I first put this course outline together and was playing with this example, I forgot about this myself. All I can tell you is that you just need to try different alternatives until something works. Hopefully, by you seeing it here, it will stick in your mind in case you see it in one of your examples – that’s why I left the error in the class instead of doing it right the first time. We learn by making mistakes sometimes.
Click now, and it should work OK.
Now, how about tomorrow’s date, and the plus or minus one day buttons? We’ll cover those in the next lesson.
RICK’S NOTE: Yes, I’m out of time. I try to keep each lesson to about 15 minutes, otherwise the videos get too long and the audio gets out of synch. That’s why sometimes I split topics across two lessons – even sometimes in the middle of the topic. Perhaps if I limited myself to fewer tangents it wouldn’t be a problem, but I like my tangents (and so should you!)
Lesson 5. More Date Buttons, Requery Button
Let’s make the button for tomorrow’s date. Copy your Today button. Change the caption to “TT” for tomorrow.
Edit our TaskDateChangesM macro. Create a new group called SetTomorrow. Make the action SetValue and you can just copy and paste the field name from the other macro group. Set the Expression to Date()+1.
Save it and close it.
Set the OnClick event for the new button equal to TaskDateChangesM.SetTomorrow.
Try it out.
RICK’S NOTE: I didn’t do this in class, but you could very easily set Control Tip Text captions for your buttons so that when you hover over the buttons the little yellow tip pops up to tell you what the button does. You can do this for extra credit if you like. We covered how to do this in a previous class.
Now let’s make the macros to set the date ahead and back one day. I’m going to copy and paste my SetToday macro group (CTRL-C, CTRL-V) and just edit that (instead of creating another group from scratch). Call this new group SetPlusOne. Now instead of setting the expression equal to a function of today’s date, we want to just increment the date value by one.
Copy another button. Put a little plus sign in the caption. Set the OnClick Event to the new macro group. See if it works. Test it. It works fine. Now, do the same thing for SetMinusOne.
Create a button for it and test it.
OK, check this one out… create a new macro group called SetLaterToday and set the value to Date()+0.75 which makes it 6pm. Why 6pm? Sometimes you want to make a task for “later” today. You don’t want to see it on your task list this morning, but you want to get to it by the end of the day.
RICK’S NOTE: You can think of days in Access and in VB as whole units of one. Date()+1 equals one day from today. Date()-1 is yesterday. Now, if you think of a whole day, what’s HALF of a day? Well that would be 12 hours. So Date()+0.5 would be today at noon. Date()+0.25 would be one quarter of a day, or today at 6am. So, Date()+0.75 is today at 6pm. You could also say Date()+(3/4) and let Access do the math for you. You could even say Date()+(18/24) if you want to think in military time. Just remember the parentheses. If you want 9am, you could say Date()+(9/24).
Make a button for it. I’ll put an “L” caption on it for “Later” today.
Next, I would like a little text box next to my DueDate field that just tells me what day of the week this task now falls on. Make a copy of your DueDate text box. Delete its label. Shrink it up and slide it in next to the other DueDate field.
Open up its properties. Set the Name to DueDate2. Set the Format property to ddd.
I’m also going to set the background to transparent, set the text to blue, and the border to transparent as well. Also, make it so this field is not a tab stop. Save and preview your form.
See? Isn’t that nice. Now you can easily see what day of the week that task falls on.
RICK’S NOTE: Why would you want to do this instead of just changing the main DueDate field format to include the day of the week? Try it yourself and see. When you click on the field, the day of week disappears and it becomes a little disorienting. This way, with two separate boxes, you don’t have to worry about the field format changing on you.
Now, notice that if I add a new task, my TaskListF doesn’t update. We still see the same four tasks. I need a button on the task list form to requery the records so I see any new ones. Create a new macro. Set the action to Requery. Leave the control name blank. It will then requery whatever form you call it from. Save this as GenericRequeryM.
Close it and go to our TaskListF. Drop a command button in your form footer, and set its On Click event to the new GenericRequeryM macro. Give it a try. Open the task list and add a new task. Then hit the requery button.
· Task Manager
· Task table and supporting tables
· Task form
· Task list form
· Synchronize two forms
· Buttons to set values with macros
· Macro names (groups)
Tell us what you think. Log on to www.599cd.com/Survey and take a short survey about this course.
RICK’S NOTE: I really do enjoy getting surveys from you! Make sure you visit the web page above and fill out the survey for this class. Let me know if I’ve moved too fast, and whether or not I covered material that was helpful to you!
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: