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 207
Course Handbook Supplement
By Richard Rost
PO Box 1308, Amherst NY 14226 USA
First Printing 12/6/2004
Copyright 2004 by Amicron Computing
All Rights Reserved
Welcome to the 599CD Microsoft Access 207 Handbook. This class follows Microsoft Access 206.
This handbook is designed to be a supplement to the full 599CD video course for Microsoft Access 207. 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. Task Query 4
Lesson 2. Recurring Tasks 11
Lesson 3. Choose to Hide Future Tasks 17
Lesson 4. Filtering Results 26
Lesson 5. Miscellaneous 37
Welcome to Microsoft Access 207, brought to you by 599CD.com. I am your instructor, Richard Rost.
Objectives for today’s class:
· Task Manager
· Task Query, Hide Completed Tasks
· Show Tasks by Date, Recurring Tasks
· Conditions in Macros
· IF in Macros
· IIF function
· Filtering results in forms
In case you haven’t been building a database with the class videos, a copy of the database from Access 206 is available on our web site at www.599cd.com/Access/207. It has also been stored on your computer in the following folder:
C:\Program Files\599CD\Access 207\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 206, Windows 101, 110. Word 101 and Excel 101 are also recommended.
Lesson 1. Task Query
Let’s begin by making a query that we can base our TaskListF form off of that will hide completed tasks. Create a new query. Design View. Bring in your TaskT table. Bring in all of the fields (*) and also bring in the Completed field. Set its criteria to False and hide it.
Save this as our TaskQ. Now go to our TaskListF form. Design it. Change it’s Record Source from TaskT to TaskQ.
Now when you open your TaskListF form, you shouldn’t see any completed tasks. Mark one completed and open it, and you won’t see it. Remember, you might need to Requery your list. Check it. It should work fine now.
OK, the next thing I want to do is hide any tasks that are for the future. Instead of hard-coding a date into our query as a criteria (which would make changing it hard) let’s place a text box in the footer of our form that has the future date in it. I’m going to just copy and paste my DueDate field into the footer.
Open up the properties for that box. Delete the control source (we’re not getting data from a table field). Change the name to TaskViewDate. Set the default value to =Date().
Now that we have a field on our form to set the date, let’s put that into our query. Design your TaskQ. Add the DueDate field. Hide it. Set it’s criteria as shown:
<= Forms ! TaskListF ! TaskViewDate
The date of the task has to be equal to or less than (earlier than) the date in the box on the form. Open up the TaskListF now and let’s see what we get. When the form first opens, we get no records. However, if you hit your Requery button, you get data. Why? Whenever you create a form whose underlying query gets its data from an unbound field on the form, you sometimes need to requery the data because the data recordset (the data in the table under the form) needs to recalculate based on a value on the form. Confusing, yes. Just remember that if you do this, you need to manually issue a Requery in your forms On Open event.
Design your TaskListF. Open the form properties. Find the On Open event and set it to our GenericRequeryM macro that we made.
That should fix the problem of your form opening with no data in it. Keep this in mind for any future forms you build – I have it come up quite a lot.
Now when you open your form, you should immediately have data.
Now, try changing the data and see that you will see future tasks.
Now, open the properties for the TaskViewDate text box. Go to the Event tab. Find the After Update property. Set that equal to our GenericRequeryM macro. This way, when we change the value of the text box, we don’t have to manually hit our requery button.
Next, let’s copy and paste our date reset buttons from our TaskF form.
Of course, we’ll need to create a new macro for these buttons. I’m going to make a copy of the TaskDateChangesM and call it TaskViewDateChangesM.
Now, just change all instances of TaskF to TaskListF and DueDate to TaskViewDate throughout this macro. Make sure to do it for each macro group. I’m not going to put a screen shot of each here… watch the video for details.
Save your macro. Now, just change each of the buttons – which macro they run. Use the appropriate new macros.
We can add an automatic requery into the macro groups too. Insert a row after each macro group and put a Requery command in there.
Save your macro. Now, when you click on your buttons, they automatically requery as well.
Lesson 2. Recurring Tasks
Let’s create a macro that runs when a user clicks on the Completed box (marks a task completed). Instead of marking the task completed, we want to move the task ahead X number of days (depending on whether its weekly, monthly, etc) and un-check the completed box. Click on Macros, New Macro.
We need our macro to now be able to make a decision… to decide what action to take based on the value of our data. On your toolbar you will see a box called Conditions. Click on that box and notice the new column in your macro.
If you’re familiar at all with programming, conditions are like IF/THEN statements. We need to say, “if the recurring combo is 1, then this is a daily recurring task. If it’s 2, it’s weekly, and so on.” So, here’s our first condition:
This says, “if the RecurringCombo is equal to one (1) then Set the Value of the DueDate field on the TaskF Form to Todays Date plus one day.” In other words, if this is a daily recurring task, move the date ahead one day. Make sure to include the parentheses after the Date() function.
Save this as your TaskRecurringM macro.
Now, open up your TaskF form and set the AfterUpdate event for the Completed check box equal to TaskRecurringM.
Now, find a daily recurring task and click on the Completed box. The date should move to today’s date plus one day.
Now, we need to tell the macro to un-check the Completed box. Edit your macro. On the next line down, set the value of the completed box to false. You can also feel free to put any notes you want in the notes column.
Now test your task. The date should move forward, and the box should uncheck itself automatically.
Now we just need to figure in the other options. We have daily taken care of. We can copy and paste both lines. Weekly is number 2, and we want to increment the date 7 days.
Biweekly is 3, and it can be 14 days. 4 is monthly. We’ll set it to plus 30 days. Quarterly can be 90 days. Annual can be 365 days.
RICK’S NOTE: When we get into VB programming, I’m going to show you how to do this with a real IF/THEN statement (and maybe even a SWITCH statement). There is a VB function we can use called DateAdd where we can add a real month or a real year to our value… but this is close enough for now.
Go ahead and test your other recurring types. They should work now.
Now, we have a problem with the TaskListF. If they click on the Completed box on that form, it will also close the task. For now, let’s just lock the Completed box on that form. We’ll learn how to deal with this better in a future class. Open up the properties for the TaskListF. Set Allow Edits, Allow Deletions, and Allow Additions to No.
Notice you can no longer change values on the TaskListF. The TaskF form does open, however, and you can make changes there. I’m going to make some cosmetic color changes.
One little problem has crept in. Since I locked the whole form by setting Allow Edits to no, I can no longer change my task view date criteria box. Let’s set Allow Edits back to Yes, and manually lock the individual field controls instead.
Set Allow Edits in the form properties back to Yes.
Highlight all of the form controls in the detail section (remember the trick from the rulerbar?)
Right-click on any one of them to bring up the properties common to them all. Set Locked to yes.
Lesson 3. Choose to Hide Future Tasks
We’re now going to give the user the option of whether or not to hide the future tasks. We’ll use the IIF() function to do this. First, drop an unbound check box on your form and call it ShowAllTasks.
Remember to set it’s name to ShowAllTasks and set its default value to False.
Now, in the TaskQ query, I need to say, “if that box is checked show all the tasks. If not, apply the date filter.” Open up your TaskQ. In order to keep this from being one big huge monsterous IIF statement, we’re going to break it up into smaller steps.
First, let’s create a new column that just tells us whether or not each task falls in the future. Let’s create a new column called IsInFuture.
Here’s what this says, in English: “If the DueDate of this task is less than or equal to (earlier than) the Date Filter on my Task List form, set the IsInFuture date to FALSE, otherwise, set it to TRUE.”
RICK’S EXTRA HELP FOR HANDBOOK READERS:
I like to try and make the more difficult topics easy to understand, and sometimes things like this are just easier to see in writing than they are to explain in a video (strange, I know). Here are some extra examples:
The IIF function basically works like this:
MyValue = IIF (ConditionToTest, ValueIfTrue, ValueIfFalse)
So if I said:
X = IIF (5 < 10, Yes, No)
The value of X would be Yes. Right? If 5 is less than 10 (which it is) then set X equal to Yes. Otherwise, set it to No.
Here’s the help example I give in the video:
IsJoe = IIF (Name = “Joe”, True, False)
In regular VB, that could be translated as:
IF Name = “Joe” THEN
IsJoe = True
IsJoe = False
We’ll see more VB like this later in our 300-level classes.
Now, if my statement was:
BridesName = IIF (IsMarried=True, GroomsLastName, MaidenName)
For each record in my query where IsMarried is equal to True, the BridesName would be set equal to the GroomsLastName, otherwise the MaidenName would be used.
See how this works? Here’s one more…
SalesTax = IIF (IsTaxable=True, OrderTotal * 0.0825, 0)
Here in Erie County (New York State) we have a lovely 8.25% sales tax. So, if the IsTaxable field in our query is True, set the SalesTax equal to the OrderTotal times .0825. Otherwise, the sales tax is zero. We’ll be using this one later when we start our Order Entry system in the 300-level classes.
Got it now? If not, tell me in the forums: www.599cd.com/forums
Save the query and run it. Make sure your TaskListF is open. Notice the new column
We have two tasks showing, both of which are saying they’re not in the future (0 = false). If you check their dates, that is correct. Let’s delete the DueDate field from the query however. It’s still limiting us to seeing current-only tasks.
Now if you run the query, you’ll see that some of them are current, and some are in the future (-1 = True).
Let’s create another column to determine whether or not we are hiding tasks. If our little box is checked, we don’t need to hide anything. If it is checked, we need to hide.
AreWeHiding: NOT Forms!TaskListF!ShowAllTasks
If we run it right now, our box is not checked, so every record in the query should be set to True (it’s NOT what our box is set to). Neat trick, huh?
Now we know whether or not each particular task is in the future and we know whether or not we have to hide it. If we put these two pieces of logic together, we can tell whether or not each task should be shown.
ShowThisTask: IIF((AreWeHiding=False) OR (AreWeHiding=True AND IsInFuture=False), True, False)
This is long, but it’s not that tough to understand if you think about what we need. We’re just saying “if we are not hiding tasks, then show everything. Otherwise, if we are hiding tasks AND this particular task falls in the future, then hide it.”
RICK’S EXTRA HELP:
Here is how that would look in classic VB:
IF (AreWeHiding = False) OR (AreWeHiding=True AND IsInFuture=False) THEN
ShowThisTask = True
ShowThisTask = False
Notice that I’ve put parentheses around certain conditions to group them together. We’ll cover this in much more detail when we get to VB in the 300-level classes. For now, just make sure you have them the way I have them.
Now, let’s run the query. Right now, my TaskListF is open and my check box is set to show all tasks.
If I change my check box to hide future tasks, I get this:
Now, notice that the only tasks that would be visible are the tasks that are not in the future, which is what we want. Now, set the criteria for ShowThisTask to True. This is the factor that ultimately determines whether or not we should see each task.
Now, for some reason, the form isn’t working perfectly. If we try running the query manually, it’s asking us for AreWeHiding. What’s this all about?
The problem is that the query is having a hard time figuring out it’s own values and then applying a criteria to them. It’s a common problem. To fix it, we need to take these results and feed them into a second query. That second query will deal with the criteria.
Open up TaskQ and delete the Completed field. Also, remove the True criteria from the ShowThisTask field.
Create a new query. Pull in the TaskQ into this new one. Bring in all (*) of the fields. We need Completed to be False and hidden, and ShowThisTask to be True and hidden. Save this as Task2Q.
Now open your TaskListF and change its Record Source property to get it’s values from the new query: Task2Q:
Now, your form should work correctly. Check on and off the Show All Tasks button and hit Requery, and it should work fine now.
RICK’S NOTE: Yeah, it’s strange, but sometimes you just have to take the results from one query and feed them into a second query to get it to work right. This happens all the time, especially when you have fields with values calculated with IIF functions and criteria. Doing it with two queries allows the first query to competely finish calculating its values before trying to apply the criteria. My motto: when it don’t work, try somethin’ else.
One more thing, put a Requery on our check box. Bring up the properties for the ShowAllTasks checkbox and set its AfterUpdate event to our GenericRequeryM macro. Now when you check the box it will requery results.
Lesson 4. Filtering Results
In this lesson we’re going to learn how to filter results using form text boxes. Now, I could just filter my results manually using the built-in Access filtering features, but this allows me to show you some cool new tricks. If you wanted to filter your list to see just your daily tasks, for example, you could right-click on any one of the combo boxes that say “Daily” and select Filter By Selection.
To remove the filter, right-click and select Remove Filter/Sort. This is nothing new.
Now before we do that, let’s turn a Sort on. Open up your Task2Q for design. Add the PriorityID field to your query. Hide it. Sort it Ascending.
You should now notice your tasks sorted correctly by priority. You may need to remember to remove all filters, close, and re-open your form for it to display correctly.
Go to design view in your form. Make some room in your form footer by moving all of your fields down.
Now, copy and paste the Description, PriorityID, CategoryID, and RecurringID fields from the detail section into the Form Footer. I’ll also color them gray. These will be our filter boxes.
I’m going to remove the macro events from all of these combo boxes (yes, even the ones in the detail section).
Next, we need to make sure each of these filter boxes is unbound. They’re not gettting data from the table. We want to set the values ourselves. Open up each one. Set the Control Source to blank. Change the name of each to whatever the field is plus “Filter.” So we’ll have DescriptionFilter, PriorityIDFilter, CategoryIDFilter, and RecurringIDFilter.
Make sure to also set them all so that they’re not Locked.
Let’s go back to our Task2Q query. Bring PriorityID into your query again. Hide it. Yes, it’s OK to bring a field into your query more than once. If it’s hidden, you won’t end up with duplicate names. This is, in fact, the third copy of PriorityID in the query. Now, set the criteria equal to:
= Forms ! TaskListF ! PriorityIDFilter
This way when the query runs, it will only show records where the PriorityID is equal to whatever is in the filter box. Save your query. Close it. Close and reopen your TaskListF form. Notice I get nothing.
Notice even if I set it equal to something – like URGENT – and requery, I still get nothing. So this isn’t good. What about using Wildcard characters? Remember those? In order to do this, we’ll need to add asterisk (*) values into our tables – so they can be used as data. Not a big deal. Open up each of your tables: RecurringT, CategoryT, and PriorityT, and add a single * record to each.
RICK’S NOTE: If you wanted to make it so these wildcard values do NOT show up for your normal combo boxes on your TaskF and TaskListF, you could just make a query showing all records except those where the value is *. Use the Not keyword, and this is simple to do. Just remember to make the combo boxes all based on your new query.
Now, let’s go back to the Task2Q. Delete the PriorityID with the filter parameter in it. It’s not working. We need to now check whether or not the value chosen is the * or not. This can’t be done by just looking at the value of the ID (well, it could, but that would assume the * was always the same ID in all tables – which it might not be. It’s easier to just look for the *). This will involve linking in the other tables. Click on the Show Table button.
Add in the PriorityT, CategoryT, and RecurringT tables to your query. Notice the links have been automagically created for us.
Now, bring in the Names… CategoryName from the CategoryT, PriorityName from the PriorityT, and Description from the RecurringT.
Now, set the criteria for each. CategoryName must be:
LIKE Forms ! TaskListF ! CategoryIDFilter
This way, if there is a * in the filter box, everything will be shown. If not, it will filter based on the value that’s in there. Now, do something similar for the other fields. PriorityName’s criteria will be:
LIKE Forms ! TaskListF ! PriorityIDFilter
And RecurringT.Description (that’s a way of saying the Description field for the RecurringT) will be:
LIKE Forms ! TaskListF ! RecurringIDFilter
Save your query. Close and reopen your TaskListF. We still get nothing. That’s because the filter combo boxes themselves still are bound to IDs. Remember the first column (a hidden column) in each is the ID for that combo box. We need to get rid of that value and just show a list of the descriptions. Open up the properties for the PropertyIDFilter box first.
Find the RowSource for the combo box. Notice how it has two fields (columns) in it. Click on the Builder button […] for this property.
All you have to do now is Hide the PriorityID.
Close the SQL Statement window. Save changes. Change the Column Count property to 1. Change the Column Widths property to 1”.
RICK’S NOTE: Alternatively, instead of hiding this column, you could have changed the Bound Column value to 2, which would bind the “value” of the combo box to the second column. That, however, is another class.
Now, do the same thing for each of the other two combo boxes: CategoryIDFilter and RecurringIDFilter.
OK, now close and reopen your form. You’ll have to manually set each of the combo box filters to “*” and click on the Show All Tasks button. You should see some records now.
Let’s set the default value of each of the filter combo boxes to * so we don’t have to manually set them each time. You’ll need to put “*” in the Default Value property. Do this for each of the three combo boxes and for the description filter text box. (Yes, I know it’s not in the query yet – we’ll get to that).
Notice if you pick one of the filter values, like Daily, and hit the Requery button, you should see your newly filtered results.
Edit your Task2Q and add the Description field from the TaskT table. Hide it. Set the criteria to:
LIKE Forms ! TaskListF ! DescriptionFilter
Save your query. Close the TaskListF and reopen it. Turn on Show All Tasks. Type in “*check*” into your description filter and requery your list.
RICK’S NOTE: You could set your description criteria to:
LIKE “*” & Forms ! TaskListF ! DescriptionFilter & “*”
This would remove the user from having to type in the * to get all records, or from having to type in *s around their criteria. I thought of this one after the class was finished. J
Lesson 5. Miscellaneous
One problem still remains with our task list. We’re only seeing five out of seven total tasks. The problem is that I have some CategoryIDs in my TaskT that are zero (0) and don’t have any matching values in the CategoryT table.
We can resolve this problem by either (a) making sure all of our Tasks have a category set - but this isn’t likely knowing how users are, or (b) creating a 0 value for the CategoryT table… like Not Categorized. You could also change the default value for the CategoryID in the TaskT, but this is actually easier and we won’t have to change any previous records.
Now if you open your TaskListF form again, you should see all seven tasks.
We also need to set a requery event for each of our filter fields. Set the AfterUpdate event for the Description box and all three combo boxes to our GenericRequeryM macro.
Our database is getting a little big and slow. Let’s go to Tools > Database Utilities > Compact and Repair Database…
This will squeeze out all of the garbage space from our database. A moment later the database will reopen. Let’s now put a command button on our MainMenuF for our TaskListF. We’ve done this a million times…
· Task Manager
· Task Query, Hide Completed Tasks
· Show Tasks by Date, Recurring Tasks
· Conditions in Macros
· IF in Macros
· IIF function
· Filtering results in forms
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.
Don’t forget to visit our Microsoft Access Forum online at: www.599cd.com/Forums. You can chat with our instructors, other users, and even Richard too. You can ask us all of your Access questions, get answers, and tell us what you thought of our class.
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: