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  
 
 
 
Courses - Microsoft Access 203
Description: Intermediate Microsoft Access 3
Running Time: 87 minutes
Pre-Requisites: Access 202 very strongly recommended
Previous Lesson: Access 202
Next Lesson: Access 204
Main Topics: Form Controls, ActiveX Objects, Tab Control, Bound OLE Objects
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 10/27/2014 to get a FREE upgrade to our Access 2010 version!
Click here for details

 
Microsoft Access 203
Intermediate Access 3

Option Groups, List Boxes, Toggle Buttons, ActiveX Controls, Tab Control, Bound v. Unbound Object Frames, more. 87 Minutes.
 

AC203 Major Topics

  • More Form Controls
  • ActiveX Objects
  • Tab Controls
  • Bound OLE Objects

This class follows Access 202, which we strongly recommend before taking this class. The main focus for this course is Form Controls. We'll cover all of the form controls we haven't covered in classes to date, including:

  • Option Groups & Option Buttons
  • List Boxes
  • Toggle Buttons
  • The ActiveX Calendar Control
  • Picture OLE Fields
  • Tab Controls
  • Bound & Unbound Object Frames
  • Image Control
  • Line & Rectangle Controls
  • And more.

We'll begin by creating an Extended Customer Table so that we can store additional information about our customers, but not take up space in our main Customer Table. There are several benefits to this technique, which I will explain in class.

 

Next, we'll create an Option Group to track each customer's Gender. We'll use Option Buttons for this.

 

We will then create a Customer Type List Box to track what type of a customer we're dealing with. This list of types will be user-defined and we'll create a separate table to track this information - so that you can add, edit, and delete the options.

 

Next, we'll play with the settings for our Option Group - colors and properties and such.

We'll then learn all about Toggle Buttons. We'll create a column of toggle buttons to indicate which services and products each customer buys from us. I'll also teach you a trick for assigning keyboard hot keys to these toggle butons.

 

In the next lesson, we'll do some more tricks with Combo Boxes. We've done a lot with Combo Boxes in previous classes, but there are a few more examples we need to cover. We'll add a Sales Rep combo box to our Customer Form, and a Service Tech combo box to our Extended Customer form. Both of these will be fed from our Employee table that we built in an earlier class. Why do it this way? You'll see in class.

 

Then we'll learn something really cool... how to place a special object called the ActiveX Calendar Control in our form. This will allow you to pick a date from a calendar and store that date in your table.

 

We'll make a Command Button next to open up the Extended Customer Form right from our Customer Form, so that we can see just the data for the currently open customer. No big deal - we've done this before when we were making our Contacts form, but this is a good review PLUS I'll show you how you can force this to be a one-to-one relationship and only have one Extended Customer data record for each customer record.

 

I'll show you how to use Hyperlink fields in your tables and forms so that you can type in a web page, click on it, and your web browser is automatically launched and you're taken right to that page.

 

Here comes a fun lesson... We'll learn how to create Tab Controls. You've seen them before (like if you click on Tools > Options in any Microsoft Office program like Word, Excel, or Access). Well now you'll learn how to put Tabs in your own Access Forms to save space and logically group related information.

 

Next we'll study Bound and Unbound Object Frames. We've seen before how to put a picture in an OLE field. We did that with our employee table and form. Now, I'll show you a few more tricks with Bound Object Frames to store pictures in your database. I'll also teach you how to Link to images on your hard drive from your database so you can still have the image available, but not waste space in your tables.

 

I'll also show you how you can use Unbound Object Frames to either store information directly in your forms, OR link to external data files such as Word documents or Excel spreadsheets so that your database users have quick access to commonly used files.

 

Finally, we'll round the course out by showing you how to add Images to your forms using the Image Control. We'll also cover the Line and Rectangle controls, Page Break Control, and more.

 

This is a great class if you'd like to really take your Access Forms to the next level. You'll learn how to use all of the controls in the Toolbox, and I'll teach you a lot of tips and tricks to teach you which tools to use and when.

 

 

Access 203 Outline
 
0. Introduction

1. Creating the Extended Customer Table
Building the table
Creating fields
Hyperlink field for Web Site

2. Using Option Groups
Option Groups
Option Buttons
List Box
Creating a Customer Type Table

3. Toggle Buttons
Toggle Buttons
Making a Check Box > Change To Toggle Button
Creating a Toggle Button manually
Changing Toggle Button Properties
Changing Option Group Colors
&Caption trick

4. ActiveX Calendar
Combo Box for Sales Rep
Combo Box for Service Tech
Picture OLE Field
Birthday ActiveX Control

5. Tab Control
Linking CustomerF to CustExtF
Tab Control

6. Object Controls
Bound Object
Pasting v. Linking Bound Objects
Unbound Object
Image Control
Boxes & Lines
Page Breaks
Force a 1-to-1 Relationship

 


Try a FREE Demo Lesson

 
 
 

Student Interaction: Microsoft Access 203

Richard on 1/1/2007:  Option Groups, List Boxes, Toggle Buttons, ActiveX Controls, Tab Control, Bound v. Unbound Object Frames, more. 87 Minutes.
Judith Koester on 2/11/2008: My toggle buttons look flat AND only one "toggles" at a time so I can't select more than one. Tried the special effect button. What am I doing wrong?
Richard Rost on 2/28/2008: I'm not sure about the flatness problem. I can't even change the border style of my toggle buttons. Are you SURE you're using a TOGGLE BUTTON? Now as far as being able to select more than one goes, the toggle button is an ON/OFF control. Unless you are using them in an OPTION GROUP you should be able to turn each one on and off.
Walter Pohle on 3/27/2009: I have to keep information
on clients for food program
and the form we have need to Kids Name,DOB,M/F Could this Exteneded Tab work or is there a better way
Time Index14:17

Richard Rost on 4/1/2009: Walter, the use of a second table (the Extended Customer Info, for example) is great when you have a little bit of information on ALL of your customers, and a LOT of information on only a FEW of your customers and you don't want to waste space in your primary table.

For example, let's say you have name, address, and phone number on ALL or most of your customers, but you only have buying habits and demographic data on about 10% of them. This is where an extended table and a one-to-one relationship comes in perfect.

 Tonya on 4/3/2009: Because you're using a extended table in this lesson, is it possible to set the CustomerID textbox to show the customer's name rather than the ID when in view mode? I tried change the textbox to a combo box, but when I do, my control source won't allow me to select any field except the ones within my extended table. I'm not sure what to do. bottom line is that I don't have a Nickname field to help me know which record I'm changing.
Richard Rost on 4/6/2009: You could merge the two customer tables together in a query, and then use that query as the recordsource for your combo box. That would allow you to see the names and have the IDs as the bound field.
Daryl Armstrong on 5/11/2009: When I right click on the picture box I do not get the insert picture option.
Richard Rost on 5/18/2009: Daryl, what version of Access are you using, and what options DO you have available?
TBone on 6/30/2009: Can you have multiple Tabs in the view and have them all on the same window without scrolling right and left to view them? In other words, maybe "Stair Step"
or "Cascade" them.

Richard Rost on 7/1/2009: TBone, I don't believe this is possible, but I don't really use TABS much - I hate them. I have never seen any other configurations for tabs aside from the default, basic design. I know there are other 3rd-party ActiveX controls you can obtain (buy, download, etc.) that give you more flexibility.
 Dave Sterner on 9/16/2009: Hi Richard: I asked you a Access 102 question regarding a bounded object frame and using a link instead of inserting the actual AdobePDF map. I reviewed Section6 of Access 203 created a link just like your bitmap example in Section 6. When it adds a picture of the link I find that my file size only drops from about 7 MB to 6.8 MB - not a significant difference between link and unlinked. So then I saw that you can substitute an icon and for a picture and that drops the file size dramatically. All well and good except now my question is when I try to print a report (inserting an OLE bounded box) all I get is the icon to print not the actual image - I cruised around in Access but couldn't find an easy solution.
Thanks

Richard Rost on 9/17/2009: Dave... try going back to inserting the link, but then COMPACT your database before checking the file size. Remember, if you add a 10 MB image, your database goes up 10 MB. If you delete that image and replace it with a 2 MB image, your database might only go down to 9 MB because Access reserved a ton of space for that big image. Try compacting (compact & repair) and see if that does the trick. If not, ask me your second question again. :)
Dave Sterner on 9/17/2009: No go Richard, I inserted the link and then compacted, still only seems to subtract about 250kb from the original Adobe PDF file. In fact, I hadn't noticed this before, but the weird thing is that my original pdf file is only 256kb and my mdb file is only 356 kb. But when I either insert the pdf or link it to Access the whole mdb file goes up to ~7MB!! I add another 200 kb pdf and the mdb goes up to 12 MB!
joe  mccann on 12/6/2009: i see the speed of the lessons is speeding up
and getting harder
(great just what i wanted to see)
i learning more every day

James on 12/12/2009: Richard To Tbone's question, Yes. On the properties tab for the Tabs Control there is a property for multi row. I have a 320 Item button menu ordering screen that was built like this. Caution...754 controls are the max on a form.
Richard Rost on 12/14/2009: James, thanks for that answer. Again, I personally don't like tabs, so I hardly ever use them. You'll notice that I covered them only briefly in class. I'll probably spend more time with them in Access 2007 because it looks like they fixed a lot of the bugs and other things that annoyed me with them.
Stephanie Perkins on 9/13/2010: I have not been able to copy and paste a picture into the OLE Object field on the EmployeeF. I can drag from my desktop or "insert object" but it only gives me a thumbnail of the picture with the file name below it, not the picture itself. Do you think this a problem with my OS (Windows 2000) or an Access installation error?
Judith Koester on 6/8/2011: My graphic won't show up. I get the name of the file *****.jpg (Command Line). Access 2007 is driving me crazy. No problems with 2003.
Alex Hedley on 6/9/2011: Hi Judith,
What control are you using to show the image?
Are you adding the source through code/field/property?

Judith Koester on 6/9/2011: I am r clicking on object frame, checking link and browsing to fine the pic. It just inserts a link to the pic and not the pic itself. Followed Richard's instructions in Object Frame lesson.
Alex Hedley on 6/9/2011: Hi Judith
Have you tried opening the picture in Paint, Click Select All (Ctrl+A) then Copy (Ctrl+C) then move back to Access and Paste (Ctrl+P) into the form and this will paste an OLEUnbound object into your form, showing the picture you want.

If memory serves maybe from the Imaging Seminar (Well worth getting if you use a lot of images) it is Access that has a problem showing JPGs in the OLE, if you try a Bitmap (BMP) the full image will show. It won't display all images types and just shows an icon instead.

Al


 Elaine on 7/7/2011: I had problem inserting an image, I copied and pate, I drag and dropped, I inserted as a link and all I got was the jpeg icon, I am using Access 2003 I also have 2007 on my computer so when I open Access 2003 it shows some of the 2007 Interface
RIck Felton on 9/30/2011: Hi Richard Just downloaded your viewer. Question, How do you make the viewer larger? I have tried to find the extents button or maximuse the view but it stays at the one size. Why? Please advise solution Regards Rick

Reply from Richard Rost:

Click on the EXTERNAL button. That will load the videos in Windows Media Player. You can then resize the window or full-screen it.

Wilma on 10/3/2011: You said you don't perfer using tabs? What is the alternative? I am building a school database and was planning to use a separate tab form for each of the following: addresses, emails, allowed to pickup, emergency, medical (doctors, etc), student (grades, teachers, etc.), immunizations, custody, volunteer group, etc. This is a many to many situation. One individual could be a parent to one kid, a stepparent to another, on the emergency form, but not on the pickup, etc., etc.,

Reply from Richard Rost:

I don't like using tabs because they become problematic later on when you get into programming. Actually, however, I made that statement way back when I first recorded this class (using Access 2002 or 2003). Since then, in Access 2010, a lot of the quirks and problems associated with tabs have gotten much better. STILL, I prefer using multiple forms. That's just a personal preference. If you want to use tabs, and you're using a newer version of Access, go right ahead.

BONNIE STAIB on 4/30/2012: Richard, I am working in your class 203, using Access2007. In Lesson 2 @4:00 you drop-down a list of fields to choose your option group. You do the same in Lesson 3 @2:56. In both cases your full field list drops down; in both cases on my database the list is incomplete and only has those fields which I have already moved onto the form. In Lesson 3 @3:25 I also have an incomplete list for Control Source Options – only up to Hardware, because we have worked with the previous fields. So I cannot do what you do in copying & pasting. Is that normal in Access2007? Is there a work around? Thank you! Bonnie
Tamlyn L on 9/26/2012: In the list demonstration in Access 203-3, you had us add ampersands to the captions to make hot keys. In Access 2010 this does not seem to work. No letter is underlined.

Reply from Richard Rost:

It works just fine for me in 2010. I use it all the time.

Sandra on 9/27/2012: Hi, I am using Access 2007, but have been following the 203 lesson. I can't get the default value to when I want the btton on the coustomerF to open the ExtendedF. I only get a blanc form. What am I doing wrong?

Reply from Richard Rost:

Is there data in the Extended table?

sandra on 9/27/2012: Yes, there is data in the extended table

Reply from Richard Rost:

You're using the command button wizard to open a form and find specific data, correct? Did you make sure to set the relationship between the two forms? Did you make sure you have the CustomerID field on both forms?

sandra on 9/27/2012: I have made a costumerF from CustomerT. Then an ExtendedCostumerF from ExtendedCustomerT. I have the CostomerID in both forms, as an autonumber in CustomerT and as a number in ExtendedT. In ExtendedF I put in a combobox with the CostumerName from the CustomerT and linked the CustomerID from both tables. Then I changed the Default Value of the Combox with the name you gave. But when I open the customerF og click on ExtendedBoutton it only opens an empy form, unless if there is a form already filled in for the Customer. Then he opens this customerF. But he does not fill in the CustomerName as a default as you show on the video....
sandra on 9/27/2012: I've fixed it. I had written Form! insread of Forms! with a s..... Sorry ;o)

Reply from Richard Rost:

Problems like this are almost always spelling errors or typos. Glad you figured it out. :)

sandra on 9/28/2012: A last question: I use Access 2007. When I open the Combobox wizard only shows two alternatives. The last alternative "Find a record on my form based on the value I selected in my Combobow" is not there. Does that has to do with the 2007 version? How do I find this alternative?

Reply from Richard Rost:

Yes... this is actually something I'm addresses RIGHT NOW in my new Access Search Seminar. In order for that 3rd option to show up, your form has to be based on a TABLE or QUERY - not an SQL statement, which is what the new Access "Quick Form Builder" uses. Open up your form's properties and change the SQL statement in the Control Source to an actual TABLE name. Then remake your combo box.

Vannak Hou on 12/9/2012: So far I am in Access 203 video. What I have seen so far is that you only created just one relationship table right? I haven't seen more of the table relationships yet. What I have found out is that it is very important that the Forms are correctly designed right or else it's not going to work with all the other tables that are related to the fields. Right? I'm sure there are more than that but basically, I have noticed the importance of designed the form and its related field links from each of the different tables.

Reply from Richard Rost:

It's been MANY, MANY YEARS since I recorded these classes, but I believe at this point we've only set up ONE relationship between the Customers and Contacts. Yes. There will be more coming in future lessons.

Forms are pretty easy to change. The hard part is making sure your TABLES are set up properly from the start. This isn't always easy, especially for beginner users, because as you learn more you learn BETTER ways of doing things... but I can't just jump in and show you the BEST way first because it's pretty confusing. So I like to show you the evolution of how databases were built, moving from simple flat-file, one-table setups to more complex relationships as time goes on.

That's why I like to tell people not to start building any serious database projects until you've FINISHED my entire Access series. Many times people get through, say, the first couple 200-level classes and then start their projects... and then by the time they get to the 300s, they're like "DOH! I should have waited."

Vannak Hou on 12/19/2012: Time Index: 04:10 -04:15; It's better to create a query based on the (title/position) inside the CustomerT for the salesrep/techid; that way, a combo will have one for each type of field. But anyway, I do get your point.
Vannak Hou on 12/22/2012: Time Index: 02:27-02:33. I do not have Customer8 on there. Even the CustomerT is selected in the RecordSource of the Properties Sheet form. It's the original data file of Access 2003 of Access 202 file but Access 203. I'm trying to work from 202 to 203. I see several customers in the CustomerT table somehow it is not showing me up in the CustomerF. Why is that?

Reply from Richard Rost:

I don't understand the question. Can you elaborate please?

Vannak Hou on 12/22/2012: Time Index: 02:05-02:22. My customerF form on has a record 1 of 1 NOT 1 of 8. So I can't go any further than customerid #5. Am I do something wrong. I used the Access 202 data file instead of 203. I followed the instruction on the screen and I have tried more than 5 times to make sure that I have not done anything by reviewing the video; so far there isn't anything that I didn't do it right. The "Control Source" is CustomerID in the CustomerF form in the Properties form. What I am I doing wrong? I did not want to use the AC203 file instead I used the AC202 data file.

Reply from Richard Rost:

So you're saying the customer records are missing from the 202 data file? Just copy them in and paste them from 203.

vannak on 12/23/2012: Richard, I have been watching the 202 video over and over again and I'm trying to do it again; hopefully I'll get it. I'll let you know more. The problem I have is that in the MainMenuF, CustomerF, when I click on the Next Record button, I do NOT see any other record with an exception of Record #5. There is ONLY 1 record in there; just customer #5. I check the CustomerT and I have more than 8 records in it. I'll let you know more as stated above to see what I am doing wrong. I'm trying to figure out what I am doing wrong. I haven't found the answer but I'm willing to discover the problem myself until I exhausted. I used your original Ac202 file. Thanks again.

Reply from Richard Rost:

It almost sounds like you're selecting the "open the form and display a specific record" option. Recreate the button from your main menu that opens the customer for, and make sure you're showing ALL records.

Vannak on 12/23/2012: Thanks for the suggestion Richard. I'll let you know more. Last night, I kept on trying for over 2.5 hours and I finally gave up and I copied the ContactF from from your 202 file and paste it on there and it works. I am going to try that suggestion.
Vannak Hou on 12/28/2012: Time Index: 02:00-03:00. is there a way in Access 2010 configuration that I can configure to have the picture(s) link to a new directory/location? For example, lets say my database file has about 50 photos in the OLE and I copied the database file to another computer since those photos were "linked" to a directory/location; how do I configure the file onto a new computer once I transferred all the photos to the new computer when the file is transferred? Or what is the best route to transfer the file along with the Access file to a new computer?

Reply from Richard Rost:

Well, as I've mentioned before, I don't like storing images in OLE objects (even linked). The best method is to store the PATH and filename of the image and keep them on a server on your network. You can then store the UNC path like \\SERVER\Images\MyPic.JPG and not have to worry about it. Use a picture control to display them using this technique.

However, if you're storing your images with file paths like C:\Documents\Images\Pic.JPG then you just have to make sure you reconstruct the same path on the new computer.

Is there a way to dynamically update the links with VBA? I'm sure there is. I've never done it before, but I'm sure it's possible.

Arul Tandavan on 1/30/2014: Hi Richard, Can you help me with a problem using a Combo Box? Here goes. This is an inventory application with an Item table containing a SizeID and a Size look up table containing the SizeID and SizeDesc. I'm using the Combo Box to select Items with the SizeDesc to add to a third table, PickPack. The Item to PickPack table update works just fine but the Size table is also being updated with another entry for SizeDesc. That is, 'large' is now listed in the Size table twice. How can I prevent the Size table, a reference table, from being updated in this instance? Many thanks. Arul
 

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