Access 2007-2016
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   News   Tips   Glossary   Templates   Forums   Help   Logon   Order   Contact Us  
 
NEW Release: Access Developer Level 3   dismiss
 
 
Courses - Microsoft Access 207
Description: Intermediate Microsoft Access 7
Running Time: 73 minutes
Pre-Requisites: Access 206 very strongly recommended
Previous Lesson: Access 206
Next Lesson: Access 220
Main Topics: Macro Conditions, IIF Function, LIKE Keyword, Compact, Repair, Allow Edits, Deletions, Additions, Form Filter
Versions: This course is valid for Access 2000 through 2003. If you are using Access 2007 or 2010, you will still benefit from this course. You will find that the concepts are the same, but there are cosmetic differences between the versions.

    

Order before 7/31/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 207
Intermediate Access 7

Form-Based Field Filters, Macro Collections, Allow Edits / Deletions / Additions, IIF Function, LIKE Keyword, Compact & Repair. 73 Minutes.
 

AC207 Major Topics

  • Form-Based Field Filters
  • Macro Conditions
  • Allow Edits, Deletions, Additions
  • IIF Function
  • LIKE Keyword
  • Compact & Repair

In this class, and it's pre-requisite Access 206, we are building a Task Management System to manage your "to do" list. This is the second of a 2-part series on Task Management. In this course you will learn how to hide completed tasks, show tasks by date, set recurring tasks, set conditions (IF/THEN) in macros, learn about the IIF function, and learn how to filter form results with your own text boxes.

We will begin this class by creating a date filter for our tasks - to only see tasks that are due up to and including a certain date. We will use the AfterUpdate property to assign a macro to it so it requeries the list when you change the date automatically.

 

We will learn more complicated macro grouping techniques, and how to assign all of these groups to buttons.

 

Next we'll learn about Macro Conditions, which are essentially IF / THEN statements for macros. Use them to make the macro smart and make decisions.

 

We will deal with recurring tasks... if the task is set to be a "Daily" task, for example, just move the date forward one day instead of closing it when someone clicks on the Completed box.

 

We'll learn about some new form properties: Allow Edits, Allow Deletions, and Allow Additions.

 

We'll also learn how to lock individual form controls with the Locked property so that users can't change them.

 

Next we'll learn about a real powerful function called IIF or Immediate If. This is a function that can act as an IF/THEN statement inside of queries or forms.

 

We will use that IIF function in several places to determine whether or not to display tasks based on a checkbox we'll place on the form (show all tasks or not?)

 

We will review manually filtering results on a form.

 

Then we'll create our own set of combo boxes and text boxes so our users can filter the results by setting the values themselves - without having to know how to filter.

 

We will review how to use the LIKE keyword to create wildcard parameters for our queries - allowing the users to set filters based on any combination of characters in the description.

 

You will see how to deal with the problem that can exist if one of your users doesn't pick a value - say assign a category to a Task - and that task is missing. We'll compact and repair our database.

 

We'll finish off the class by placing a button for our new Task Management System on our Main Menu.

 

Make sure to get yourself a copy of the Handbook for this course. There are a ton of ideas that I threw in while I was writing the book - after the course videos were made. The handbook has lots of extra tips in it!

 

Don't miss this course! We cover a lot of really cool tips and tricks with relation to macros and form values.

 

Access 207 Outline
 

Lesson 1. Task Query
Creating a TaskQ query
Create a TaskViewDate form field to limit the date
Base the data in the query on the date on the form
Run our requery macro in the On Open event
Run our requery macro in the AfterUpdate for the date
Copy date reset buttons from TaskF to TaskListF

Lesson 2. Recurring Tasks
Learn about Macro Conditions (groups)
Conditions are IF/THEN statements for Macros
If the Completed box is clicked on a recurring macro increment date
Deal with weekly, monthly, and other tasks
Allow Edits form property
Allow Deletions form property
Allow Additions form property
Locked control property

Lesson 3. Choose to Hide Future Tasks
Create a Show All Tasks check box
If the check box is unchecked, hide tasks by date
If checked, show all of the tasks regardless of date
Use the IIF() function to determine if task in future
Use the NOT keyword to negate values
Complex IIF() function with multiple conditions
Nested statements using AND and OR clauses
Feeding query results into a second query Task2Q

Lesson 4. Filtering Results
Manually filtering using built-in Access filters
Filter By Selection, Remove Filter/Sort
Creating our own filter text and combo boxes
Adding * values to our supporting tables
Review of the LIKE keyword in queries
Manually editing the Column Count and Column Width

Lesson 5. Miscellaneous
Dealing with missing data
Tasks without a category won't show. Fix this.
Compact and Repair Database
Add Tasks button to Main Menu

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 207

Richard on 1/1/2007:  Task Management Part Two, hide completed tasks, IF conditions in macros, IIF function, filter combo boxes
 joe on 2/13/2008: I have a table called LeadDetailsT and one of the fields in that table is called TypeOfSale. TypeOfSale has 10 choices from a drop down box. One of the choices is called "Avaya Maint". I have a query that returns a bunch of data for me. I have a form which is based off of that query and in the form I have some blank boxes/fields where I can do calculations. One of the calculations I need to do on the form is IF TypeOfSale = "Avaya Maint" THEN multiply Field A x 0.07 OR IF TypeOfSale = any other choice THEN multiply Field A x 0.15
Richard Rost on 2/28/2008: Joe, this is the exact kind of thing you need PROGRAMMING in VBA for. I start covering this kind of thing in Access 301. You can use a classic IF/Then statement in an AfterUpdate event, or you can use an IIF function.
Judith Koester on 4/29/2008: Access207 Recurring @6:02 - My TaskListF and TaskF open together fine. When I check Completed on the TaskF on a daily, weekly task the date changes OK. Then Requery the TaskListF – OK. However, when I check Completed on a Non-Recurring task, nothing happens. Also check marks don’t show up on TaskF or TaskListF at all even though date changes. When I remove the TaskRecurringM from Completed on the TaskF, I get check marks, but of course my dates don’t change.

I have been working this for 2 weeks and going back over the lessons, Macros, Events, etc. and can’t find out what is going wrong.

Richard Rost on 5/24/2008: Judith, this one is MY FAULT. The problem is that I didn't correctly show you how to use the CONDITION column in the macro builder. See, just like I said in the video, I almost NEVER use macros. I use VBA programming for almost everything (especially something this easy). And, unfortunately, I didn't test this thoroughly enough in class to notice this problem - which is why this user feedback interaction is so cool.

Anyhow, here's what happened... I forgot to tell you that you either need to copy the conditional statement down to the NEXT ROW for each item that is supposed to run UNDER it, *OR* use an ELIPSIS to indicate that the current condition runs as part of the previous one.

So, what's happening right now, is the first condition runs, which is [RecurringCombo]=1 and it performs its SetValue command if that is the case, but then the NEXT line runs REGARDLESS OF THE CONDITION because it has no condition. So in ANY CASE, the value of the Completed checkbox is made FALSE.

How to fix it? Either copy the condition down from the previous line, or just put a ELIPSES (...) there to indicate it's part of a "BLOCK" of statements.

I'll make sure to make this change to the video the next time I edit the class, but I'm very happy you found this problem. This is a MAJOR booboo on my part. I really haven't done anything in any of my personal projects with Macros in YEARS (again, all VBA), so that's why I didn't catch it. As soon as you mentioned this was happening, I was like "uh oh... my bad." :) I'm just sorry I didn't get to this sooner. I've been so busy I haven't had a chance to go through my Blog posts in a long while.

Richard Jenkinson on 9/30/2008: I couldn't get this to work for ages. After a lot of trial and error I have finally got it working. I'm pretty sure the solution was changing the 'Description' field in the RecurringT to a unique name. In the TaskListF I was getting '#Name?' in the description field and I think this was because Access didn't like the fact I had two fields called 'Description' - one in the TaskT and one in the RecurringT. I'm not sure if this is just a problem for Access 2007 but I thought I'd note it in case anyone else experiences the same problem.
Debbie on 2/24/2009: I built a query based on 3 tables i then built a report based on this query, i then built a form where you can add new records. These are all supposed to work together but they don't! I have checked the vba and it seems to be fine I don't know what else i'm supposed to check can you help.
Richard Rost on 2/26/2009: Debbie, I'd love to help you, but you haven't given me anywhere near enough information. Tell me what's not working, and what it's supposed to do.
Debbie on 2/26/2009: All 3 table are supposed to work together to build and update a report every time a new record is added. The query is not picking up or pulling any records out of the table that i created.In the query there are 3 tables 2 of them already exist and i created 1 for the form that i'm using with the add new record command button and the one that i created is the one that the query is not picking up on.i have checked the joining properties and they seem fine, i have them left joined
Richard Rost on 2/26/2009: Debbie, again this doesn't help me. I guess I would need to see what you're doing and how your database is laid out in order to help you. If you have a query based on a new table and it's not pulling records from it, then something is either wrong with your query, or your data isn't making it to the table. Just based on the description you've given me, however, it's impossible for me to tell. Why don't you submit this to me, along with a copy of your database, to my TechHelp page.
Lynn Shankles on 11/6/2009: Hi. I need help with an Access problem. I have a form with several tabs to track production. One of the table is a total tab that pulls information from the tufting, latex, powder and felt tabs. I am trying to create a IIF then statement that says if there is no date in the felt tab go to the powder tab. If there is not date in the powder tab then go to the latex tab and so on and so forth. Can you help me with the forumula?
 Chris on 3/30/2010: Access 207.2, TI 7:50 Using Access 2000.

When I create the Macro shown in the instruction, the "Not Recurring" Tasks do not get set to complete.

I found that in all cases of the value of RecurringCombo (0...6), the Macro executes all instances where we set the value of Completed to False. As well, through the execution of the macro, where the one condition line matches the value of RecurringCombo, this line will also exceute.

This means that when I click the completed checkbox on a task that is "Daily", I will run the condition where I change DueDate by one day; but I will also set Completed to False six (6) times as the macro progresses to the end.

In the case of where RecurringCombo equals "Non Recurring", there is no conditional match and so the due date cannot not get set - as expected; but the Completed value will get set - 6 times to false.

To make this work in my environment I slightly changed it as follows:

[RecurringCombo]=1 SetValue Daily
... SetValue

For each "SetValue" where there is no condition defined and we set Completed to False, we need to add an ellipsis ("...") in the condition field to indicate that this action takes place if the same conditions defined above the ellipsis are satified. This way, SetValue of Completed to False only gets executed once, and in the case of RecurringCombo equals "Non Recurring", SetValue of Completed does not ever get executed. This allows the value of the check box to remain set to true.

Jim Gray on 12/24/2010: Mr.Rost,

Is there any reason to keep a history of completed tasks? If so, how would you renew the task as well as archive the original completion along with any notes perinent to that particular event? One more question: What happens if you go past the original due date of a task and then complete it or should you just skip it at that point?

Reply from Richard Rost:

Sure. You can save your completed tasks. That's what the IsCompleted field is for. When they're done, check 'em off. Don't have completed tasks show up in your reports or forms. Now if you want to track all of the histories for RECURRING tasks, that gets a little more complicated. What you could do is have the database create a new, identical task when you mark one task completed. This way you have all of the details, notes, and data on the old task, and a NEW task with a new date has been added.

victor victor on 3/21/2011: Hi Richard,
I can't find setvalue in the list of macro in my system. I use windows 7 and office 2007.
Please has the name changed to something else? I couldn't do date setting in 206

Reply from Richard Rost:

This was addressed in the User Forum for Access 206. I'll repost it here:

There is an embarrassingly easy solution to the SetValue problem. Earlier, students mentioned that SetValue doesn't seem to exist in Access 2007. Well, it's there - just hidden. While making your macro, click on the Macro Tools > Design ribbon tab. In the Show/Hide section, click on the SHOW ALL ACTIONS button. Now look at the list of available macros. You'll see SetValue there. Access tries to only show you the "safe" commands unless you specifically ask for the dangerous ones.

victor on 3/21/2011: Thank you Richard, I have done that and it worked
 eddy geijselaers on 5/30/2011: Dear Rick,

After working through Access 206 and 207 I have a few questions/problems.
First of all I did not succeed to build in a weekday field into the TaskListF. I copied it from the TaskF where it works perfect. After trying several options and comparing the properties on both Forms I just can’t figure out what’s wrong or were I went wrong. Like in th TaskF its in the same section so that shouldn’t be the problem. Properties are set write, also the Names and sources.
I know it’s not in the course but it would be nice to see it also in the TaskListF.

Secondly I cannot get the “later today” button to work properly. Same problem; on the TaskF it works fine. I followed several times the instructions and video but I cannot figure out this one either.
The result is in the best case a number instead of date and time.

Thirdly ….. is there any way to set a value to pop up the TaskF in a proper manner?
On my PC this Forms is coming up every time I open the TaskListF or change the selected row, in front of the TaskListF. No matter how or from what position I save or close them.

And although in the lesson you set the Filters on TaskListF (Task2Q) your filter didn’t work on first attempt on my PC it worked fine. So I checked and guess what I set the criteria without “=” up front.
Only problem it works only with one field and the “all tasks” checkbox must be checked (on).
What about this?? Probably MS…
And/or because at this point I didn’t remove the “newrecord-line” beneath the tasks in the form.

The Filter on the Description I didn’t managed to get working, so I removed it (for now)

After doing 3 times the 207 lesson I give up because I am certain the spellings are correct and everything else is like in the lesson.


Thanks in advance and greetings from Holland
Eddy Geijselaers

Alex Hedley on 5/30/2011: Hi Eddy,

Have you tried comparing your db to the Sample db Rich provides?

I've debugged for quite some time and it's usually a silly spelling error or I've missed a crucial line because I've been starring at the code for so long.

Hope you mange to ort it out,

Al

eddy geijselaers on 6/1/2011: Thanx Alex, done that also a couple of times. Let it rest for a day or two and done it over again. Not the expected result either.
Of course I could use Rick's DataBase but then I wouldn't learn to much, would I?
I know in Rick's DataBase is a lot more code (VBA/SQL) then when we build it from the lesson but most stuff works without this code. And then if needed I would like to learn that too!

Greetz
eddy

eddy geijselaers on 6/2/2011: comment on my 5/30 questions;

I solved the problem with the weekday field in the TaskListF by "steeling it" again from TaskF and now it worked!
What did I do wrong? Well first time around I changed the rowsource to TaskViewDate on the TaskListF. Didn't realize this would make a difference till lesson 220/221 were Rick pointed this out from another angle.

Now that my weekday is functioning, Access saved the positions of the two forms as were I saved them the last time using them. Again a problem solved.

Strange enough the later today button functions like it should now the weekday field works properly.

there is still the issue of the sorting on description that I have to figure out.

Thankx again

 Elaine on 7/22/2011: Richard: The Macro for our Reset buttons,the macros for todays date we put Date() in the Expression. and Date()+1 for Tomorrow Can't you use the Set Tomorrow and Set Plus One as one Button. Any reason you can't use Date()+31 if you wanted to do by month instead of typing in Forms!TaskF!DueDate+31. I've tried the above and it works fine for me, but I changed it to the way you did it. I didn't want to run into trouble later because I didn't do it correctly. Somtimes I will venture off on my own and try different things, but I always change them back.
I am using Access 203

 Elaine on 7/28/2011: Recurrigcombo will not work for me I have read the post on using the ... I have copied and pasted the previous condition, I get the error (The object dosen't contain the automation object recurringcombo) I have checked my macros for misspellings and everything can't figure it out.
Ryan Bessler on 7/31/2011: I am trying to create a search form for an appraisal company database. I have about 40 fields that I want the user to specify a property type, address, city, etc. In addition, there are other fields that I want them to be able to specify a minimum and/or maximum. For example, a min/max sale date, building size, year built, etc. Is there a specific lesson that would deal with tying the search parameters specified in the form to a query and how do I set up the search parameters in the query. Thanks for the help.
Alex Hedley on 7/31/2011: Ryan there is a mini search tutorial from Richard but there will be a seminar out soon [EST Aug 12: Access Search Form Seminar]
Benjamin Chua on 10/14/2011: On Macro Condition;
If I create a combobox whether it is a Straight time or Overtime, How do I say the Action to let the user enter the rate in the STratefield or OTratefield? Or is there another way of doing this. Currently I am on Class 207.

Benjamin Chua on 10/14/2011: Access class 207 time 7:40 to 9:06

My AreWeHiding Field is empty

It is not even showing either -1 or 0

What am I doing wrong?

Ben Chua on 10/16/2011: Comment on 10/14/2011
Regarding Macro: I answered my own question.

I use an IIF Statement. I created UnionRate and NonUnionRate.

LaborAmount:IIF(IsUnion=True,UnionRate*Hours,NonUnionRate*Hours)


 Ron on 11/18/2011: Iam getting the following error when I click the task completed box in the TaskF. The object doesn't contain the Automation object "RecuringCombo

Reply from Richard Rost:

Check your spelling. Make sure the control is named properly.

Ron on 11/18/2011: I am at minute 6:03 in 207 recurring tasks. I have placed the following in may macro:
Condition Action
'forms[recurringcombo]=1' SetValue
Arguments
Item [Forms]![Taskf]1[duedate] Expression Date()+1
Condition Action
... SetValue
Arguments
Item [Forms]!TaskF]![Completed]
Expression False

Then I just copied this to the weekly, bi-weekly, monthly etc...

Everytime I check the completed it changes te date to a year in the furure. What am I doing worg.

Reply from Richard Rost:

Did you TYPE all of that in or copy and paste it? If you copied it, then you've got a lot of things typed in wrong:

forms[recurringcombo]=1
[Forms]![Taskf]1[duedate]
[Forms]!TaskF]![Completed]

Remember it's:

Forms!TaskF!DueDate

I suspect the problem is with something that's not typed in correctly.

 Ron on 11/29/2011: I have the same issue an Benjamin.
 Ron on 12/8/2011: Access 207 #3 Hide Future Task time 6:47. When I run the If In Future query I am asked for a TaskViewDate.
 Ron on 12/8/2011: Access 207, Session 3, Hiding Fiture Tasks, time 8:55. I have run the Task query and the AreWeHiding I get a new field but there are no ones or zeros populating the field.
 Ron on 12/8/2011: Access 207, Lesson 3, minute 16 I keep getting the enter parameter box for TrueandIsInFuture
G on 5/25/2012: I need an IIF function to Schedule an up coming date.

I have a DOR (date of review) column and a Outcome column which has drop down box function where I input trainee outcomes for their after their yearly reviews.

IF the outcome is less than 3 then the date needs to be schedule an mm/yyyy from the date of their review and if it is 3 or more then they need to come back 6 months from the date of their review - how would I do that?

Reply from Richard Rost:

You don't need an IIF function, just a little VB code. Use an AfterUpdate event for the combo box to set the value. It looks like your Outcome value is a NUMBER(?) from what you've said here. So you'd say something like this in your event:

If Outcome < 3 Then
DateOfReview = DateAdd("m",1,Date()) ' add 1 month to current date
Else
DateOfReview = DateAdd("m",6,Date()) ' add 6 months to current date
End If

James Miller on 2/27/2013: Hi Rich, I poked around in the wizard tool and found that the following is what I had to add to the Value part of the SetProperty Macro.
=DateAdd("d",1,[Forms]![TaskListF]![TaskViewDate]) For subtracting a day I had to use =DateAdd("d",-1,[Forms]![TaskListF]![TaskViewDate]). That worked.

James Miller on 3/2/2013: Hi Rich. I am using Access 2010 and the macros are quite different than the ones you show here. When I try to use the [Forms]![TaskListF]![TaskViewDate] it gives me an error telling me I misspelled it or refers to a control that does not exist. None of that is true. It all works in the TaskFormButtons, but not in the Task List Buttons. I changed everything like you did. I Found that in 2010 you don't have to enter in all the forms! etc... Now I get a type mismatch on the add a day button ,but the Today and Tomorrow buttons work fine. I am stumped.

Reply from Richard Rost:

I will be updating this lesson very soon and will address the changes in macros then.

Jim on 10/21/2013: Rick,

Do you save your completed tasks in some archive file? I am creating a recurring checklist for our ambulances and using recurring tasks to set daily, weekly, monthly, etc. for the rig checks. I have a need to maintain that history for both internal auditing and legal purposes. Should I just set up an archive file, use an after update event on the completed checkbox to add these tasks to this file (Insert Into statement) or have I over-simplified the process?

Reply from Richard Rost:

You can just leave them in the main tasks table, marked COMPLETED. If you find your database file is getting too large, then go ahead and archive them, as I showed in the append/delete query lessons.

Emad on 2/5/2016: Hello, Access 2013-Recurring Condition does not work with me, I convert the Macro to VBA to show you what I did
'------------------------------------------------------------
' TaskCovertto_VB_RecurringM
'
'------------------------------------------------------------
Function TaskCovertto_VB_RecurringM()
On Error GoTo TaskCovertto_VB_RecurringM_Err

With CodeContextObject
If (.RecurringCombo = 1) Then
Forms!TaskF!DueDate = Forms!TaskF!DueDate + 1
' Daily
End If
If (.RecurringCombo = 2) Then
Forms!TaskF!DueDate = Forms!TaskF!DueDate + 7
' Weekly
End If
If (.RecurringCombo = 3) Then
Forms!TaskF!DueDate = Forms!TaskF!DueDate + 14
' BiWeeks, means 2 weeks
End If
If (.RecurringCombo = 4) Then
Forms!TaskF!DueDate = Forms!TaskF!DueDate + 30
' Monthly
End If
If (.RecurringCombo = 5) Then
Forms!TaskF!DueDate = Forms!TaskF!DueDate + 90
' Quarterly
End If
If (.RecurringCombo = 6) Then
Forms!TaskF!DueDate = Forms!TaskF!DueDate + 360
' Annually
End If
End With


TaskCovertto_VB_RecurringM_Exit:
Exit Function

TaskCovertto_VB_RecurringM_Err:
MsgBox Error$
Resume TaskCovertto_VB_RecurringM_Exit

End Function

Reply from Alex Hedley:

What happens instead?

Emad on 2/5/2016: The Macro TaskRecurringM working but, if I open the form I got this message "Access was unable to locate the macro or VBA function. If you are trying to call a macro, make sure that the name of the macro and the name of the macro group are spelled correctly.
If you are trying to call a user-defined VBA function, be sure to use the following syntax:
=FunctionName() or =FunctionName(argument1,argument2,...)
Make sure that the function is either:
Defined in the code for the form or report.
- or -
A public function that is in a module (not a class module)".
Also, I faced problem, The CheckBox control behavior different, like you have to click it 3 times?!.

Reply from Alex Hedley:

Did you rename the macro but not change the button click event?
Have you set the checkbox to triple state?

Emad on 2/5/2016: Hello, Why did you type the IIF programing at TaskQ Filed, instead of Criteria? I mean why you did not type your programing at Criteria place?

Reply from Alex Hedley:

You can create a Calculated Field instead

Emad on 2/6/2016: Hello, How can you make Macro for [DescriptionFilter] after you update it let * come back again?

Reply from Alex Hedley:

Are you wanting to clear your search?

John H on 4/16/2017: "I will be updating this lesson very soon and will address the changes in macros then".
Where are the updates to this lesson??
Thanks in advance

Reply from Alex Hedley:

Check out the Advanced Lessons

 

You may want to read these articles from the 599CD News:

 

Learn
 
Accessindex
Excelindex
Wordindex
Windowsindex
PowerPointindex
Photoshopindex
Visual Basicindex
ASPindex
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Free Upgrades
Insider Circle
Student Databases
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Articles
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Help
 
Live Chat
Customer Support
WalkThru Tutorials
Troubleshooting
FAQs
TechHelp
Consulting Services
About Us
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Gift CDs
Contact
 
Live Chat
General Info
Support Policy
Contact Form
Email Us
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

Google Plus    LinkedIn

Blog RSS Feed    YouTube Channel
Richard Rost Microsoft MVP