ERROR #3704: Operation is not allowed when the object is closed. Microsoft Access 104: Contact Management, String Concatenation, Report Headers, Sorting, Grouping, Page Numbers
 

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
 
 
Courses - Microsoft Access 104
Description: Using Microsoft Access 3
Running Time: 70 minutes
Pre-Requisites: Access 103 very strongly recommended
Previous Lesson: Access 103
Next Lesson: Access 201
Main Topics: Contact Management, String Concatenation, Report Headers, Sorting, Grouping, Page Numbers
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 104
Using Microsoft Access

Creating a contact history table, string concatenation, contact history report, more form and report tricks. 70 Minutes.
 

AC104 Major Topics

  • Contact Management System
  • String Concatenation
  • Report Headers
  • Sorting & Grouping
  • Page Numbers

In this course we're going to begin building a Contact Management System. A ďcontactĒ is every instance of communication with a customer Ė via phone, in person, email, etc. We'll begin by building a table to store all of the contacts with our customers.

 

Next, you'll build a contact query, so that you can get - for example - a list of all contacts between two dates - of course the user can type these dates in when the query runs using parameters.

 

We'll learn about string concatenation - or taking two text fields (like First Name and Last Name) and smashing them together into one Full Name field.

 

Next we will make a contact report - showing all of the contacts for each of our customers. We'll learn about the Can Grow and Can Shrink properties in reports - which will allow certain fields (like our notes fields) to grow to fit the data in them.

 

Then we'll learn about Sorting and Grouping levels in reports - so that we can turn group headers and footers on and display all of a particular customer's contacts together in the same group.

 

Finally (with reports) you'll learn how to use the Page and Report Headers and Footers to put a classy title at the top of your report, and Page Numbers at the bottom of each page.

 

We'll go back to our Main Menu and add buttons for our new contact management features. We'll also create Tooltips on them (you know - those little yellow text boxes that pop up to tell you what a certain button does when you hover over it).

 

If you're interested in building your database into a professional-quality contact manager, this is the course for you. Say goodbye to your old Rolodex or Act! software. We're going to make Access do everything for you! By the end of the next few classes, you'll have something very powerful to use... and you can customize it to do exactly what you want it to!


 

 

Access 104 Outline
 
l. Introduction
Welcome
Objectives
Pre-Requisites
Versions
Sample Database Files

2. Contact Table
What is a "Contact"
Using the customer notes field for contacts
Limitations
Creating the Contact
Duplicate data problem
Setting the default date with the Now() function

3. Contact Query
Listing contacts by date
Show only a specific date between two dates
Between keyword
Let the user enter the dates with a parameter
Creating Full Name field by merging two fields
String concatenation
Formatting a query field
Format property

4. Contact Report
Using the page header
Selecting objects using the ruler bar
Notice an & disappears in label
&& in Label
Adding a line object
Keeping your line straight
Text boxes with large amounts of data are truncated
Can Grow / Can Shrink textbox properties
Can Grow / Can Shrink section properties
Sorting and Grouping
Sorting in the report
Group Header & Footer
Keeping a group together on a page
Report header / footer
Adding page numbers to your reports
Showing total number of pages

5. Miscellaneous
Put contact report on Main Menu
Control Tip Text

6. Review
Review topics
Skills check
What's next?
 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 104

Richard on 1/1/2007:  Creating a contact history table, string concatenation, contact history report, more form and report tricks.
John Brandrick on 10/20/2007: Hi Richard,
In section 03 you show the perameters in the date/time field >=[StartDate] And<[EndDate] will this only work in the D/T field? I've tried using it with week numbers in a text field so we can search between>=[Week No And <[Week No]but it only asks for the first parameter then immediately opens the query I've also tried entering the Between [WK No]And[WK No]that does the same thing as well. what am I doing wrong
Thanks
JohnB

Richard Rost on 10/20/2007: The problem, John, is that you're using the same parameter value twice. If you ask for [WeekNo] and then [WeekNo] again in the same query, Access will only prompt you for it once. Try [WeekNoBegin] and [WeekNoEnd] and that should work. In later classes, you'll see how you can do this with a form and text fields to supply your parameter values.
Francisco Ponze on 11/8/2008: Richard, The Control Tip text does not work. Is any advice that you can give.

Thank you

Richard Rost on 11/10/2008: Francisco, what do you mean when you say it does not work? Are you putting in a Control Tip and not seeing the popup text? What version of Access are you using? It *should* work.
Francisco Ponze on 11/10/2008: I am using Access 2003, I fallowing you class and set the control tip text to show the text with the mouse on the button in Access 104. I also tested with the DB from the download and still does not show the text. First I went a select repair Access, but still witht the problem. That is why I send you an email perhaps another students have similar problem and have the solution.

I will try re-istall Access later and see that will resolve the problem.

Richard Rost on 11/10/2008: Francisco, I tested this both with Access 2000 and 2003, and I've never had a problem. You're the first student (out of thousands) who has reported this problem. I want to help you figure it out, but I can't reproduce the problem on my end, so it's probably something on your PC.
Francisco Ponze on 11/10/2008: Richard:
I tested in onther PC and it works, I re-install Office 2003 and still with the problem, It seem that is my PC it just 8 month old from DELL and using windows xp pro sp3. Any way I will setup another PC to test in case I have any other problems for the rest of the tutorials.
Thank you

 Tina on 12/5/2008: The instr function counts to the left. What if you had yyy@uu@iii how would you get the iii or how to get the instr to look at the 2nd @
Charles Brumagem on 1/12/2009: Richard I want to build a good knowledge tree program for the helpdesk that I run. I'm going to use a lot of ck boxes or radio buttons and on what is selected it will build a memo field of what was done and then I want to some how link it to the tickets system that we use called Mantis.I have read you history that you worked support and built tools. I sure you have some good thoughts on this can you let me know what you think. I'm going to take all your classes and I hope by the end I will be able to build and program more tools.
Keep up the good work!

Fran Raymond on 4/9/2009: Hi Richard. I'm running some parameter queries. I have auditors that go out to our schools and i have dates that the auditors visit. I'm running parameter queries on the auditor names and dates. Everything is fine except that i get the parameter prompts TWICE - EVERY TIME (??). In the Auditor field i have

Like [enter auditor] &"*"

and in the AuditDate field i have

Between[enter start date] And [enter end date]

Is it some sort of glitch in my system that gives me each prompt twice or is there something wrong with my code?

Richard Rost on 4/9/2009: Fran, it sounds like you have the parameter set up twice. Is the query perhaps pulling on another query that also asks for the same parameter?

You might want to try using a FORM FIELD to provide the parameter. It's a little more complex, but it works much better. Here is a tutorial that explains how:

http://599cd.com/tips/access/forms-formname-field-notation

You can literally set up a small form with ONE field in it (the parameter) and then make a command button to launch the query, which gets the parameter from your form. Neat, huh?

Judith Koester on 4/27/2009: I am designating a query of projects before 2008 which works fine, but I want to include some years that are blank.
Richard Rost on 4/28/2009: Judith, you'll have to say:

<2008 OR Is Null

Nathan Wittmann on 8/13/2009: I have an address that has and address line 1 and and address line 2. I currently have them in a group header and have the header set to "can grow/shrink" to yes and the textboxes grow/shrink to yes and it will not shrink if there is not data in the address line 2. It does do it if I move them to detail section, but not if I have them in the group header or any header i put it in. Any Ideas?
Richard Rost on 8/15/2009: That's very odd. Do you have any other controls NEXT TO (left and right side) the text boxes? For example, LABELS cannot grow or shrink, so if you have a matching label next to the address text box, then it will prevent the shrinking.
Nathan Wittmann on 8/17/2009: Actually I had some objects to the right and when I moved them it worked. Unfortunately my report looks odd now with a large gap to the right of my address, but I think it looks better then having a gap in my address. Thanks for your help!
Anthony on 11/2/2010: I am having a problem with formatting in MS Office 2007, in Access 2007.

The issue is; I I have created a simple db and made a form and went to get a report. I go into the design view and attempt to adjust the with of a cell and it is adjusting all the cells in that column. Also I want to move one cell to center it under the column header but it moves everthing.

How can i make adjustments to a cell with out moving or adjusting everything???
Thank you

Anthony

Reply from Richard Rost:

When you create a form using Create > Form, by default the controls are all grouped together. To remove this grouping, right-click on any of the controls and select Layout > Remove Layout. This will give you the freedom to move individual controls around. I don't like this feature either, and that's why I always design my forms using Create > Form Design.

 Emad on 11/9/2010: Time 8:07
1-Why you did not insert the Full Name Field in Table? Why did you inserted it in Query?
2-Which kind of Programming Language did you use in the Field ,Criteria and Or? are they SQL or VBA?

Reply from Richard Rost:

1. Because it's MUCH easier to store complex data in it's component parts and then reassemble them later. It's a whole lot easier to store FirstName and LastName in separate fields and put them together into one FullName field than it is to store them as a FullName in the table and then try to pull them apart later.

2. It's not really a programming language. It's how you create a calculated field in Access. Access then creates the SQL from that. You can see it by clicking on the SQL VIEW button (on the Design View button drop down).

 Terry on 11/16/2010: Richard, these courses are excellent. When i first found your site, I knew basics on access, but i didn't realize how basic untill i started taking your courses. I've bought several so far but have gone back at least 3 times and reviewed each course, and im amazed at how much more i pick up each time. I have since build a "job" table in my computer store and it has helped my business imensley. I'm currently tracking customers thru quickbooks, but the access database fits like a glove for the jobs!

Reply from Richard Rost:

Thanks for the compliments - and I'm glad you're happy with the courses. Yes, once you know how to build your own database, no other "canned" program will do. :)

Lisa Seibert on 9/16/2011: Richard, I am using Access 2007 and have tried the Control Tip Text feature from lesson 104 and it does not display the text when I hover the mouse over the button in Form View. I tried it on the database I created following the lessons as well as the one provided with the lesson...no luck. I double checked that the etx was entered in the property sheet. Is it possible that the default settings is not set to recognize the act of hoover over the button and I have to change the settings for this to work? If so what do I need to change . Thank you.
 Gary on 11/30/2011: Can I use Access Database on the Web and have my sales people across the country access it and look up and add to their Employee data?
Alex Hedley on 11/30/2011: Hi Gary

Have you seen the Web Database Seminar or the ASP courses?
Alex

Gary Donelson on 11/30/2011: No but I will look.
 Chad Johnson on 3/14/2012: Richard, I have a friend that ask me to set him up with a simple product database were the prices in access are linked to the prices shown in a catalog he is creating in Word. I started out in Excel because I figured the copy and paste link was easy and would automatically keep his catalog prices current. However I saw the potential in Access for features (like userfriendly forms etc.)that are not available or differcult in Excel. Now it seems that there is no way to link Access like Excel?

Reply from Richard Rost:

You CAN, but it's a lot more difficult. I would recommend doing the WHOLE catalog in Access using reports. It would be SO much better than linking between Word and Excel/Access just to get the prices. You can put all of the relevant data in Access: product name, description, pricing, etc. Use reports to print out the catalog.

Wayne Loomis on 7/1/2012: Richard
Using the principles on these tutorials I have created a membership database. What I would like to do is print a Index card using a list box.


Reply from Richard Rost:

Well, printing an index card is very similar to printing a mailing label... just use the wizard and pick the right size. What do you mean by "using a list box" though? You want to pick a specific record to print? One index card?

Wayne on 7/2/2012: Yes
I would like to select a member to print using eather list box or combo box. I hope it is possible.

Cheryl Hokanson on 8/27/2012: How do you type in an query description so that you can remember why you made a query or how you are using it?

Reply from Richard Rost:

If you right-click on ANY object in the Navigation Pane, you can click on Object Properties and you'll find a big text box there that you can use to type in a description or notes about that object. I personally never use it myself (objects should have descriptive names) but it's there for you if you need it.

Emad on 1/30/2016: You said if want to make new table, you ask yourself 5 question- Who: (Customer), What (Notes), When (Date/Time), Where () and Why (). Can u plz give me example for Where and Why?
Emad on 1/30/2016: The Default Value accept =Now() and Now(), So does = or without make any difference or it is same? Access 2013

Reply from Alex Hedley:

I think the difference is = denotes expression

STEPHEN on 1/25/2017: Hi Richard
I need to concatenate strings in a form to generate a code like SLA-C-16-ACT-001 C being the region selected,16 being the year(2016) ACT being the short form of ACTIVITY 001 being the ID.i tried using expression builder and gives me SLA-CENTRAL-2016-ACT-1.And it does save it to the table.wat am i doing wrong.Thank you Richard

Reply from Alex Hedley:

Is the Field bound to a Table?

 

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