Introduction

Welcome! Customer Forms & 1:1 Relationships

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Expert Level 4. In this course we will focus on additional form controls in Access, including option groups, toggle buttons, the image control, and more. We will set up an extended customer information table and establish a one-to-one relationship with the main customer table, as well as explore one-to-many relationships by building a family members subform. We will discuss creating subforms, combo boxes, helper forms, and review the progression from previous Expert levels. We will also talk about version differences, available seminars, and tips for using the course effectively and participating in the student forums.

Navigation

Prev: X04
Next: Lesson 01

Keywords

Access Expert, option groups, toggle buttons, image control, one-to-one relationship, subforms, combo box, helper forms, extended customer table, family members form, tab controls, display image on form, toolbox controls, list box, SQL primer, visual basi

Subscribe to Introduction
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Expert Level 4, brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.

Today's class is going to focus primarily on learning additional form controls. We are going to learn about option groups, toggle buttons, the image control, and a lot more. We are going to set up an extended customer information table, with information you might not want in your primary customer table, because it is either sensitive or you simply do not have it for everyone. We are going to learn how to establish a one-to-one relationship, where one record in the customer table can have one and only one record in the extended customer table.

Then we are going to spend some more time working with one-to-many relationships. One-to-many relationships are the most popular relationship type in Microsoft Access, and so I feel it is important to give you several examples of how to work with one-to-many relationships. In today's class, we are going to set up a family members per customer form, where you can put in as many family members as you want, their date of birth, their name, and specify whether it is the mother, father, and the mother.

We are going to build several more subforms, several combo boxes, some helper forms, and lots more. This class follows Microsoft Access 2010 Expert Level 3.

This is the first class that I have made to jump to Access 2013, which you should find very similar to 2010 and 2007. Rather than re-recording all of the previous classes, I decided to move straight into 2013, because the interfaces really are pretty much the same. There are a couple minor differences, and I will mention them today in the first lesson.

Expert Level 3 covers an SQL primer. You will learn a little bit about structured query language. You will see a little bit more of that today. We have built a callback form, so we have a list of customers that we have to call back, a contact subform, we will work more with a subform today, and we learned how to calculate form footer totals.

If any of these concepts seem unfamiliar to you, check out Access Expert Level 3. And of course, Access Expert Levels 1 and 2 cover all the basics of relating tables together. So if you have never worked with multiple tables at the same time, make sure you start with Access Expert Level 1.

Again, I am using Microsoft Access 2013. If you are using Access 2010 or 2007, you should not have any problems following along, as all three versions are pretty similar. If you are using Access 2003 or earlier, you should go back to my website and look for my Access 203 class under my Access 2003 lessons. It is roughly the same as today's class.

You are strongly encouraged to build the database yourself that I build in class, but if you are like me and you are lazy and you just want to download it, go to my website at accesslearningzone.com/databases, and you can find all of the sample databases from this and the previous classes online.

My courses are broken up into four groups: Beginner, Expert, Advanced, and Developer. The beginner lessons are designed to give you a basic overview of the simple features of Access. The Expert Series, the one you are in now, is designed for more experienced users who are already comfortable with Access. The Advanced Lessons cover working with macros, automation, and many more advanced features. And the Developer Lessons get into programming with Visual Basic for Microsoft Access.

Each of the series are broken down into different numbered levels, starting with level 1. The beginner series, for example, had levels 1 through 9. This is level 4 of the Expert Series. The beginner series had levels 1 through 9, and there will probably be about 10 levels of the Expert Series before we get to the Advanced courses. Of course, do not hold me to it, sometimes I add more stuff.

In addition to my normal Access classes, I also have seminars designed to teach specific topics. Some of my seminars include building web-based databases, creating forms and reports that look like calendars, securing your database, working with images and attachments, writing work orders, tracking accounts payable, learning the SQL programming language, and lots more.

You can find complete details on all these seminars and more on my website at accesslearningzone.com.

If you have questions about the topics covered in today's lessons, please feel free to post them in my student forums. If you are watching this course using my custom video player software or the online theater on my website, you should see the student forum for each lesson appear in a small window next to the class videos, as long as you have an active internet connection. Here, you will see all of the questions that other students have asked as well as my responses to them and comments that other students may have made.

I encourage you to read through these questions and answers as you start each lesson, and feel free to post your own questions and comments as well. If you are not watching the lessons online, you can still visit the student forums later by visiting accesslearningzone.com/forums.

To get the most out of this course, I recommend that you sit back, relax, and watch each lesson completely through once without trying to do anything on your computer. Then, replay the lesson from the beginning and follow along with my examples. Actually, create the same database that I make in the video, step by step.

Do not try to apply what you are learning right now to other projects until you master the sample database from this class. If you get stuck or do not understand something, watch the video again from the beginning or tell me what is wrong in the student forum. Most importantly, keep an open mind. Access might seem intimidating at first, but once you get the hang of it, you will see that it is real easy to use.

Now, let us take a closer look at exactly what is covered in today's class.

In lesson 1, we are going to build some tables to store additional customer information and some related data that we do not want stored in our customer table. We will also see how to force a one-to-one relationship.

In lesson 2, we are going to build the extended customer form. We are going to build an option group, a list box, and a bunch of toggle buttons.

In lesson 3, we are continuing work on our extended customer form. I am going to show you how the tab controls work, and I am going to show you how to display an image on your forms and your reports without actually saving that image in your database file.

In lesson 4, we are going to build a family member form for the table that we created in lesson 1, and we are going to add it as a subform inside of our extended customer form. We will also make the relative type combo box and a helper form for it.

In lesson 5, we are going to look at some of the toolbox controls that we have not worked with yet.

Quiz

Q1. What is the main focus of Microsoft Access Expert Level 4?
A. Learning additional form controls such as option groups and toggle buttons
B. Creating advanced reports
C. Designing database security features
D. Writing VBA code for automation

Q2. Why might you create an extended customer information table?
A. To store sensitive or less common customer data separately
B. To keep a backup of all customer records
C. To hold customer logins and passwords
D. To store customer financial transactions

Q3. What type of relationship allows each customer to have only one extended information record?
A. One-to-one relationship
B. Many-to-many relationship
C. One-to-many relationship
D. Self-joining relationship

Q4. Which form element is specifically mentioned as being part of the lessons in this class?
A. Option groups
B. Action queries
C. Parameter fields
D. Crosstab queries

Q5. What example does the instructor give for a one-to-many relationship in this course?
A. Family members per customer
B. Invoices for sales orders
C. Products per invoice
D. Employees per department

Q6. What should you do if you are unfamiliar with SQL or table relationships as presented in this course?
A. Review Access Expert Level 3 or earlier
B. Skip these topics in the course
C. Consult the Access online help only
D. Only focus on forms and reports

Q7. For users working with Access 2010 or 2007, what advice does the instructor give?
A. The lessons are still applicable, as the interfaces are very similar
B. The classes will not work with these versions
C. Upgrade immediately before starting
D. Download different sample databases

Q8. How does the instructor suggest you get the most out of the course?
A. Watch each lesson through once, then replay and follow along step by step
B. Skip ahead to more advanced lessons
C. Only read the provided handouts
D. Try applying lessons to your own unrelated projects immediately

Q9. What is the benefit of using student forums as discussed in the video?
A. To see others' questions and instructor responses
B. To download all course materials
C. To access new features in Access 2013
D. To unlock additional lessons

Q10. Which of the following is NOT identified as a division in the course series?
A. Professional
B. Beginner
C. Expert
D. Advanced

Q11. What is recommended before applying what you learn from the lessons to other projects?
A. Master the sample database created in class
B. Immediately use the lessons in your real-world projects
C. Skip the sample database for efficiency
D. Only watch the videos for theory knowledge

Q12. How does the instructor suggest handling confusion or difficulty with a topic in the lesson?
A. Watch the video again or ask in the student forum
B. Skip the difficult lesson and move on
C. Change to a different learning platform
D. Email Microsoft support directly

Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is Expert Level 4 in our Microsoft Access series. I am Richard Rost, and in this class, we will be focusing mainly on some more advanced form controls.

First, we will be looking at option groups, toggle buttons, the image control, and several additional controls you can use to enhance your forms. I will show you how to set up an extended customer information table. This table is designed to hold information that you may not want in your main customer table, either because it is sensitive or because you might not have it for everyone. From there, you will learn how to set up a one-to-one relationship, which ensures that each record in the primary customer table matches with one and only one record in the extended table.

Next, we will spend more time working with one-to-many relationships. These are the most common table relationships in Microsoft Access, so I want to give you several real world examples. In this class, we will create a family members form for customers. This will let you add as many family members as you need for each customer, storing details like name, date of birth, and their role in the family, such as mother or father.

We will also work more with subforms, combo boxes, helper forms, and more. If you have completed Microsoft Access Expert Level 3, you are well prepared for this class. Level 3 introduced SQL and some basic database programming, and I will continue to build on those concepts here. We previously put together a callback form, created contact subforms, and learned how to calculate totals in form footers.

If you are not familiar with these concepts, I suggest reviewing Access Expert Level 3. If you are new to working with multiple tables, definitely look at Expert Levels 1 and 2 to make sure you are comfortable with the basics.

For this class, I am using Microsoft Access 2013. If you are working with Access 2010 or 2007, you should be able to follow along just fine since the interfaces are almost identical. If you are on Access 2003 or earlier, I recommend you check out my Access 203 lesson, which covers similar lessons specifically for that version.

To get the most out of this course, I encourage you to create the sample database along with me as you watch. But, if you prefer, you can download the databases for this and previous lessons on my website at accesslearningzone.com/databases.

All my courses are divided into Beginner, Expert, Advanced, and Developer groups. The Beginner lessons cover all the fundamentals you need if you are starting out. This Expert Series is designed for users who already know the basics and want to dig a little deeper. Advanced lessons cover things like macros and automation, and Developer lessons will take you into Visual Basic programming inside Access.

Each group is broken down into numbered levels. For example, the Beginner Series had nine levels. This is Level 4 of the Expert Series, and I expect there will be about ten levels here before moving on to the Advanced track, although sometimes I add extra topics if needed.

In addition to the regular classes, I have seminars focusing on specific concepts, such as creating web-based databases, building calendar-style forms and reports, securing your database, working with images and attachments, managing work orders, tracking accounts payable, and learning SQL.

You can find all the details about these seminars on my website at accesslearningzone.com.

If you have any questions about today's topics, you're welcome to post them in the student forums. If you are using my custom video player or the online theater, you will see the forum for each lesson right next to the video, provided you have an internet connection. Here you can browse questions from other students, see my responses, and join the conversation. If you are not watching online, you can still access the forums later at accesslearningzone.com/forums.

To make the most of this course, I recommend that you first watch each lesson all the way through without doing anything on your computer. Then, go back to the beginning, follow along, and build the same database step by step as I do in the lesson. It is best to master the class material before attempting to apply these ideas to your own projects. If anything is unclear, rewatch the lesson or ask in the forums for help. Just keep in mind that Access can seem intimidating at first, but once you get into it, it becomes much easier.

Let me give you a quick overview of what we will cover in today's class:

Lesson 1 is about building tables to hold additional customer information and other data you do not want stored directly in the customer table. I will show you how to set up a one-to-one relationship.

In Lesson 2, we will create the extended customer form. We will work with option groups, list boxes, and multiple toggle buttons.

Lesson 3 continues with the extended customer form. I will show you how to use tab controls, and you will learn how to display an image on forms and reports without actually storing the image inside the database file.

In Lesson 4, we will build a family member form for our earlier table and add it as a subform on the extended customer form. I will also cover how to create a combo box for relative types and a helpful form to manage them.

Lesson 5 will explore some form controls in the toolbox that we haven't covered yet.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Option groups in forms
Toggle buttons in forms
Using the image control
Creating an extended customer information table
One-to-one relationships in Access
Setting up a family members table
Specifying relative type (mother, father, etc.)
Creating one-to-many relationships
Building subforms
Creating combo boxes
Designing helper forms
Tab controls in forms
Displaying images on forms without storing them in the database
Adding subforms to forms
Creating and using list boxes in forms

Article

Welcome to this Microsoft Access Expert Level 4 tutorial. Today, we are going to take a more advanced look at form controls and relationships within Access, focusing on some tools that can really enhance your database applications.

We are starting by exploring additional form controls such as option groups, toggle buttons, and the image control. These are powerful elements that give your forms more interactivity and help you organize data entry in ways that standard text boxes and combo boxes cannot.

To give these new controls a useful context, we are going to set up an extended customer information table. The idea here is that sometimes you have customer details that are either sensitive in nature or that you simply do not have for every customer. For example, you might wish to store emergency contact info, customer preferences, or private notes. You would not want to put such fields in your primary customer table because not every record will have this data, and you do not want to crowd your main table.

To solve this, we are going to create a one-to-one relationship. In Access, a one-to-one relationship means that for each record in the primary table, there can be at most one related record in the secondary table. Here, every customer can have, at most, one extended customer information record. To establish a one-to-one relationship, both tables need a primary key, and the secondary table references the primary key from the main table as its own primary key. For example, if your Customer table has a field called CustomerID as a primary key, your ExtendedCustomerInfo table would also use CustomerID as its primary key, and set it up as both a primary key and a foreign key. This setup ensures you can only have a single ExtendedCustomerInfo record per customer.

After covering that, we will look more closely at one-to-many relationships. This is the classic relationship type in Access, where one record in a primary table can relate to many records in a secondary table. An example relevant to this lesson would be storing multiple family members for each customer. Imagine you want to keep track of each family member's name, date of birth, and their relationship to the customer. You would create a FamilyMembers table with a foreign key field, such as CustomerID, linking back to the main Customer table. Then, on a form for entering customer details, you can use a subform to allow entering as many family members as needed for that customer.

Subforms are a cornerstone of working with related data in Access forms. When you embed a subform into a main form, you allow users to add and view related records easily without leaving the main form context. This is perfect for our family members example, enabling users to add multiple family members without having to manually match keys or look up related information.

Along the way, we will build more forms and subforms, use combo boxes to make data entry more efficient, and explore helper forms to maintain lookup lists. For instance, if you have a combo box for choosing the relationship type (such as Mother, Father, Sister, etc.), you can link it to a small helper form to add new relationship types as needed.

Access 2013 is our sample environment, but the tasks covered here are virtually identical whether you use Access 2010 or 2007. Only a few minor interface differences exist. Anyone using 2003 or earlier would need some adjustments but the underlying concepts remain the same.

You might be interested in downloading ready-made sample databases for these lessons if you want to experiment directly rather than building from scratch. You can find these on specialized Access tutorial websites, but I strongly encourage you to try building the database yourself first, as this is the best way to learn and remember how everything fits together.

Building on previous knowledge is important. If you are comfortable with table relationships, forms, and basic controls, you will have a smoother experience here. If terms like one-to-many relationships, subforms, combo boxes, and form footers with calculated totals are new to you, it would be worth reviewing foundational topics first.

Now, let us take a closer look at how to implement these features step by step. To start, we will create two tables. The first is the Customer table, which contains the general information you need for every customer, such as name, address, phone, and so on. The second is the ExtendedCustomerInfo table, which includes fields like EmergencyContactName, EmergencyContactPhone, Notes, or other optional or sensitive information.

Here is how you could set up these tables. In the Customer table, you will create a primary key, typically an AutoNumber if you are starting fresh:

CustomerID AutoNumber Primary Key
CustomerName Short Text
...

In the ExtendedCustomerInfo table, make the CustomerID the primary key as well (not AutoNumber), and make it a foreign key linked to CustomerID in the Customer table:

CustomerID Number Primary Key, Foreign Key
EmergencyContactName Short Text
EmergencyContactPhone Short Text
SensitiveNote Long Text

When linking these tables in the Relationships window, drag CustomerID from Customer to CustomerID in ExtendedCustomerInfo, and enforce referential integrity. This links them one-to-one.

On your form, which displays Customer data, you can create a subform for ExtendedCustomerInfo. Since the relationship is one-to-one, ensure the subform data entry works on a one-record basis. You can use form controls like option groups or toggle buttons to represent fields such as Contact Preference (call, email, text), or to indicate a Yes/No choice, such as whether the customer has opted into a specific service.

To add more polish, you might use an image control on the form. Rather than storing images directly in the database, which can quickly balloon your file size, store images in an accessible location (like an Images folder) and save only the file path in the database. In VBA, you can bind the image control's Picture property to this file path, for example:

Me.MyImageControl.Picture = Me.ImagePathField

This line of code would be used in the form's OnCurrent event, so that when you move to a new record, the image updates.

Moving to the one-to-many example for family members, create a FamilyMembers table:

FamilyID AutoNumber Primary Key
CustomerID Number Foreign Key
FamilyMemberName Short Text
DateOfBirth Date/Time
RelationshipType Number Foreign Key to a RelationshipTypes table

The RelationshipTypes table is a simple lookup table:

RelationshipTypeID AutoNumber Primary Key
RelationshipLabel Short Text

This lets you use a combo box in the subform for FamilyMembers to select from predefined relationship types like Mother, Father, Spouse, etc. You can also create a helper form so you can add new relationship types on the fly.

To bring this all together, build a main form for customers, add the ExtendedCustomerInfo subform for optional customer details, and add another subform for FamilyMembers. As you select a customer, the subforms show or allow entry of all their related information.

Finally, do not forget to experiment with more advanced controls that you may not have used before. The toolbox in Access offers many options like list boxes for multiple selections, command buttons for custom actions, and tab controls to organize related sets of fields into logical groups. For example, you can use tabs to separate contact info, notes, and family members, keeping your forms tidy and easy to use.

To get the most out of these lessons, it is best to read through an example, then try building it yourself step by step. Once comfortable with the structure and form controls, start applying these methods to your own database projects, gradually increasing complexity as your confidence grows. If you get stuck or want to go deeper into Access features like macros or VBA programming, there are plenty of additional resources and community forums where you can seek help.

In summary, by learning to use option groups, toggle buttons, image controls, subforms, and proper table relationships, you will be able to build much richer and more effective Access databases. Take your time to master each concept, and remember that hands-on practice is the best way to solidify your skills.