| |
| |
 |
|
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 5/20/2012 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.
|
|
|
| |
|
You may want to read these articles from the 599CD News: |
|
|
|
