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

Microsoft Access 204
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on Google Plus Richard Rost on LinkedIn Email Richard Rost

Designing a Letter Writer, Access Macros, Events, OnDblClick Event, Refresh Macro, Much More. 65 Minutes.

Permanent Link
Course Link: Microsoft Access 204
Keywords: access macro event ondblclick refresh
Post Reply

The macro apparently does not work in Access 2013 Comment from Rick Shafer @ 3/3/2013
Bah... No need to address this.  I got cocky and thought I could follow along as I was watching the video.  It was the part where you intentionally only put in the part of the equation...  =[Forms]![ContactF]![CustomerID] so that you could show that the macro field needs both sides of the equation [CustomerID]=[Forms]![ContactF]![CustomerID].  If I had just watched a few more seconds I would have been given the answer.  I was just caught up on why it was not working for me.  :)  Ooops.  Learned my lesson to watch FIRST, then try and re-create the information.  However, I won't soon forget that macros require the full equation.

Reply from Richard Rost:

Ah. You're right. I should have remembered that, but it's been almost NINE YEARS since I recorded that video (release date Sept 2004). I'll be revising it soon... working on Expert 2 now which is the equivalent of 202.
Show Just This Thread        Post Reply
The macro apparently does not work in Access 2013 Comment from Richard Shafer @ 3/2/2013
The macro apparently does not work in Access 2013.  When I follow all that you do, it only opens the first customer of the customer form.  I am putting in =[Forms]![ContactF]![CustomerID] and no matter what contact record I am on on the ContactF, it only opens up the ver first customer on the CustomerF.  Is this different in Access 2013 or am I doing something wrong?

Reply from Richard Rost:

I'll be addressing this issue shortly.
Show Just This Thread        Post Reply
Deleting Relationships Comment from Robert Fitzgibbons @ 1/17/2013
This worked great.  I did it about a week ago and have held off on my reply to make sure there were no bugs.  But the database is working perfectly.  Thanks a lot.  

Reply from Richard Rost:

Awesome. Glad to help.
Show Just This Thread        Post Reply
love your classes Comment from Matt Pattison @ 1/17/2013
Richard I love your classes

Reply from Richard Rost:

Thanks, Matt!
Show Just This Thread        Post Reply
Deleting Relationships Comment from Robert Fitzgibbons @ 1/6/2013
I have an access database which keeps track of patients and referring physicians. There are 3 tables, and numerous other objects to manipulate them.  The primary key on the contacts table (referring Physicians) is an auto number that links to the patients table( patient demographic data) and the patient encounter table (each individual appointment) Several months ago,  I made some changes to the database to improve is functionality and renamed the file new office management.    Unbeknownst to me for some strange reason my secretary continued to use the old database for referring physicians adding new contacts and editing old while using the new one for everything else.  . This comes under the heading of not being able to win for loosing. The simple solution would be to delete the contacts table in the new database and then export the old table into the new database.    However when I try to delete the contacts table in the new database, access warns me about the relationships I might be destroying.  My question is, will it work if I simply duplicate the relationships as they currently look in the new database?

Reply from Richard Rost:

You can go into the GLOBAL RELATIONSHIPS, delete the relationships, import the new table (replace the old one if needed) and then re-create the relationships. This will not cause any loss of data. It will work provided that all of your IDs match up. You probably have a CASCADE DELETE in place that's causing the warning.
Show Just This Thread        Post Reply
FORMAT option Comment from vannak @ 12/31/2012
Thanks for the reply.  Didn't expect you would reply this soon. Sorry to bother you on the late night e-mail. lols.  I'll find out later where the FORMAT option is located and I'll let you know.  Thanks again Richard.

Reply from Richard Rost:

It's no problem.
Show Just This Thread        Post Reply
Microsoft Access 204 Comment from vannak @ 12/31/2012
Hey Richard,

Once again,

I'll let you know because that computer or machine is a once in a while I do get my hands on it.   But I will definitely save your suggestion on my e-mail and when I get my hands on it, I'll do it.  As a suggestion for others as well, you might want to post this suggestion that you wrote and post on the blog somewhere. Unfortunately, I haven't used any of the blog on this forum yet.  Once again thanks, Richard!
Show Just This Thread        Post Reply
comdlg32 Comment from vannak @ 12/31/2012
Yes, I did download a few of the comdlg32.ocx off the sites (Microsoft and a few others, still no luck).  But, i will try with your suggestion and let you know.
Show Just This Thread        Post Reply
different name Comment from vannak @ 12/31/2012
Hmmm ... I thought I had given a different name file as well but I'll do it again.
Show Just This Thread        Post Reply
Format menu item Comment from Vannak Hou @ 12/31/2012
Using Access 2007.  Where is the Format option in the design menu.  Time Index: 04:48-05:35.  I do not see the "Format" option on under the menu.  I'm using a computer at work that has Access 2007.  At home, I have Access 2010. Most of the time I am accessing the site from my work place computer.

Reply from Richard Rost:

I have to admin that I don't still have a copy of Access 2007 installed on any of my machines, but in both 2010 and 2013, when you select objects on a report in design view, you get a REPORT DESIGN TOOLS section of the ribbon. All of the commands I mention in the video are available on those tabs. You can also right-click on the report objects.
Show Just This Thread        Post Reply
Ambiguous name detected Comment from Vannak Hou @ 12/31/2012
Index Time:  04:50-05:08. "The expression On Click you entered as the event property setting produced the following error:  Ambiguous name detected: PrintLetterButton_Click."  I even changed/modified to a different and still get the same error message." Any suggestion?

Reply from Richard Rost:

You've got the same name used twice in your database.
Show Just This Thread        Post Reply
comdlg32 Comment from Vannak Hou @ 12/31/2012

I'm getting an error message on Windows 7 Proffessional 32 bit.  It has been working okay but the past few days, when I tried to run any of the downloaded file (AC2* series) it's giving this error message "Your MS Office Access (2007) database or project contains a missing or broken reference to the file 'comdlg32.ocx' version 1.2."  I googled and so far no luck.  Any suggestion. The file opens but when I click on any of the menu buttons from the PCResale.NET Main Menu or any Menu button from within the Form file does not work (meaning the button does not open" or do anything at all.  Any suggestion?  It was working at the beginning for the past 2 weeks.

Reply from Richard Rost:

You may have a different version of COMDLG32.OCX on your system. You could try to download an updated version, but here's an easier fix... instead of messing around with files, just create a blank new database in 2007 and then IMPORT all of the objects from my download databases (tables, queries, etc.). That should then use YOUR comdlg32.ocx file. Let me know if this works.
Show Just This Thread        Post Reply
Macro error Comment from vannak @ 12/30/2012
I'm getting this error message while trying to save the Macro(there is no time index listed); "An expression you entered is the wrong data type for one of the arguments... blah blah .."  I'm using Access 2010.  I then clicked on the OK button and came up to the screen "Macro Single Step."  Am I doing anything wrong?  Thanks.

Reply from Richard Rost:

I need more context. What exactly are you doing?
Show Just This Thread        Post Reply
CanShrink Comment from Finola B @ 11/12/2012
Thanks Richard, I did that but it doesn't get rid of the entire field, I still have an empty line in the middle of an address.  Access is able to do it on its Labels Wizard, ie move lines up so that there are no empty gaps, but I don't know how to do it on my generic letter.

Also, you had mentioned about being able to leave the 'Country' field blank if the country was the U.S.  You said you covered it in a previous class but I've not come across it and it would be useful to be able to do.  Could you let me know the critera for leaving a particular country blank in a query?  Thanks

Reply from Richard Rost:

I would have to see your report to tell you exactly what the problem is, but that's all you SHOULD have to do.

As far as leaving a country blank... just don't type anything into the field. There isn't any special trick. Then, if it's on a line by itself in your report, and its CanShrink property is set to yes, it should effectively disappear.
Show Just This Thread        Post Reply
CanShrink Comment from Finola Bromley @ 11/12/2012
Hi Richard I have hopefully quick questions.  First one is that I seem to have missed how to leave 'Country' blank if it is the US.  Could you let me know how I do that please?  The second, more irriating problem is that I can't get rid of empty fields in addresses.  For example when I'm sending a generic letter to someone in a company, I try to include their job title in the address.  But I don't always have a job title for an individual and so where that field is empty, I want Access to ignore it.  I've tried changing the CanGrow CanShrink options to Yes in the field properties and in teh Detail properties, but nothing seems to change and the empty fields are still in the address.  Is there a way around this please?  Kind regards

Reply from Richard Rost:

You have to make sure that you set the CanShrink property for the CONTROL and the SECTION the control is in to YES. You also have to make sure that there aren't any other controls on the same horizontal line with them in your report. For example, if you place a label or a vertical line that is next to them, that will also keep the report from shrinking.
Show Just This Thread        Post Reply
Help Comment from Alyson @ 3/22/2012
Ok, to say my problem correctly (lol) I built my query and then a form from the query. When I change from design mode to form view, it appears as a blank page, but when I go back to design view of the form, everything is there. I can't move on from this point due to this problem. I hope it makes more sense this time :) Please help!
Show Just This Thread        Post Reply
Comment from  Alyson @ 3/21/2012
Sorry for so many questions...I built my query and report like you when I go to the query, it won't show my looks like a blank page, but when I go to design view, it's all there...what is going on?
Show Just This Thread        Post Reply
Comment from  Alyson @ 3/21/2012
Hi. I'm using Access 2010 and when I click to create a report, it pulls up tables automatically...not queries...the only way I've found to use a query to feed a report is if I click the query and use the report wizard. Is there a way around this?
Show Just This Thread        Post Reply
Comment from  Frank @ 8/18/2011
Access 204 video 1

What is the reason for adding the default value in the customerID combo box?
Show Just This Thread        Post Reply
Report Ignores Query Sort Comment from Robert @ 2/23/2011
I have written a query which sorts my database according to appointment time.  But the report I have developed based on this query sorts differently which is very annoying.  I can’t seem to tell the report how do sort properly.  Any  advice?

Reply from Richard Rost:

A report will override (or sometimes ignore) your query's sort. You have to use a SORTING AND GROUPING LEVEL in your report to force the sort you want. That's covered in Access 104.
Show Just This Thread        Post Reply
Comment from Robert Fitzgibbons @ 2/8/2011
I cannot open  I get the "page cannot be found error meaasge".  Any suggestions?

Reply from Richard Rost:

Robert, is that a link in the video? If so, and you're looking for the student databases, they've been moved here.
Show Just This Thread        Post Reply
Comment from  Rick @ 8/13/2010
Hi Richard

I have been doing the Access course with you at 599cd which is helpful. I have a question.

I have a table that I have imported from an excell sheet. Only 2 fields. (Stock code and Suppliers part number.
The data came in well but I have duplicates which I want to sort out. (there are a lot of records in this table.) and generally only one or two duplicates for each entry, if any,
Some are a 100% duplicate (eg stock code and Suppliers pat number) and others have the same stock code but no suppliers part number. I want to keep the record with the most amount of information, Eg both stockcode and suppliers part number. Eventually I want to make a new table out of the sort.
I was thinking of using a query or a macro but not sure how to go about it. Can you point me in the best way to do it? Thanks Richard.
Show Just This Thread        Post Reply
Comment from Greg Paradise @ 2/18/2009
I'm using 2000 version and the Refresh from the run command will not run, I get an error that says this version does not support the function.  Do I need to have my cd in?
Show Just This Thread        Post Reply
Bold Comment from Richard Rost @ 11/23/2008
Elsa, nope. You can't change the format of PART of a textbox string.
Show Just This Thread        Post Reply
Bold Comment from Elsa @ 11/18/2008
Hi Richard,
Does MS Access 2003 and earlier have the functionality to bold a portion of a concatenated string in a report textbox?
Show Just This Thread        Post Reply
Thank you Comment from Elsa @ 10/28/2008
Hi Richard,  
Thank you for your help.  I am currently at 223 but will keep going.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 10/27/2008
Use the FORMAT function. Here are some examples:

="Your balance is " & Format(Balance,"##,###.00")
="Your birthday is " & Format(DOB, "mm/dd/yy")
="You owe " & Format(Balance, Currency)

I cover this in Access 311
Show Just This Thread        Post Reply
Comment from  Elsa @ 10/27/2008
Hi Richard,  How do I format only a few words within a concatenation in a report textbox?  Example: "Your balance is:" & "[Balance]"  I want [Balance] to be in bold and formatted to ##,###.00 while the rest of the concatenation stays the same.
Show Just This Thread        Post Reply
Comment from Richard Rost @ 9/8/2008
Margaret, that all depends on what you're doing. I need more information to go on. What type of FIELD are you working with? Number? If so, did you format it as an INTEGER or LONG INTEGER? If so, that's your problem.
Show Just This Thread        Post Reply
Comment from Margaret  Cattarin @ 9/3/2008
I have tried everything that I can think of to stop decimals from rounding out to whole integers.  Is there hidden code somewhere?
Show Just This Thread        Post Reply
Comment from Richard Rost @ 4/16/2008
Sasson, you can find a searchable index of all of my Access classes at
Show Just This Thread        Post Reply
Comment from sasson cohen @ 4/13/2008
do we have an index/list  for reviewing each theme on each class ( maybe to support our design )?
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


Sorry about this step. It's just to keep the spam bots away:
  Verify: What is 9+9:
  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

Visual Basicindex
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search Our Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
About Us
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Gift CDs
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