Subdatasheets
By Richard Rost
3 years ago
Subdatasheets in MS Access - What, How, & Why
In this Microsoft Access tutorial we are going to talk about Subdatasheets: what they are, how to use them, and why I don't let end-users play with them. This is a good tool for you, the developer, to quickly and easily see table data and related child records, however it's a feature that I almost never provide to end users in a finished database.
Pre-Requisite
Links
Recommended Courses
Details
Subdatasheets in Microsoft Access are a feature that allows you to view related data from different tables in a single datasheet. They are accessed through expand indicators or subdatasheet indicators, which are little plus signs to the left of a table row. When you click on the expand indicator, Access displays a subdatasheet that contains all the related data for that row. Subdatasheets are used in one-to-many relationships, where one record in a table can have many related records in another table. By using subdatasheets, you can quickly access and analyze related data without having to switch between multiple datasheets or queries. Additionally, you can customize the subdatasheet layout, change the table that it represents, and work with different types of relationships.
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, expand indicators, subdatasheets, subdatasheet indicators, related data, table relationships, one-to-many relationship, Navigation Pane, database management, data relationships, customizing subdatasheets, troubleshoot subdatasheets, subdatasheet layout, table properties, changing subdatasheet tables, how to change the table that a subdatasheet represents after the initial selection
Subscribe to Subdatasheets
Get notifications when this page is updated
Intro In this video, we will talk about subdatasheets in Microsoft Access, including what they are, how to use them, and why they are intended for developers rather than end users. I'll explain how subdatasheet indicators appear when you set up relationships with referential integrity and show you how to manage which related table appears as a subdatasheet. We'll also discuss the differences between subdatasheets and subforms, how to control access for users, and some properties you can adjust for subdatasheets in your tables.Transcript Welcome to another Fast Tips video brought to you by AccessLearningZone.com. I am your instructor Richard Rost.
In today's video, we're going to talk about subdatasheets in Microsoft Access: what they are, how to use them, and why I don't let my end users play with them.
This is an expert-level tutorial. What does that mean? Well, it's between beginner and developer. Expert classes, in my opinion, are ones where I focus on relationships between tables and other advanced stuff. So you should have a firm grasp on relationships between tables before watching this video. If not, go watch my relationships video. I will put a link down below you can click on, and then come back.
Recently, I made a change to my TechHelp free template. Now, if you open up the customer table, you see all these little pluses over here in that left column. What does that mean? These are what are called subdatasheet indicators or expanders. If you click on one of those, it will show you related records in a second table.
For example, here we have a relationship between customers and orders, and James Kirk has an order there. You'll see, if I click on the one next to me, I have two orders. Each of those is related to order details. See that? You can keep going.
How did this happen? This didn't use to be there. A few versions of the template ago, I covered this in one of the videos. I set up a global relationship using something called referential integrity. It's up under Database Tools, then Relationships. Here's a relationship I set up with CustomerT to OrderT and from OrderT to OrderDetailT. You can learn all about that in my referential integrity video.
Essentially, it prevents you from having an order that doesn't have a customer, for example, or a line item detail that doesn't have an order. Once you do this, once you set up referential integrity and create a global relationship between two tables, you'll start to see these subdatasheet indicators over here. You can click on it to expand.
I personally do not let my end users work with my tables directly. It's dangerous. It gives them too much power. Even a subdatasheet or a datasheet view of a form can be dangerous. I don't like giving this to my end users. You have very little control over what you can keep them from doing if they have direct access to tables. You're going to make them work with forms.
If you want to replicate this kind of behavior with forms, you can create subforms. Video on subforms, and your subforms can have continuous forms inside them. That's a continuous form. You can have a subform inside a continuous form, which Access tries to prevent you from doing, but you can do it. Here you go. Here's your main form and you've got a subform down here. You can even have nested subforms with continuous forms in them. There's a trick for that. I have videos for all this stuff.
One thing that's not easy to do is to quickly and easily change the source object of the subform. I'm going to show you how to do it with a subdatasheet in a second, but you've got to go into design view properties and all that. Here, you can actually give your user a way to click on a little tab or a button or a link or whatever and switch what's in there. You can't do that with a subdatasheet.
Subdatasheets are not for your end users. There are plenty of ways you can give your end users means to get around on your database and do cool stuff like this. This is a developer tool. This is for you, the designer of the database, the person who understands what all of this stuff is because this can get complicated looking. This is for the person that also realizes that you can very easily delete stuff.
I have referential integrity set up so I can't delete that order because there are order details. Your users can easily come in here and do something like that and there's no control over that. At least in your form, you can give them control over what they can and can't delete. End users should not go into the tables. Don't let them in your tables or your queries directly. They get forms and reports and that's it.
Now, a couple of notes. What happens if you have multiple relationships between those tables? If you go to Database Tools and go to Relationships and let's say I add the contact table in here. The contact table is also linked to the customer ID in the customer table. We'll set up referential integrity. There we go.
Now, customers are related to both of these. Save that. Then we're going to come back out to our customer table and open it up. Now, I'm going to open up the subdatasheet indicator again and immediately it asks me because now it sees there are multiple relationships. So, do you want Order like we had before, or we can go to ContactT, and it guesses what the linked fields are down here just like a subform will do. You can also pick queries and stuff. Now we're linked to the contact.
That's actually saved in the table design. If you want to change it, there's no easy way to come in here. You can't right-click. Wait, you can right-click once this is expanded. You can right-click on this little guy. Data sheet, and there are some options in here. You can change how it looks - raised, flat, sunk in, the colors. There are data sheet border options and a column header underline option. Honestly, I never use this. I am just showing it to you. If you want to play with it, go ahead. I don't bother with this stuff. I use forms.
If you want to change this back to Orders, you have to go to design view. Go to the properties for the table. Right-click on the title bar. Go to properties. Here you'll see Subdatasheet Name. It's the ContactT table. You can switch this back to OrderT. It guesses what the link fields are again. You can change those if you want to. If you've got a table with a different name like Customer, Employee, whatever. There are options up here too: Subdatasheet Expanded and Height. You can control that. Close it.
Now when you open up the CustomerT, you can go back in and you can see we're back to Order and OrderDetail. That's what your subdatasheet is. Use them if you want to for yourself, you the developer. Don't let your end users in your tables directly. Give them forms and subforms to play with.
I have lots of videos teaching you how to do that. I don't even talk about subdatasheets until Access Expert Level 2. That's after my nine beginner classes and Expert 1. In Expert 1, I cover basic relationships and how that all works. Then in Expert Level 2, we talk about table subdatasheets and stuff.
But again, I don't use them that often. I really don't. I prefer to work with forms. Tables are okay once in a while if you have to poke around in there and figure out something that happened or where something is or why those deleted records go. That kind of weird stuff. But no, I don't use them.
Of course, shameless plug: if you want to learn lots more about this kind of stuff, I have a Relationship Seminar where I cover everything you could possibly want to know about different kinds of relationships. We cover it all. Relationships one-to-one, one-to-many, many-to-many, self joins, reverse relationships, all kinds of different examples.
This is one of my most popular seminars. If you want to learn about relationships and all the rest of them, get this.
That's it: subdatasheets. I hope you learned something today. Live long and prosper, my friends. I'll see you next time.Quiz Q1. What is the primary function of a subdatasheet in Microsoft Access?
A. To display related records from another table directly in a datasheet view
B. To create visual charts and graphs from your data
C. To export data to Excel automatically
D. To enforce referential integrity between tables
Q2. What causes subdatasheet indicators (the little plus signs) to appear in an Access table?
A. Adding a primary key to the table
B. Setting up a global relationship with referential integrity between tables
C. Saving the table in design view
D. Adding a calculated field to the table
Q3. Why does Richard recommend not letting end users work with tables and subdatasheets directly?
A. Subdatasheets are only available in newer versions of Access
B. Users might accidentally change table relationships
C. Subdatasheets give users too much power and too little control, increasing risk of accidental changes or deletions
D. Users cannot navigate between records using subdatasheets
Q4. What is the recommended alternative for giving end users access to related data?
A. Letting them use the tables directly
B. Allowing them to modify relationships
C. Using forms and subforms to display and edit related data
D. Exporting all data to Excel
Q5. When you have multiple relationships between tables, what does Access do when expanding a subdatasheet?
A. Automatically disables subdatasheets
B. Forces you to close all other tables
C. Prompts you to choose which related table or query to display
D. Deletes the existing subdatasheet settings
Q6. If you want to change which table is shown as the subdatasheet for a table, which of the following must you do?
A. Edit the field in the table's datasheet view
B. Go to the table's properties in design view and adjust the Subdatasheet Name property
C. Right-click the plus sign and select Change Subdatasheet
D. Drag and drop a table directly onto the datasheet
Q7. What can subforms in Access do that subdatasheets cannot?
A. Be nested within continuous forms and allow developers to change the source object dynamically
B. Display unrelated tables in the same window
C. Synchronize data with Excel
D. Allow users to set referential integrity
Q8. According to the video, why might a developer occasionally use tables and subdatasheets directly?
A. For quickly exploring and troubleshooting issues within the data
B. To print the data directly from the table view
C. To create complex reports
D. Because they are always faster than forms
Q9. What seminar does the instructor recommend for in-depth learning about relationships in Access?
A. Advanced Forms Seminar
B. Relationship Seminar
C. VBA Programming Seminar
D. Macros and Automation Seminar
Q10. At which level of the instructor's courses are subdatasheets formally discussed?
A. Beginner Level 1
B. Expert Level 1
C. Expert Level 2
D. Developer Level
Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-B; 7-A; 8-A; 9-B; 10-C
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 TechHelp tutorial from Access Learning Zone is all about subdatasheets in Microsoft Access. I want to explain what they are, how you can use them as a developer, and why I never allow my end users to access them directly.
This lesson is aimed at the expert level. It assumes you're already comfortable working with table relationships in Access. If you're not, I recommend taking the time to review my tutorial on relationships first before proceeding with the material here.
In a recent update to the TechHelp free template, you might notice that when you open the customer table, there are now plus signs in the leftmost column. These are called subdatasheet indicators. When expanded, a subdatasheet reveals related records from a second table. For instance, with a relationship set up between customers and orders, expanding the indicator for a particular customer will show their related orders. If that order has related details, you can keep expanding to drill further down.
This feature appears after establishing a global relationship between tables using referential integrity, which you can access under Database Tools, then Relationships. If you need a refresher on referential integrity, I have a separate video dedicated to that. Briefly, referential integrity prevents inconsistent data, like creating an order without an associated customer or an order detail without a related order. After you set up these relationships, subdatasheet indicators automatically appear, allowing you to expand and view related data within your tables.
Now, while subdatasheets are a powerful developer tool, I never let end users interact with tables directly. Allowing them access in datasheet view, even with subdatasheets, can be dangerous since there's very limited control over what users can do. Users can inadvertently delete important data, even with referential integrity in place, and there is only so much you can restrict at the table level. For this reason, I require end users to work exclusively with forms and reports rather than tables or queries directly.
If you want your forms to emulate the expandable behavior of subdatasheets, Microsoft Access allows you to build forms with subforms. For example, you can create a main form that contains a subform displaying related records in continuous form view. Although Access tries to prevent nested continuous subforms, there are ways to achieve this, and I offer tutorials that cover these techniques. Moreover, forms provide far greater control over user permissions and enable creative navigation, such as using buttons or tabs to swap out subform content, something impossible with a table subdatasheet.
Subdatasheets exist to aid developers and advanced users in managing and reviewing data relationships. The complexity and risk of accidental deletions mean they are meant for those who thoroughly understand the underlying data structure and constraints. Allowing regular users to access tables invites unnecessary risk.
Consider what happens if you have multiple relationships coming from the same table. For example, if you link a customer table to both orders and contacts, when you open the customer table and attempt to expand a subdatasheet, Access will prompt you to select which related table you'd like to view. Access tries to infer the related fields, much like it does with subforms. This preference is actually saved in the table's design properties. If you wish to change the default subdatasheet, you need to open the table in design view, access the table properties, and choose a different subdatasheet source from there. The interface offers basic formatting options as well, though I very rarely use these and recommend focusing on form-based solutions instead.
To summarize, subdatasheets can be helpful for developers inspecting table relationships and related records quickly, but they should not be exposed to end users. Instead, rely on forms, subforms, and reports for all user interaction. I cover subdatasheets only after my beginner and introductory expert classes, as they are truly an advanced topic.
If you're interested in learning even more about table relationships in Access, consider looking into my Relationship Seminar, where I discuss everything from basic one-to-one, one-to-many, and many-to-many relationships to more advanced topics like self joins and complex relationship examples.
For a complete video tutorial that walks you through all these concepts with step-by-step instructions, visit my website at the link below. Live long and prosper, my friends.Topic List What subdatasheets are in Microsoft Access
How to identify subdatasheet indicators in tables
Using subdatasheets to view related records
Relationship between customers, orders, and order details
Effect of referential integrity on subdatasheets
Setting up relationships to create subdatasheets
Risks of allowing end users direct table access
Difference between subdatasheets and subforms
How to nest subforms for similar behavior
Changing the subdatasheet source via table properties
Managing multiple relationships and subdatasheet selection
Customizing datasheet appearance options
Adjusting subdatasheet properties in table design
Switching the subdatasheet display to a different table
Controlling subdatasheet expansion and height
Why subdatasheets are primarily for developers |
