Synchronize Subforms
By Richard Rost
4 years ago
Synchronize Subforms in Access Without VBA
In this video, I'm going to show you how to use two subforms to browse related records using an intermediary parent form. This will allow you to see a set of parent records in one subform, you can click on one, and then see the list of related child records in the second subform. For example, we'll see a list of starships and their crew. The same concept works for categories and products, customers and orders, employees and timecards, etc.
Pre-Requisites
Recommended 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, #fasttips, Synchronize Two Related Subforms, How to Link Two Subforms, Multiple Subforms, Synchronizing Multiple Subforms, Synchronizing Two Subforms, link subforms, ms access multiple subforms example, access link subforms
Intro In this video, I will show you how to synchronize multiple related subforms in Microsoft Access without any VBA programming. We'll use a parent-child relationship, such as starships and crew members, to create a form setup where selecting an item in one subform dynamically displays related records in another. Key topics include building necessary tables, setting up relationships, adding combo boxes for assignments, designing continuous forms, using link master and child fields, and managing subform synchronization for seamless data browsing. This fast tip focuses on making your Access forms more interactive and user-friendly.Transcript Welcome to another Fast Tips 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 synchronize multiple related subforms in Microsoft Access. What does that mean? We're going to have a parent form, a form on the left, and a form on the right. We're going to click on a parent record over here on the left, like the Enterprise D, and you'll see their crew over here. Look on Voyager, and you'll see their crew over here. This is the list of the parent records, and these are the child records.
This works with Starship and Crew, or it can work with categories and products, customers and orders, employees and time cards, anytime you've got a parent-child relationship. So, how did I build this without any VBA programming? Let's take a look.
Before we get started, we've got some prerequisite videos. If you don't know about relationships, go watch this video. It's free. It's on my website and my YouTube channel. Go watch this first. You have to have a good solid understanding of at least one-to-many relationships.
You need to know how to make relational combo boxes. That's where you can pick a customer, for example, on an order, and the combo box gets its value from a different table.
This video is all about subforms, so definitely make sure you watch my subforms video first. This is a definite. Make sure you watch this one before trying what I'm doing today. Optionally, go watch my link master-child fields video. It's a short one. It explains what these link master fields and child fields properties are. This will be helpful.
Go watch all that stuff. It's all free. It's on my YouTube channel. Go watch it. Then, come on back. Go on. Go. Get out of here. Go.
Here I am in the TechHelp free template. This is a free database. You can download a copy from my website, which you already know if you watched all those other videos.
The first thing I need for my customers – let's pretend these aren't customers. Let's pretend these are crewmen or officers or whatever. These are our people, and now we need a parent table to store what Starship they're assigned to. Let's make a table.
Create. Table design. We got the Starship ID – that's our autonumber – and the Starship name – short text. How about just name? Do we use just name? No. Why? Because name is a reserved word in Access. We never have a field called name, just like we never have a field called date, that kind of stuff.
You could put all kinds of other information in here: notes, the registry information, who the captain is, and so on. But for now, for this lesson, all I care about is just the ID and the name.
Let's save this: Starship. StarshipT. Singular. I like to keep all my tables and field names singular, so there's no question. Primary key. Yes. Okay. Let's put some data in here. Not Mr. Let's put some data data in here. And if you pronounce it data, you are wrong. It's data. One is my name. The other is not.
Just a few ships: we got the Enterprise A, we got the Enterprise D (we'll skip the ones in the middle), we got Voyager, the Defiant, and let's put Serenity in here. I know it's not a Starship, but it's one of my favorites. Close that.
Now we have to assign our people – our crewmen – to a Starship. So we have to add a Starship ID to the customer table. Close down that form, and go to design view. You can either insert it here or come all the way down to the bottom and move it up. I like to keep all my IDs up at the top of the table. It's just personal preference. It doesn't matter where they are, but I like to keep all the IDs up top.
I'll put in here a StarshipID, and that'll be a number of type long integer, which we learned from our Relationships video. This is called a foreign key. We're going to slide it right up there. Save it.
Now, could a crewman be assigned to multiple Starships? Now you're talking about a many-to-many relationship. As far as I'm aware, in Starfleet, you can't do that. You're assigned to one ship at a time. So, that's – I don't make the rules. But no, go watch my many-to-many video if you want something like that.
Like multiple cars and drivers: if you have a fleet and you've got cars that can be assigned to different drivers and drivers assigned to different cars, that's a many-to-many relationship. Wait, I got a video for it, hold on. There's that one. I got a video for everything, but we don't need this for today's lesson. Watch it if you're curious. Today we're doing a simple one-to-many relationship.
Close that. Now we need to add a combo box on the customer form to pick the Starship they're assigned to. Let's slide this down and put it right here. Go to the combo box wizard. This guy, I think, is a good wizard. There are good witches and bad witches. This is a good witch.
Add a combo box. Pick from values from a table or query. Get it from the StarshipT. Yes, I'm moving fast. This is covered nice and slowly in the relational combo box video that you're supposed to watch.
Bring over both of those fields and drop them down. Sort by the Starship name. That looks good. Next, we're going to store that value in the StarshipID. We're going to pick a Starship, store it in the StarshipID. What label do you want? Starship. There we go.
Slide you over here like so. Slide you over just a bit. I'm going to grab some format painter action and go bang. Make that nice and black. Slide you over.
Now I'm ready to put people in Starships. I'll put myself on the Enterprise D. James Kirk on the Enterprise A. We'll just do a couple here. Deanna Troi, Enterprise D. Jean-Luc, of course, Enterprise D. Who else do we have? Most of my people in here are Enterprise D people.
There's Malcolm Reynolds; let's put him on Serenity. We got Wesley, Tasha, Barclay. Oh, there's Janeway and Tom Paris; they're on Voyager. We'll just get our people assigned to the right ships. There's Sisko, he's Defiant. Julian, Defiant. One more: O'Brien. He can go both ways: Defiant or Enterprise D. He did his best work on the Defiant.
So now I have some of my crewmen assigned to Starships. Now I need a Starship Form. Right, a Starship Form. I already have a single form here from my blank template. Let's copy and paste that. Copy paste. This will be StarshipF.
Let's just do it like a traditional single form for now, just so I can show you. I only keep these guys around for formatting. Let's bind this form to the Starship table, so now this form gets its data from StarshipT. Add existing fields – there they are. Click and drag and drop.
Literally, the only reason I keep this around is for format painting. Watch: bang, and then, bang. Then we're done. Now I can get rid of you. Goodbye. You are the weakest field. Goodbye.
Close this. Of course, you could fix up your labels and make it all pretty and do whatever you want with it. Save this, close it, open it back up again. That's what we have. You can do your formatting and make it look all nice.
Now, what we learned in the subforms video is how I can now take my people and put them here as a subform. This is a parent record, and each of the customers (crewmen) have a Starship assigned.
Now, I have a customer list form. I could drop this right in the Starship form as a subform. Let me modify this real quick. I just want to make this a little bit smaller. Remove all this information here. Save it. Close it.
Now I can take the Starship form, and if I want to, I could drop the customer list form right there. Let's get rid of that label, make it bigger. Now that subform should show related people from the Starships. Let's see if it works.
Go back to my Starship form, and there we go. Enterprise A, Jim Kirk. Enterprise D, there's me, Deanna, and Jean-Luc. Voyager, Defiant, Serenity. See, that subform has made the link.
How did it make the link? If you look at the subform's properties, you've got link master fields, link child fields: StarshipID. There's a StarshipID there. Even though the StarshipID isn't on this form, Access realizes that this table (the customer table) has a StarshipID in it.
That's pretty good. Older versions of Access didn't automatically do that. New ones do – it's kind of nice. Before you – make sure you put the StarshipID on that form. But this isn't really what I'm looking for.
This is nice and all, but I want to be able to have a list of starships here, where I can click on them, and then a list of people over here that are on that starship.
So what we're going to do is utilize our main menu form if you want to, or you could make a whole separate form. I'm just going to drop these guys on the main menu form.
Let's make a copy of the main menu. Copy, paste. We'll call this my Starship Assignment Form, or whatever you want to call it. Design view. Let's get rid of all this stuff. This will be our – let's just call it a Starship Group.
Let's go back to our Starship form now, the first one we have, and I'm going to get rid of this subform. Let's make this guy look like a continuous form.
I'm going to remove these labels. We'll put our StarshipID here. You don't really need the ID, but I'll leave it there for illustration purposes. Shrink that up, shrink you up, and set you from a single form to a continuous form so you see multiple records at a time. Save it, form view – that's what it's going to look like. Get rid of that alternating color. I hate that. I keep meaning to turn it off in the template, but I haven't yet. No color.
By the way, if you want to learn more about continuous forms, go watch this video. This is a prerequisite for one of the other videos I told you to go watch, so if you watched all the prerequisites, you should have seen this one already. Don't skip the prerequisites.
That's why I have a full regular course; I teach things in order, so you don't have to jump around. That's why I have beginner level one, level two, level three, and so on. For these TechHelp videos and the Fast Tips videos, sometimes I make you bounce around a little bit.
So we got this. Let's see what this looks like now. That looks good. This is going to be the left side, the parent side subform over here. Ready, click, drag, drop. We can get rid of the label and slide you up here.
This intermediary form isn't bound to anything. It's just a menu for this. There's no data in it. If you click up here, there's nothing in the record source. We'll slide you down a bit.
Save this. We can close this now and open it back up again. That's all you have over there.
Now we have to have a way to bind this to the other guy. How are we going to do that? Let's think about that for a minute. I need to have this guy talk to this guy without any programming, without any VBA code. That was the assignment – no VBA.
Let's bring over the other subform. We've got our customer list form right here. Click, drag, drop – there's the other one. Slide you up, drop this down, maybe slide this across. If you're going to take the time to build it, you might as well take the time to make it look good.
What do we have? I'm seeing all the starships, and I'm seeing all of the people in my database – all of them. This we can get filtered if we had this inside of a parent form, like this one was. Unfortunately, you can't directly connect two subforms together, but we can connect this subform to this one and this subform to this one as well.
So what we're going to do is drop a little text box here. This text box is going to be equal to whatever ID the user clicks on in here.
How do we do that? Go to design view. Grab a text box, drop it right there. Delete the label because we're going to hide this guy when we're done with it.
Click on it, bring up the properties. Change its name to StarshipID, and its control source is going to be whatever the StarshipID is on this subform.
How do you say that? It's going to be equal to StarshipF (that's the name of the subform) .Form!StarshipID. That's how you refer to that field. It's saying: this is the Starship form, the Starship subform. Go to this guy, then go to its form stuff, and find the StarshipID field.
If this is confusing to you, I've got a whole separate video on how to get a value from another form that's open. Go watch this. It'll explain it in more detail. Essentially, here's some of the syntax: Forms!FormName!FieldName, or if you're getting a field from a subform: Forms!ParentFormName!SubformName.Form!FieldName. I didn't make this stuff up. I didn't develop this. This is Microsoft. I just teach you how to use it.
StarshipF.Form!StarshipID – hit OK. This guy is now bound to whatever is selected in here.
Save it. Close it. Open it up again and look – there's a 1 in there and there's a 1 up here. If I click on 3 – boom, that's the guy – that's 3 in there. 4, 5. That changes because this text box is bound to whatever field is selected in there.
Now that guy has a value, we have something to bind to this guy using the link master and child fields properties. So when that changes, this can change. Check it out. Link master fields – you can use this little thing to hit the button, but I'm just going to type in StarshipID and StarshipID. The field doesn't have to be on here, as long as it's in the record set under the form – in other words, the table or query that it's bound to.
Save it, close it, and then open it up. Look at that. Enterprise D. Voyager. That is pretty cool. Now you can browse all your starships on the left and your people in the starships on the right.
This can be a blessing and a curse, because this is live data. Someone could come in here and make changes, and you might not want that. Or they might add people in here who are already in the table and vice versa.
You can modify your Allow Edits, Allow Additions, and Allow Deletions properties so that people can't make changes here if you just want them to browse through it. I like to do this: I can pick a starship or product category or whatever you happen to want. I know it's starships – I'm just trying to keep the lessons fun. I am a Star Trek nerd.
If you had a list of product categories and products, or employees and their timesheets, or customers and orders – whatever it is – you can pick a starship, pick a person, open it up, go right to the record. That kind of stuff.
You might not want people modifying stuff here, so you can lock these things all down. Here's a video, for example, that explains how to do all that – Allow Edits, Deletes, and Additions – you can turn all that off so people can't mess with those records. Unless you want them to, you might want to let them add stuff. That's up to you.
That's why I teach you how to do this stuff – so you can do whatever you want to do. I just show you how the different Lego pieces fit together. Your job is to build your little toys.
There you go. That is your fast tip – I don't know, 17 minutes long, that's not exactly fast, but that's pretty cool.
The problem with a lot of these Fast Tips is that the fast tip itself I show you in a minute or two, but it's all the build up to get there. I had to build a starship form and the other table and the combo box. I could have just picked another database, but where's the fun in that? I like doing this. I have fun doing this stuff.
There is your fast tip for today. I hope you learned something and had some fun, and I'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 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 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 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 – I also teach Word, Excel, Visual Basic, and more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will 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.
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 objective of the video tutorial?
A. To show how to use VBA programming to manage subforms in Access
B. To synchronize multiple related subforms in Microsoft Access without VBA
C. To set up security permissions in Access
D. To import external data into Access
Q2. Which relationship type is primarily used in the example shown in the video?
A. Many-to-many relationship
B. One-to-many relationship
C. One-to-one relationship
D. Self-join relationship
Q3. What is a prerequisite concept that viewers should understand before proceeding with the tutorial?
A. How to write SQL queries
B. One-to-many relationships in Access
C. How to create switchboards
D. Creating macros
Q4. Why should you avoid using the field name "name" in an Access table?
A. It is case-sensitive
B. It is a reserved word in Access
C. It is too generic
D. It requires special formatting
Q5. What type of field is used as a foreign key to relate the crew (customer) table to the Starship table?
A. Text field
B. Short text field
C. Number field (Long Integer)
D. Date field
Q6. What is the function of the combo box added to the customer form?
A. To filter customer records
B. To select which starship a crew member is assigned to
C. To enter customer names
D. To display the customer's age
Q7. Why would you use a continuous form for displaying Starship records?
A. To show only one record at a time
B. To allow data entry directly into the form
C. To display multiple records in a list
D. To create a summary report
Q8. In the main synchronization technique, what enables the subform on the right to display only the crew of the selected starship?
A. Query filters
B. VBA code
C. The Link Master Fields and Link Child Fields properties
D. Input masks
Q9. If a crew member could be assigned to more than one Starship, what type of relationship would be required?
A. One-to-many
B. Many-to-many
C. One-to-one
D. None
Q10. What is a recommended way to prevent users from editing data in the synchronized subforms if you prefer them to view only?
A. Lock the entire database
B. Hide the subforms
C. Adjust the Allow Edits, Allow Additions, and Allow Deletions properties
D. Remove the forms altogether
Q11. What does the text box added to the main form (bound to the starship subform) accomplish?
A. It lets the user enter a new starship manually
B. It synchronizes the selected StarshipID for filtering crew in the subform
C. It displays error messages
D. It calculates the total number of crew
Q12. How is the value from a subform used as a filter in this example?
A. Through a macro attached to the subform
B. By using a control source formula that references the selected value in the subform
C. With a series of update queries
D. By hardcoding the value
Q13. If users want to reference a field from a subform in another control, which syntax should they use?
A. [MainForm].[Subform].FieldName
B. Forms!ParentFormName!SubformName.Form!FieldName
C. =Subform.FieldName
D. MainForm!Subform_Form!FieldName
Q14. What is the purpose of binding the crew list subform's Link Master Field to the StarshipID text box?
A. To allow users to sort the crew list
B. To ensure the crew list updates based on the selected starship
C. To apply a format rule
D. To set default values
Q15. Which of the following is NOT a possible scenario where parent-child forms like the example could be used?
A. Categories and products
B. Employees and time cards
C. Customers and orders
D. Sorting data alphabetically
Answers: 1-B; 2-B; 3-B; 4-B; 5-C; 6-B; 7-C; 8-C; 9-B; 10-C; 11-B; 12-B; 13-B; 14-B; 15-D
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 shows how to synchronize multiple related subforms in Microsoft Access without using any VBA code. The technique I discuss is useful for any parent-child relationship in your Access databases, such as Starships and Crew, Categories and Products, Customers and Orders, Employees and Time Cards, and more. The goal is to build a user interface where selecting a parent record, like a specific Starship, displays its related child records, such as the assigned crew, in a separate subform.
Before starting this lesson, you should be comfortable with several foundational topics. Make sure you understand one-to-many relationships in Access. You should also be familiar with relational combo boxes and have watched my video on subforms to see how they work. For extra help, watch the tutorial on link master and child fields properties, as it explains how forms are connected using these settings. All these prerequisite lessons are free and available on my website and YouTube channel.
To demonstrate the process, I begin with the TechHelp free template database, which is available for download on my site. First, I set up a parent table for Starships, giving it an autonumber StarshipID and a text field for the Starship name. I avoid naming any field just "Name" because "Name" is a reserved word in Access, and using reserved words can lead to problems. Additional data like notes or Captain could be included if desired, but for demonstration, StarshipID and StarshipName are sufficient.
Next, to associate crew with Starships, I add a StarshipID field (a long integer, serving as a foreign key) to the crew (customers) table. I keep all ID fields grouped together, but that's just a personal habit. In real-life scenarios, if someone needed to be assigned to multiple parent items (like a crew member serving on multiple Starships), that would be a many-to-many relationship. For this tutorial, we only need a simple one-to-many setup: one crew member belongs to one Starship.
Once the data structure is established, I add a combo box to the crew form, allowing the assignment of each crew member to a Starship. This combo box uses the wizard to pull values from the Starship table and stores the selected StarshipID in each crew record. As I work, I assign several known Star Trek characters to their respective ships to populate the database.
Moving on, I create a single form for the Starship table. I format it, bind it to the Starship table, and drop in the appropriate fields. This primary Starship form will become the basis for the parent side of our interface.
With both forms set, I then embed the crew form as a subform inside the Starship form. This creates a situation where, when viewing a particular Starship, you see all its crew members. Access makes this link automatically if both forms include the StarshipID, thanks to the link master and child fields properties.
However, the classic parent/subform view is not always the ideal workflow. Suppose you want an interface that displays a list of all Starships on the left, and when clicking one, shows its related crew on the right. To achieve this, I prepare a main menu form, clear out the default elements, and use it as a shell for our group display.
On the left side, I insert a Starship form in continuous form view, which shows all records at once. If you want more details on continuous forms, there is a separate tutorial covering them. The main menu (the parent form here) remains unbound, purely serving as a container.
Next, I bring in the crew (customer) list form, placing it on the right as another subform. At this stage, all Starships and all crew members appear independently, because Access does not directly link two subforms together based solely on user selection. The challenge is synchronizing the display of the right-side subform (crew) based on which Starship is selected on the left.
To bridge this gap, I add a hidden text box to the main form. This text box is set up so that its value reflects the StarshipID of the currently selected record in the Starship subform. The syntax to do this involves referencing the subform and its current StarshipID. If you are unfamiliar with this approach, I have separate videos on referencing values across open forms and subforms.
Once the link is established, I set the crew subform to use this hidden text box for its Link Master Fields property, linking it to the StarshipID field in its record source. Now, selecting a Starship in the left subform updates the value in the text box, which in turn filters the crew subform to show only the assigned crew. The result is an intuitive two-pane interface letting you quickly browse parent and child data.
A word of caution: because these subforms display live data, users could potentially edit or add records. Depending on your needs, you may wish to adjust the Allow Edits, Allow Additions, and Allow Deletions properties to lock things down and prevent unwanted changes. There is a video covering those settings as well if you want more control.
This same technique works well for many other situations, such as displaying categories and their products, employees and their time cards, and so on. You can customize the forms to suit your workflow and prevent accidental data modification as needed.
That wraps up this Fast Tips lesson. Even though the video ran longer than a couple of minutes, all the necessary groundwork was covered to fully explain the process. Working through examples like this is part of the fun and helps you learn not just shortcuts, but how the components of Access fit together.
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 a Starship parent table with appropriate fields
Assigning crew members to starships using a foreign key
Adding a StarshipID combo box to the crew form
Populating starship and crew sample data
Building a single Starship form bound to the Starship table
Converting a single form to a continuous form for starship listing
Inserting a continuous subform to act as a parent record selector
Adding and configuring a customer/crew subform to display child records
Using a hidden text box to sync selected parent and child subforms
Binding subforms with Link Master Fields and Link Child Fields
Synchronizing two subforms without VBA code
Adjusting form properties to restrict edits, additions, or deletions
Arranging and formatting forms for a synchronized parent-child display |
