| |
| |
 |
|
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 |
| Versions: |
|
We use Access XP in this course,
but the lessons are valid for all versions of Access from 95 to 2003.
There are cosmetic changes in Access 2007. Order before 3/15/2010 to
get a FREE upgrade to our 2007 version when released!
|
|
|
|
|
| |
 |
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 |
| |
| |
|
 |
Huge Discounts Available
When you purchase multiple classes together
Huge savings up to 50% off! Order Now. |
|
| |
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. |
|
|
| |
| |
|
You may want to read these articles from the 599CD Blog: |
|
| |
|
 |
| |
| Do you have
questions about
Word, Excel, Access, Web Design, or computers in general? Just
ask us anything you'd like.
Click here
for assistance. |
|
|
|
|
|
| |
|
CLICK HERE for a FREE
lesson |
Order
your first 599CD course now.
Your Satisfaction is Guaranteed!
|
|
|
|
|
|
| |
|
|
|
|
