If you've developed Microsoft Access forms, you might have stumbled across subforms - a form within a form - and wondered what they're for. I find them vary handy, so here's a quick guide to help you understand when you might need one.
A subform comes in handy when you've got a parent-child relationship between two tables, and you'd like to display all related data on a single screen.
Let's start by taking a look at an example of a parent-child relationship. I'm working with the Northwind sample database, which is freely downloadable from Microsoft. That database contains an Orders table, which contains general info about the order, like who ordered it and when; and an OrderDetails table, which, as the name implies, gives details about the order, like which products were ordered and what quantity. Here's a sample of data from both tables:
If you're not familiar with parent-child relationships, take a minute to look this over. You'll see that the Order table includes a Sales Person ID, a Customer ID, and an Order Date, among other things. In the Order Details table, you've got a Product ID, a Quantity and a Unit Price.
Now notice the connection between the two, which I've highlighted in the screen shot. Order number 1 has one record in the Orders table and three records in the OrderDetails table. That means that the customer ordered three items on this order. Order number 5 has one record in the Orders table and two records in the OrderDetails table, because the customer ordered two items.
So, in both cases, we had one record per order in the Orders table, but multiple records per order in the OrderDetails table. That's called a one-to-many relationship, because each order can have many detail lines.
You might be wondering: did we need two tables for this? Couldn't we just have all of the info from the Orders table in the OrderDetails table? The answer is yes, you could have, but don't. You'll be setting yourself up for some messy data issues if you do. I'll tackle that topic in a future blog. If you'd like to receive a notification when that post goes live, use the form at the bottom of this page to sign up.
Now that you understand the parent-child relationship, let's think about how you'd present that information to the user on screen. If you're database savvy, you might just open the tables and enter information directly into them. But in most cases, it's nicer to have a form, which presents the data in a clearer way, and also allows you to add validation and feedback that don't come naturally to tables.
Without subforms, you'll create one form for the Orders table, and a separate one for the Order Details table. You'll open the Orders form, enter the order data, and close the form. Then you'll open the Order Details form, pick the order number, and enter the order details. You'll repeat that (including choosing the order number) for each line item. Here's an example of what that form might look like:
There's nothing wrong with this, except that its not particularly user-friendly. The user can't easily see who the customer is, or when the order was placed. Plus, the user has to re-choose the order number for each line item.
Here's where a subform would come in handy. What you'll do in this case is create a form for the Orders table, and on that form, you'll include a linked subform for the order details. That might look like this:
The grid with the Order Line Items, including the products, quantities and prices, is the subform. Here's how it looks in Design view:
To recap, a subform is used to display the "child" records in a parent-child relationship. It's a form in-and-of itself, but it appears as an object on the "parent" form. The two are linked together, in this case, by the order number.
I hope this helps you determine if you need (or want) a subform in your Access database. They are a powerful tool, allowing you to create robust, user-friendly applications.
As I mentioned earlier, I'll write another post to discuss the use of parent-child tables, as opposed to combining all data into a single table. I'll also cover the methodology of linking the parent form and the child subform. If you'd like to be notified when those posts go live, subscribe using the form below.
And, as always, don't hesitate to reach out with comments or questions. I'm a true data-geek, and I love working with database of all shapes and sizes!
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
#northportsolutions #learningneverstops #microsoftaccess #msaccess #database #dataanalysis #smallbusiness
© 2023 North Port Solutions, LLC. All right reserved.