ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 206: Task Management, OnCurrent Event, GoToRecord, GoToControl, Expression Builder, Named Macro Sections
 

4/24/2017: You may see an "operation not allowed" error on the site. We're working on fixing the problem. Nothing seems to be affected, it's just annoying. Carry on. :)   [dismiss]
 
Access 2007-2013
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 Advanced Level 4 and Level 5   dismiss
 
 
  If you want to make Microsoft Access forms and reports that LOOK like real monthly Calendars, then take a look at my Access Calendar Seminar.

This class (Access 206, below) covers scheduling and task management, but doesn't actually print a monthly calendar report.
 
Courses - Microsoft Access 206
Description: Intermediate Microsoft Access 6
Running Time: 68 minutes
Pre-Requisites: Access 205 very strongly recommended
Previous Lesson: Access 205
Next Lesson: Access 207
Main Topics: Task Management, OnCurrent Event, GoToRecord, GoToControl, Expression Builder, Named Macro Sections
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 4/27/2017 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 206
Intermediate Access 6

Task Management System, Synch Records on Two Forms, Set Values with Buttons, More Macro Tips, More. 68 Minutes.
 

AC206 Major Topics

  • Task Management System
  • Named Macro Sections
  • On Current Event
  • GoToRecord, GoToControl
  • Expression Builder

In this class, and Access 207 which directly follows, we will be building a Task Management System. We will teach you how to manage your tasks (to do list). We will learn how to synchronize the records on two forms. You will learn how to do more with buttons (like set field values). We will do more with Macros.

We will begin by setting up our Task Management table, the supporting tables, and forms. This will include fields to give each task a priority (high, medium, low, etc), a category, and whether or not the task is recurring.

 

While working on this form, we'll assign macros to the On Double Click events for each of the combo boxes - that way you can double-click on each combo box to open up a supporting table where you can edit the list of priorities, categories, and so on. In order to do this, we'll learn about Macro Names, where you can group sections of related macros together.

 

Next we'll make a Task List form which is a continuous form that will allow us to see all of our tasks in a nice, compact format. It's basically our "To Do" list.

 

Next we will learn about the On Current event, and how we can use it to synchronize the records on two forms. When we click on a record on our Task List form, the related Task will open up in the Task Form. This is a really powerful (and really cool) technique.

 

We'll learn some new Macro commands, like GoToRecord, GoToControl, and SetValue. You will learn how to turn the Wizards off and manually assign macros to buttons.

 

We'll take a look at the Expression Builder. Even though I personally don't use it often, I'll show you how it works.

 

We'll create some buttons on our Task form to change the task due date to today, tomorrow, plus a day, minus a day, and more. The focus here is to make buttons that can change field values.

 

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 206 Outline
 

Lesson 1. Setting up Task Tables
Create TaskT table
Create PriorityT table
Create CategoryT table
Create RecurringT table

Lesson 2. Setting up the Task Form
Create a TaskF simple form
Create combo boxes for Priority, Category, Recurring
Modify the Row Source for each to Sort the boxes
Create a macro to open each table when combo box dbl-clicked
On Dbl Click event
Macro Names (grouping macros)
Macro to open supporting tables

Lesson 3. Task List Form
Creating a Task List continuous form

Lesson 4. Synch Task List to Task Form
Creating a macro to open a specific form
Setting the OnCurrent event in the Task List Form
GoToRecord macro command (macro action)
GoToControl macro command
Creating a command button without the wizard
Assigning a macro to a command button
SetValue macro command
Using the Expression Builder
Creating buttons to set the due date to today

Lesson 5. More Date Buttons, Requery Button
Button to set due date to tomorrow
Button to set due date plus one, minus one day
Learn about adding fractions of a day
Button to set due date to "later today"
Creating a second bound date field with different format
Creating a button to requery your form results

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 206

Richard on 1/1/2007:  Task Management System, Synch Records on Two Forms, Set Values with Buttons, More Macro Tips, More. 68 Minutes.
Judith Koester on 3/25/2008: Everytime I copy anything it pastes in the upper left corner. If I am the bottom of screen I can't see it. How can I get whatever to paste where I want it without dragging it into position?
Richard Rost on 3/25/2008: Judith, I don't understand your question. Can you elaborate please? Is this a problem with Access or your computer in general?
Judith on 3/26/2008: As an example, in ACCESS lesson 103, 5, Lead Sources, you copied and pasted the customer box. When I paste that, it goes to the very upper left corner of the form. If I am in a report footer, whatever I paste will go to the upper left corner of the footer. Seems to be an Access thing. Hope this explains my question more adequately.
Richard Rost on 4/1/2008: You're right - it's an Access thing. Now, if you still have the ORIGINAL OBJECT selected, when you PASTE that new object should go right below the original. However, if you de-select the object you copied, then any pasted objects are going to go to the upper-left corner of the section you're in. I don't know of any other way around this behavior.
Walter Pohle on 4/30/2008: in 206 Video 5. I noticed that after you made the button for Requery and after you input item 9 the
AutoNumber row is not visable any more Did Miss a
step some where

Richard Rost on 5/24/2008: Walter, I think I understand your confusion. At about time index 13:29 in the video, the new BLANK record at the bottom of the form (that says "AutoNumber" in it) disappears. That's because we added a new record, and so the new blank record moved DOWN one - which is off the screen. If you scroll down on your form, you'll find it. It just happens that my window was small enough to only display 9 records. If you scroll down, you'll see the blank row.
G Owen Williams on 9/2/2008: In Video 206, 05 @ 4:33 You add a SetLaterToday button using Date()+.25
...but isn't Date() time actually midnight?
..so .25 would be 6:00 am.
Please explain.

Richard Rost on 9/8/2008: Owen, I don't understand your confusion. I just rewatched the lesson and it's correct.

Date()+0.25 would be 6am, correct. Date()+0.50 would be noone. Date()+0.75 would be 6pm, or Date()+(18/24) like I showed in the lesson around time index 6:00.

Date() returns TODAYS DATE as of midnight (this morning). If you add a fraction to it, you get a time component.

If you still don't understand, let me know.

G Owen Williams on 9/8/2008: There is my problem. I mistook your midnight term as being end of the day and not 0:00 AM at the start of the day, which makes sense. Date() = 0:00 AM, Now() = Exactly now.
Richard Rost on 9/8/2008: Owen, you got it! Midnight is 00:00:00 which is technically the start of the day. Glad I could help clear this up for you.
Margaret Cattarin on 9/20/2008: Richard,
In my db, I built a list form with check boxes next to each item. In my query, I have the criteria for the check box set to Yes, so that I can go down the list and select the items that I want to appear on my report.
How to I build a macro that will clear the check boxes when I open the form? Then, I won't have to go through the list checking and unchecking boxes each time I use the list.

Richard Rost on 9/21/2008: Margaret, you don't need a macro, just an UPDATE QUERY. I cover this in Access 222. The Update Query can reset all of your checkbox values to NO. You can run the Query with a command button. Problem solved. :)
Richard Jenkinson on 9/26/2008: Note for Access 2007 users who can't find the macro action 'SetValue'- click 'Show All Actions' in the Macro Design tab and 'setvalue' will then appear in the actions drop down menu.
Richard Rost on 9/27/2008: Richard, thank you very much for the update. I haven't had a chance yet to go through all of my lessons with Access 2007. When I do, I'll be noting things like this here too, but I *REALLY* appreciate it if anyone who is using Access 2007 NOW finds any little discrepancies to post them here!
Fred Alston on 12/14/2008: I would like to be able to double click on a text box and todays date is displayed. I've try placing "=Date()" in the text box properties, Events, On Double Click but it didn't work. Helpa
Richard Rost on 1/19/2009: Fred, you want to put code in the OnDoubleClick event for the text box. Something like:

MSGBOX Date()

would work to display the date. Or, if you want to SET that box to the current date, say:

MyTextBoxName = Date()

Easy enough?

mehul on 1/22/2009: Hi Richard ,
the time formula in excel works are follows =time(hh,mm,ss) . This returns the a composite value that shows time. I was wondering if there is such a function in Access . This is the function from the excel sheet that I am trying to convert to access format but it does not work since time function in access is different

=IF(AND(C2>=TIME(HOUR(C2),0,0),$C$2<=TIME((HOUR(C2)),30,0)),(HOUR(C2)&":00-")&(HOUR(C2)&":30"),(HOUR(C2)&":30-")&(HOUR(C2)+1&":00"))

Richard Rost on 1/22/2009: Mehul, what do you HAVE, and what are you trying to turn it INTO?
Prasanna Kumar on 2/15/2009: Hi, I have condition to be put as below:
IIf("End Month"="On hold","On hold",IIf("WR Received Date"="","",IIf("Actual End Date"="","WIP","Completed")))
Note: End month,WR Received Date & Actual End Date are column in the table. On hold,WIP & completed are status to be show as per formula.

Richard Rost on 2/18/2009: Prasanna, you need to make sure to put your field names inside of BRACKETS, not quotes. IIF([End Month]="on hold"...
 Ellen on 7/13/2009: In Lesson 4, at time index 3:35, I assigned the macro to the TaskListF but the appropriate TaskF doesn't open; instead a blank task window opens and it says at the bottom 1 record (filtered). Do you know what I'm doing wrong?
Richard Rost on 7/15/2009: Ellen, it's almost impossible to tell what you've done wrong without seeing your database. Try creating it again to make sure you didn't miss a step. Also, try restarting your database. Sometimes if a form is already open in the background, the macro won't work.
 Carolyn Quinn on 8/12/2009: I would like to show who i assigned the task to, is this possible?
Richard Rost on 8/15/2009: Carolyn, you'd need a table with your usernames in it, and then just add a UserID to your task table.
T-Bone on 10/1/2009: I am building this database in Access 2007 and this Macro will not work in 2007. I went to my Access 2003 machine and it works fine. What is the difference in the Macro's between the two versions?
Richard Rost on 10/2/2009: T-Bone, macros have changed a lot. They're now "compiled" and behave a little differently. I have NOT tested the macros in this class with 2007 yet. I will try to do so soon.
Richard Rost on 10/2/2009: 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.
David Ivens on 11/8/2009: Thanks - I have been hunting around for Set Value for weeks. Still experiencing difficulties in transferring my databases to Vista (ugh!). For some reason they refuse to work as they did under SP. Is it to do with this 'Trusted Database' status?
Richard Rost on 11/11/2009: David, are you having problems upgrading to Office 2007, or running Access 2003 databases on Windows Vista? Big difference.
 Chris on 3/28/2010: Ref: TI 3:39. I am using Access 2000.

When we click on different records within the TaskListF, I see that TaskF opens the corrresponding Task and the forms are synched. When I execute my version, I get the TaskF open and initially the record selected in TaskListF appears. This is good. However any subsequent selections within TaskListF, TaskF flashes; but does not change to the TaskID that was selected. If I close the TaskF and click on another TaskListF record, The TaskF form does open with the correct task as was done initially. The TaskListF Control is set to use OpenTaskM. This Macro opens TaskF and the where condition is [TaskID]=[Forms]![TaskListF]![TaskID]. I've check for some rouge Macro on an event somewhere that I may have added; but found none. The problem almost appears as if the TaskF form is opening before the OnCurrent on TaskListF has finished... (I know it isn't; but it "appears" like that). I've gone through your material many times now and I get the same result. Any Ideas?

Reply from Richard Rost:

Chris, send me a copy of your database. I'm unable to replicate this behavior. Is anyone else having this problem?

 Chris on 3/28/2010: Follow-up to my Access 2000 synch issue between the TaskListF and TaskF.

I could not get the macro OpenTaskM to work no matter what I tried. However, I was able to get the same failure to occur when VBA was used, and was able to correct the problem.

To Replicate the problem, I used a little VBA that replaced the Macro in the OnCurrent event. I issued:

DoCmd.OpenForm "TaskF", , , [TaskID] = Forms![TaskListF]![TaskID]

To fix it, I changed the code to:

DoCmd.OpenForm "TaskF", , , "[TaskID] =" & Forms![TaskListF]![TaskID]

If I use the above correction to the Macro, I continue to see the initial error.

Reply from Richard Rost:

If Access 2000 is writing the macro as you have shown above (the first one) then that is incorrect. The problem is that Access is creating bad code (go figure). This is one of the reasons I don't really like using macros ESPECIALLY in the older versions of Access (straight VBA coding is almost ALWAYS better). However, we don't start getting into VBA programming until Access 301.

Good catch, and good fix. That IS the correct solution, and that's what the macro should have written.

 David Sterner on 8/18/2010: Richard: For the task list: Is there a simple way using VBA code to include an recurring "every Friday" or other day of the week as opposed to once a week. Also, you mentioned there is a way to do monthly in a more efficient way than every 30 days, taking into account the variation in month length - how do you do that?
DS on 9/1/2010: Hi Richard or anyone....

I am having problems with the synching of the 2 forms. i.e the TaskF and the TaskListF.

Richard are you saying that every time you click on a different record that the matching id on the Task F should change. OR Do you have to close Task F; and then hit another record.

Let's know, pleas.

I have 2 softwares the 2000 and 2007; I have not yet tested 2007 (in progress)

Judith Koester on 9/24/2010: Where is tab stop in Access 2007. Can't find it using help or search.
Judith Koester on 9/24/2010: Never mind about the tab stop question on 2007. I found the problem. When I highlighted Task ID and Created Date, tab stop appeared, but when I added Completed to those 2, it disappeared.?? Thanks.
Judith Koester on 9/24/2010: Found the problem, I was hghlighting the label and not the check box.
Bill Donegan on 11/16/2010: Chris, Many Thanks for your solution...I might argue with Richard that he should have at least shown us how to get at the VBA code...and how to make correction if it's an Access bug in 2000. Bill

Reply from Richard Rost:

I suppose... didn't want to confuse people with VBA. That's what the advanced lessons are for.

 Emad on 2/14/2011: Time:8:70
What is the difference between Category Table that you did it Manualy and betwen the Lookup Wizard?

Reply from Richard Rost:

Emad, I don't understand your question. Can you elaborate a little more for me, please?

  on 8/8/2011: I have been taught that the number data type should only be used when the values will be calculated. I would have expected that the category data type would have been text. Please explain. Thanks
Ron on 12/2/2011: Make sure you are in the form view and not the layout view.
Carl on 11/7/2012: Using access 2007 and working on lesson 206/4. I cannot find the setvalue action. Can you help the blind man see it. I can see a setproperty but that does not let me set a value.

Thanks
Carl



Reply from Richard Rost:

Whenever you're stuck on something ALWAYS try a SEARCH on my web site. You'll see this has been answered before: SetValue.

And remember, keep your search query simple. My search engine is not as elaborate as Google. :)

Rod on 1/1/2013: Richard this is the second lesson that refers to using a Macro Name. Where in Access 2010 would I find that?

Reply from Richard Rost:

I believe it's the same issue as THIS.

Roderick B on 1/3/2013: Thanks.
Timothy Ross on 1/17/2013: I am using Office 2010 and following along, but when I create the Macro for Todays Date and Tomorrows Date. When I try and run the Macro I get the following error, "The expression you entered has a function name that PCResale.Net Customer Database can't find." I have redone the Macro a few times, with the same result. Help, Please.

Reply from Richard Rost:

Sounds like something is typed in wrong or misspelled. I'd need to see your database to tell you for sure. If Access doesn't recognize a FUNCTION name, then that's usually caused by a misspelled something, somewhere. Email me a screen shot of your macro and the error message if you can. amicron@gmail.com.

Michael Cronin on 2/13/2013: Hi,

Using 2010 and clicked on show all actions, and the setvalue appears, thank you, was stumped for a bit there.

Mick

James Miller on 2/26/2013: Hi Rich. On my Task List, I cannot get the page header to show up. I am running 2010 Access. Help

Reply from Richard Rost:

It works the same as 2003 and 2007. Just right-click in the detail section and select PAGE HEADER/FOOTER.

Don on 4/30/2013: Because I am working with Access 2010 and the course is in 2003, there are some differences in the way each one functions, in most cases i have been able to find the answers, but with this one i can't.

I am saked to press the button to turn Macro Names on and there is no button like this in 2010, nor anything about macro names in the help menu.

How do i make thie function work in 2010 as i am unable to continue with Access 206??

Any help you can offer will be greatly appreciated.



Don, Macro Names have been replaced with SUBMACROS in Access 2007/10/13. Just insert a submacro instead. I'll be covering this very soon when I get to it in the Access Expert series (almost there).

Emad on 2/3/2016: Hello, I could not find "setValue" on Macro for Access 2013, kindly can you tell me how can I see it?


Reply from Alex Hedley:

Have you set it so you can see ALL macros, some are harmful and hidden by default.
See this Thread.

 

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