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  
 

News      User Comments     History     Notify Me

1/1/2007
Microsoft Access 206
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

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

Permanent Link
Course Link: Microsoft Access 206
Keywords: access
Post Reply

Macro Names Comment from Don @ 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).
Show Just This Thread        Post Reply
Task List Page Header Comment from James Miller @ 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.
Show Just This Thread        Post Reply
Comment from Michael Cronin @ 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
Show Just This Thread        Post Reply
expression you entered has a function name Comment from Timothy Ross @ 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.
Show Just This Thread        Post Reply
macro name Comment from Rod @ 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.
Show Just This Thread        Post Reply
Access 2007 SetValue Comment from Carl @ 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. :)
Show Just This Thread        Post Reply
Comment from Ron @ 12/2/2011
Make sure you are in the form view and not the layout view.
Show Just This Thread        Post Reply
Comment from   @ 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
Show Just This Thread        Post Reply
Comment from  Emad @ 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?
Show Just This Thread        Post Reply
Comment from Bill Donegan @ 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.
Show Just This Thread        Post Reply
Comment from Judith Koester @ 9/24/2010
Found the problem, I was hghlighting the label and not the check box.
Show Just This Thread        Post Reply
Comment from Judith Koester @ 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.
Show Just This Thread        Post Reply
Comment from Judith Koester @ 9/24/2010
Where is tab stop in Access 2007.  Can't find it using help or search.
Show Just This Thread        Post Reply
Access 206 Comment from DS @ 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)
Show Just This Thread        Post Reply
Comment from  David Sterner @ 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?
Show Just This Thread        Post Reply
Comment from  Chris @ 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.
Show Just This Thread        Post Reply
Comment from  Chris @ 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?
Show Just This Thread        Post Reply
Set Value Comment from Richard Rost @ 11/11/2009
David, are you having problems upgrading to Office 2007, or running Access 2003 databases on Windows Vista? Big difference.
Show Just This Thread        Post Reply
Set Value Comment from David Ivens @ 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?
Show Just This Thread        Post Reply
SetValue Macro in Access 2007 Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from T-Bone @ 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?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 8/15/2009
Carolyn, you'd need a table with your usernames in it, and then just add a UserID to your task table.
Show Just This Thread        Post Reply
Comment from  Carolyn Quinn @ 8/12/2009
I would like to show who i assigned the task to, is this possible?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from  Ellen @ 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?
Show Just This Thread        Post Reply
How to use advance if condition Comment from Richard Rost @ 2/18/2009
Prasanna, you need to make sure to put your field names inside of BRACKETS, not quotes. IIF([End Month]="on hold"...
Show Just This Thread        Post Reply
How to use advance if condition Comment from Prasanna Kumar @ 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.
Show Just This Thread        Post Reply
Time function like in excel Comment from Richard Rost @ 1/22/2009
Mehul, what do you HAVE, and what are you trying to turn it INTO?
Show Just This Thread        Post Reply
Time function like in excel Comment from mehul @ 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"))
Show Just This Thread        Post Reply
On Double click Comment from Richard Rost @ 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?
Show Just This Thread        Post Reply
On Double click Comment from Fred Alston @ 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
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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!
Show Just This Thread        Post Reply
Comment from Richard Jenkinson @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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. :)
Show Just This Thread        Post Reply
Comment from Margaret Cattarin @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from G Owen Williams @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from G Owen Williams @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from Walter Pohle @ 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
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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.
Show Just This Thread        Post Reply
Comment from Judith @ 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.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 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?
Show Just This Thread        Post Reply
Comment from Judith Koester @ 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?
Show Just This Thread        Post Reply

Add Your Comments or Subscribe
 
If you just want to subscribe to get email updates when the News is updated, then enter your name and email address and check the Notify Me box below. If you would optionally like to add your comments below to be posted, they are welcome.
 
  Your Name:  Required
  Your Email:  NOT Public
  Subject:
  Comments:

 

Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 4+2:
  
  Notify me when the News is updated.
  Remember Me for my next comments
  
 
 
Please do not use this form for Customer Service inquiries! If you have questions about your account, shipping info, courses you've ordered, need passwords, etc. please use the Customer Service Center instead.

I value your comments. They will be displayed on this page (above). Your name will be displayed, but your email address will not be.
As always, I promise to never give away your personal information to anyone else, ever.

NOTE: If you don't leave your name and email address, DON'T expect a reply. I can't promise a personal reply to everyone who posts here. I TRY my best, but I cannot guarantee it. If you don't leave your real name and email address, I won't even bother. I usually just hit DELETE. -Richard

 

 

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

11/20/2018Microsoft Access Developer 12
8/25/2018NEW: Access Dev 9, 10, 11
8/25/2018Microsoft Access Developer 11
8/25/2018Microsoft Access Developer 10
8/25/2018Microsoft Access Developer 9
8/23/2018Access Dev 11 is ONLINE
8/17/2018Access Dev 10 is ONLINE
8/15/2018Access Tip: Search Form
8/15/2018Access Tip: Locked v. Enabled
8/15/2018Access Dev 9 is ONLINE
 

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
Change Email
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