Add Anything
By Richard Rost
4 years ago
Add User Editable Supplemental Fields to Access
In this Microsoft Access tutorial, I will show you how to add a set of supplemental data fields to your database. This will allow you to enter any kind of additional information that you want to any table, and your users can specify new fields if they want to.
Wayne from Bismark, North Dakota (a Platinum Member) asks: One thing I love about my Android cell phone is that if I want to add more phone numbers, I can. Want to add a few extra email addresses? No problem. Want to add a birthday and anniversary? You got it. Any way to do this with Access?
Members
Members will see how to specify data types for the supplemental fields. We'll add numeric and date fields and check to make sure the user entered the correct type of data.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Suggested Course
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, add any data field, add new data types, add random fields, user defined fields, supplemental data, User-Defined Types, How do I add a user-defined field, Create User-Defined Fields, Supplemental Data Tables, android contacts, extra data, extra fields
Subscribe to Add Anything
Get notifications when this page is updated
Intro In this video, I will show you how to let your users add any type of extra information they want to records in a Microsoft Access database by creating user-editable supplemental fields. We'll talk about building a many-to-many relationship, setting up the necessary tables, creating a subform for entering additional data, and adding an easy way to edit the list of available field types. If you want to make your Access database as flexible as contact lists in an Android phone, this video will walk you through the process step by step.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I'm going to show you how to add anything you want to a record in a Microsoft Access Database. We're going to create user-editable supplemental fields.
Today's question comes from Wayne in Bismarck, North Dakota, one of my platinum members. Wayne says, one thing I love about my Android cell phone is that if I want to add more phone numbers, I can. Want to add a few extra email addresses? No problem. Want to add a birthday and an anniversary? You got it. Anyway, how do you do this with Access?
Well Wayne, yes, you can do it. It just involves a little bit more work and we have to know how to do something called a many-to-many relationship. Let me show you how.
Now, this is one of the things Wayne is talking about. I love the contact manager in my Android phone. It's got some default fields: your name, your mobile phone number, your email address. And if you want to add more, it's very easy to add additional fields. Drop down the mobile field, for example, and you've got work, main, work fax (people still use faxes), pager, other. You can add a custom type if you want to. You can do this for most of the different data types in here.
So how can we do something like this with Access? Well, how about if we add a many-to-many relationship with the subform here, then we can add any kind of field we want. Drop this down. You want to pick their favorite band, put in Rush, for example, or their favorite color, or anything that you want to add in here. You want to add an item to this list, just drop this box down, type in something else. All right, Starship name. Okay, close that, and now you've got an option in here to put in their Starship name.
How do we do something like this? Well, let's take it from the top.
Before we get started, I've got some other stuff I want you to watch first. If you don't know some of this stuff, then go watch these videos. Start with my blank template. This is what I base all my videos on, so I don't have to keep reinventing the wheel. If you haven't watched this yet, go watch this first.
You'll need to know how to do relationships. Go watch my relationships video. These are all free videos by the way, you'll find links down below under the video. Go click on those, watch these, and come back.
You'll need to know how to make relational combo boxes. Now here's a tricky one for a lot of people: many to many relationships. If you've never built one of these before, go watch this video right now. Pause this video, go watch "many to many," and then come back. This is important.
And this is what I just released a couple days ago. Go watch the list items edit form. This one isn't that important. Go watch this if you can, but I'm going to be building one of these today.
And if you have a free minute, watch my "consolidate tables with helper data." That's very similar to what I'm going to be doing today. If you've got a few minutes, watch this one first. Go watch all that stuff, then come on back.
Okay, so we're going to build two tables to add information to our customer form. It's not going to be in the customer table, because obviously in our customer table, we've got individual fields - first name, last name, email, and so on. We're going to add another table. It's going to hold related information for this customer.
Then we're going to have another table beyond that one, so we can track what kind of information each one of those items is. I know it sounds confusing, but bear with me. Essentially one table is going to hold the fields like cell phone, work email, all the extra stuff you want to store. And then another table after that one will store the actual data itself.
Okay. So let's go create table design. All right, this first one is going to be the list of fields. I'm going to call it other types. Okay, so other type ID, that'll be an autonumber, and then other type name, and that'll just be short text. This will be the list of other types of data. So save this, other type T, my other type table, primary key, sure. And just put a list in here of what types of information you want to be able to store. So cell phone, maybe home phone, work phone, favorite color, favorite band, whatever, whatever other types of information your users might want to store. Stuff that you might not have for everybody, because obviously if it's something important and you're going to try to get it for everybody, you're going to make that an actual field in the customer table. So this is just like random stuff that you might or might not have.
All right, so close this table. Now we need another table where I can say, okay, here's the customer, here's the type of data I want like cell phone, and then the actual data itself. All right, so create, table design. This one's going to be the other data table. So other data ID, that's my autonumber. I'll need a customer ID, who does it belong to. That's the foreign key, so it's a number of type long integer. Then the other type ID, that's also a foreign key that relates back to the other table we just made. And then the actual data itself, we'll call it other data, short text.
Okay, save this as other data T, primary key, sure. And there we go.
All right, so now if we want to put some sample data in here, because it's always good to have some sample data, keep in mind what your other type table has in it. So we've got cell phone, work phone, all that stuff here. Some sample records might look like this: Customer one, favorite color, and it's blue. Customer one, favorite band is Rush. Customer two, Jim Kirk. His cell phone is 555-8888. See how this data is filling into the table?
Okay, now we're going to make a form to put all this stuff together. We'll make a continuous form, all right, that will be a subform in the customer form. We're going to have this, which will be a combo box, so you'll see this list of items there. And then the actual data itself, the customer ID will be in there because of the relationship between the parent form and the child form.
All right, so let's make this continuous form now. I've already got a continuous form right here from my template. I was just copy and pasting that. We'll call this the other data F. Okay, other data F, where are you? Right down here. And we'll go to design view. We don't need the ID, we don't need to see any IDs in here. In fact, I'm going to get rid of that label because this will be pretty self-explanatory.
Let's bind this guy to the other data table. Now this will actually be the data text box that we're typing in. So we're going to bind this one - let's go to all here. We're going to bind this field to the other data field, that's freely entered text.
Then we need a combo box to pick the type. Go up here to the control box, find a combo box, drop it right there. Look up the values from a table or query. Where are we getting the list of data from? The list of data items is coming from the other type T. That's the list of types. Bring them both over because we want the ID, we want to see the name. Sort them by name. That's what it's going to look like when the box opens up. Remember, the key column is hidden.
We're going to store that value in the other type ID. We're picking a type for this data. What label would you like? Doesn't matter, we're going to delete it anyways, and then finish. Get rid of that label that comes in there. So there's our combo box that lists the type, and here's the actual data itself. The tab order is going to be backwards. So I'm going to come up here and fix that real quick, tab order. Oh, it's called combo4. Let's fix that too. We want this to be, let's call this "other type combo." Save it. Get rid of that footer. Save that and close it.
Now see what we've got in here if we open this up. There's everybody. There's my two items, and there's one from Jim Kirk. The relationship will be formed when we make this a subform.
All right, so close that. Open up the customer form. Let's go to design view. I'll just move this stuff out of the way. Slide that over here. We'll do this. Make these fit under here nicely. Okay, and we'll put our subform right there. Ready? Grab it. Click, drag, drop. Slide it where it needs to go. Get rid of that label that comes in with it. Resize it. Make it look pretty. And come here. I can never grab the edge of this thing. Get that little thing right there. Drag it down. Now let's check the properties real quick. Double click on the edge. Link master field and child field should both be customer ID. There's a customer ID in here. It's not on the form itself, but it's in the record set under the form, so you can use it. Okay, and they've got a customer ID out here.
Now save it. Close it. Open it back up again and boom. There you go. If you want to turn off the navigation buttons and all that you can. Scrollbars, I like to leave them on for this. But now when I add something down here, favorite color we already got? Let's see cell phone. Put your cell phone in there.
Now you want to give your users a nice, easy, fast way to edit this list. To add something custom on here if you want to. What's his, what's starship does he pilot? Well, in that case, let's make a list items add form or list items edit form. That's another video I just released a couple days ago. Go watch this one if you don't know how to do this one. But I'm going to show you anyways.
We're going to make a simple little editing form. So I'll just copy this guy here. Copy, paste. This will be the other type F. Edit. Design view. Get rid of that. Slide that over. Get rid of that. Slide that up like this. Scrunch that down. Let's just change the color real quick. Let's make this a... If you're going to see part of this, let's make this guy blue. Just change it up a little bit, and we'll go with that blue in here. Just so this is slightly different.
Change this. The record source is going to be the other type T, and then this guy is going to be based on the other type name. And we'll change the name here as well. Save that. Open it up. There's your other type.
If you want this to be sorted, because right now it's just in any old order, you can make a query and base this on that. Or if you want, watch this. Come in here, go to the record source at the dot, dot, dot button right there. You want to create a query based on the table. Say yes. Bring in the other type ID and the other type name. Just like you're building a query, come down here and sort it. Close this. Say yes. It's going to write the SQL and put it right inside that record source for you.
Learn SQL, by the way. This is just "select this field, this field from this table" and then the "order by" is your sort. I have a whole set of different classes on learning SQL. If you want to make your databases much more powerful, learn SQL and VB.
Quick commercial: my SQL seminars. Part one is really all you need. That's the good one. It teaches you the basics of SQL. Part two is doing action queries, updates, and that kind of stuff. Changes, adding to a table, making tables. And then part three is about editing the structure of your database. So check those out.
So now we just have a sorted list in here. We can close this, save changes. Yes. Now, if I open this guy up, there's that. Now I'm going to move it here into the middle a little bit, like that. Save it. Close it.
Watch this. Now come in here. We're going to go into design view. Let's resize that a little bit. I want to pick this combo box right there. Now watch, see what just happened? When you click on this the first time, it gets the subform. Then you have to click on it the second time. Now we're editing the field inside the subform. Be careful. A lot of people get confused about that one.
If you're out here working on this, and you click on this, now you've got the subform selected. Click on it the second time, and you get that combo box. Go to Data. Find the list items edit form, and we're going to make that the one we just created, the other type F. Save it. Close it.
Now I'm in here. Sir, what starship do you pilot? Oh, I don't know. Let's hit the button down here and add it. Now we can add starship as one of our types of data that we want to collect. And if you drop this down, look at that, starship is in there. And surprise.
Go to somebody else. Oh, Mr. Kirk, what starship are you on? Oh, well, I'm also on the Enterprise. What else do we have in here? The Enterprise. All right, she's also on the Enterprise. Let's say, favorite food. Not in there. Well, let's add it. Favorite food. And now we can pick favorite food. And we'll put chocolate. Chocolate. Chocolate. Even though I read somewhere the actors hate chocolate.
See what we did? Now we can basically make our own fields. Obviously, you lose some power. I mean, like input masks and formatting. You're just saving this data as text. So, if it's important data, it should go into its own field in the table. But this is perfect for that random stuff that you might or might not need. Just extra bits of information. You don't want to necessarily throw it all into a text field, like a big long text field, like a memo field.
Over time, when you tell your people, start asking our customers what their favorite food is, or start asking their birthdays, once you see that you've collected enough of this information, maybe then move it over into an actual field in your table. This is just, like a secondary bit of information.
But this also gives your users the ability to create their own fields in a secondary table, without having to design the database, because you don't want your end users doing that. But they can add fields in here. Then you, the developer, can look at it later on and be like, well, we've collected 5,000 records that include starship data. Let's move that over into the primary customer table, and then we can control it better, and we can query on it better.
See how this works? Now, members, I am going to show you a couple more techniques that we can do. For example, let's say you want to collect birthdays. Birthdays. Now I come in here and put the birthday in, and there's nothing to stop someone from doing June. We can put a little bit of control on here with some VBA. We can say, okay, if this is a date, I want to at least make sure it's a valid date before I let them save it. We can do some checks in VB. Or we can check for numeric value. There are all kinds of things we can do with VB under that.
So, members, in the extended cut, that's what we're going to do. We'll enforce some data types. We'll check for numeric values, we'll check for date values, and our other type form will specify what data type that is, either text, numeric, or date. Currency is basically numeric. Then we'll put custom error messages up. If they type something in that's not valid in there, it'll be a date required. Then we can also format it.
Here's mine. Let's say I have a birthday in here, so we'll come in here, we'll put in a birthday. Where's birthday? There's birthday. If I just type in "June," it yells at me. "Validate required." And then if I just put in like "2/6," like that, it'll format it for me then, too. And knows it's the date and it formats it, because I like the ISO date standard.
Same thing with numbers. If I try to put a numeric value in, like number of children. I put X in there. Numeric value required. And you can force it to make sure it's a nine. When we go to add these, you'll see here, we can specify what type of date that is, what type of field that is. That will be in the extended cut for the members.
Also, if you want to learn a lot more about many to many relationships, I cover them in great detail in my Access Expert Level 7 class. Lots and lots of stuff. I go over all the basics and the background between junction tables and how to set these up properly. How to put customers in groups, that kind of stuff. If you want to learn about many to many relationships, Access Expert Level 7, I'll put a link to that down below in the link section below the video. And this will be covered in the extended cut for the members. Silver members and up get access to all of my extended cut videos. Gold members can download the databases and get access to the code vault.
I hope everyone learned something today. We'll see you next time.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a diamond sponsor and have your name or company name listed on a sponsors page. They'll be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.Quiz Q1. What is the main purpose of the video tutorial?
A. To show how to create user-editable supplemental fields in a Microsoft Access database
B. To explain database normalization rules in Access
C. To demonstrate exporting Access data to Excel
D. To discuss Access security features
Q2. What type of relationship is required to add flexible, user-defined fields to a record in Access, as described in the video?
A. One-to-one relationship
B. Many-to-one relationship
C. Many-to-many relationship
D. Self-referencing relationship
Q3. Where should permanent and important fields (like first name, last name) be stored according to the video?
A. In the supplemental fields table
B. In a memo field for each customer
C. In the main customer table
D. In a separate text file
Q4. What is the function of the "other type T" table described in the tutorial?
A. To store all customer contact details
B. To list possible types of supplemental information (e.g., cell phone, favorite color)
C. To hold only numeric data fields
D. To store customer addresses
Q5. What does the "other data T" table store?
A. Employee payroll data
B. The actual supplemental data values linked to each customer and data type
C. Records of deleted data
D. The customer's favorite food recipes
Q6. Why is a combo box used in the continuous subform design?
A. To allow users to select which customer they want to view
B. To select the type of supplemental information being entered for a customer
C. To restrict access to certain fields
D. To export data to Excel
Q7. How can users add new types of supplemental data entries (such as "starship") to the list?
A. By modifying the original customer table
B. By writing new VBA code each time
C. By using a list items edit form designed for the "other type T" table
D. By changing the database structure in the backend
Q8. What is a recommended way to handle data like date of birth or numeric fields according to the video?
A. Always store all data as plain text
B. Rely entirely on user input without validation
C. Use VBA to check and enforce data types such as numeric or date
D. Require users to manually check input for validity
Q9. When should you consider moving a frequently collected supplemental field into the main customer table?
A. As soon as one user requests it
B. Never move it, always keep supplemental data in a separate table
C. After collecting enough of the same type of data from multiple users
D. Only if the field is numeric
Q10. What does Richard suggest as a benefit of this technique for end users?
A. It allows end users to change table design directly
B. It lets end users create new database objects
C. It enables end users to add their own data fields via the interface, without altering the main database structure
D. It makes the database structure more complicated
Q11. What is recommended if a user wants to edit or sort the types available in the supplemental data type list?
A. Edit the customer table directly
B. Use a query to sort and a dedicated edit form for the "other type T" table
C. Change table relationships manually
D. Rebuild the database from scratch
Q12. What should a developer do once a certain supplemental type becomes important and widely used?
A. Leave it as is forever
B. Move it into the main table for easier querying and validation
C. Delete older entries
D. Move it to a spreadsheet
Q13. What role does the subform play in the overall Access form design described in the video?
A. It provides navigation buttons for the main form
B. It displays and allows editing of supplemental data linked to the current customer
C. It summaries all customers in the database
D. It imports data from external sources
Q14. What is emphasized as important to fully understand before attempting this tutorial?
A. Macros and VBA automation only
B. How to export Access data
C. Relationships, particularly many-to-many relationships, and relational combo boxes
D. How to use PivotTables
Q15. What extra features can members learn about in the extended cut of the video?
A. Data security and encryption
B. Using VB and SQL for advanced data validation and formatting supplemental fields
C. Exporting data to different cloud services
D. Access installation troubleshooting
Answers: 1-A; 2-C; 3-C; 4-B; 5-B; 6-B; 7-C; 8-C; 9-C; 10-C; 11-B; 12-B; 13-B; 14-C; 15-B
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 focuses on how to add flexible, user-editable supplemental fields to records in a Microsoft Access database. Many people, like Wayne who asked today's question, appreciate how easy it is on an Android phone to add extra phone numbers, multiple email addresses, birthdays, or other details to a contact. The goal is to replicate this kind of versatility in Access, giving users a way to attach as much or as little supplemental information to a record as they want.
To approach this, you'll need to set up what's known as a many-to-many relationship in Access. This allows you to attach any number of arbitrary data items to each customer, much like Android's contact manager lets you add custom fields on the fly.
I recommend having some basic knowledge of Access before proceeding. It's important to be familiar with blank templates, relationships between tables, relational combo boxes, many-to-many relationships, and editable lists. If these topics are new, you should review my free training materials on these subjects first. Links to these resources can be found on the website.
The system we're building today involves two main tables in addition to your existing customer table. The primary customer table holds the usual set of fields like name and email. To supplement this, create a separate table containing the different types of extra information you might want to store—things like "cell phone," "work phone," "favorite band," or "favorite color." This table contains just an ID and the name of the information type.
Next, you'll need another table to actually record the supplemental data itself. This table has its own ID, a foreign key reference to the customer, a foreign key to the information type, and then a field to hold the actual data (as text). This design means that for every piece of extra information you want to store for a customer, you'll have a record indicating which customer it is, what type of data it is, and what the value is.
With these tables in place, you can then create a form for entering supplemental data. The form should be set up as a continuous subform linked to your customer form. Include a combo box for selecting the type of information from your types table, and a text box for entering the actual data. The relationship between the parent and subform ensures that only data for the current customer is displayed and edited. Remember to set up the subform's properties so that Access knows how to relate records based on the customer ID.
It also helps to make it easy for your users to manage the list of available supplemental field types. To do this, build a straightforward editing form based on the types table, giving users a simple way to add or rename items in the list. You can sort the list alphabetically by modifying the form's record source with a query.
When users want to add a new category – for example, if you want to ask which starship a customer pilots, or what their favorite food is – they can open the editing form, add the item, and it immediately becomes available in the combo box for selection.
This approach makes the database extremely flexible. Users can add custom fields whenever needed. The data for each field is just stored as text, so you lose the specific formatting or data validation you might get if each had its own dedicated field with constraints. For this reason, really important pieces of data you want to consistently collect from all customers should remain as their own fields in the customer table. This supplemental approach is ideal for the sorts of details you might only collect from a subset of customers or information that changes or evolves over time.
As your users work with the database and you discover that certain types of supplemental information are being recorded frequently – perhaps you find that nearly everyone has a "starship" entry, for instance – you can consider adding that field directly to your main customer table down the road. The system gives you the flexibility to track random details now, and then formalize and organize your data structure later, based on actual usage.
For Access Learning Zone members, I cover additional techniques in the extended cut of this video. In the extended cut, I demonstrate how to enforce data types with VBA. For example, if someone is entering a birthday, we can validate to ensure the value is a valid date and display an error message if it is not. Similarly, we can check for numeric values, and adjust the specification for what is expected in each field type. The form for editing supplemental field types can specify whether a type is text, numeric, or a date, and the system will respond with custom error prompts or formatting to help maintain data integrity.
If you want more in-depth study of many-to-many relationships, I go over these in great detail in my Access Expert Level 7 class, where I explain junction tables and techniques for connecting records in a robust and scalable way.
To sum up, today's video gives you the tools and knowledge for implementing highly flexible, user-driven fields in your Access customer database. Your users gain the power to track whatever details they consider important, without exposing them to the complexities of design changes to the database structure.
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 Creating user-editable supplemental fields in Access
Designing a table for field types (other types table)
Populating the field types table with sample data
Designing a table for supplemental data storage
Defining relationships between customer, type, and data tables
Entering sample data into the supplemental data table
Building a continuous subform for supplemental fields
Creating a combo box for selecting field type in the subform
Binding the subform to display appropriate data records
Adding the subform into the main customer form
Linking master and child fields for proper data entry
Adjusting subform layout and properties for usability
Creating an edit form for field type management
Sorting field types in the edit form using a query
Linking the edit form to the combo box's list items edit property
Adding new field types dynamically via the edit form
Demonstrating how users can create and select new custom fields
Discussing benefits and limits of this approach for flexible data storage
Recommending how and when to formalize ad-hoc fields into main tables |
