Fitness 17
By Richard Rost
3 months ago
Making a Subform in a Header Control the Parent Form In this Microsoft Access tutorial I will show you how to create a subform in a form's header that controls the parent form, set up consistent form layouts, adjust background colors for clarity, use conditional formatting for field highlighting, and address issues with editing aggregate query results. This is part 17. MembersThere is no extended cut, but here is the file download: 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!
PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, fitness database, subform in header, continuous form subform, aggregate query, MealListQ, MealListF, filter parent form, master child fields, on current event, conditional formatting, field has focus, color coding forms, design view, add new button, scroll buttons, database interface consistency
Subscribe to Fitness 17
Get notifications when this page is updated
Transcript
Today is part 17 of my fitness database series. If you haven't watched parts one through 16, go watch those first.
As a reminder, whether you care about tracking fitness, food, exercise, and all that stuff, that doesn't matter. I'm teaching you really cool stuff about Microsoft Access that will apply to any database. So let's get into it.
I'm very happy with how this turned out - one form. We got it like this. Now, in the meal list, we've got the same situation going on. I want to have this one form with the list of meals up top and then the details down below. The problem is this is going to be a little more complicated because this is already a continuous form. Having this in the footer of a continuous form with a subform is going to be tricky. I think it would be easier to go the other way around and to take this and make this a subform in this guy's header.
Also, this stuff isn't editable over here. The records from this form are from an aggregate query, MealListQ, which is here. You can't edit this no matter what you do. Even if I join this to another query, as soon as an aggregate query is involved, you lose the ability to edit it, add new records, all that stuff.
So we could take this and make this a header subform up top here, basically make it look like this. That way, they both behave the same way. I want the whole database to behave consistently - every form should behave similarly, so there aren't two different interfaces. But we're going to have to make this a subform inside of this guy's header.
It's a little trickier. I don't think I've ever done this before in one of my videos, but we're going to do it today.
Let's close this. Now, let's go to design view over here. There's a header section right there, but it's shrunk up, so let's open that up so we have some room to work with here. I'm going to take that - what is it? - the MealListF. We're going to drag that and drop it up in here. We're going to get rid of that label, then we're going to resize it. I'm going to leave a little room just for now so we can see it, but later on, I'm going to make it take the whole space, with no room around it, just so we can see exactly what's going on here. I'm going to make this a little bit bigger because we can, just like so. This is one label, so we'll just put a bunch of spaces in there.
All right, let's save that, close it, and let's see what we got.
Oh, this button is opening up this guy, which is still opening up that guy. But you can see back here what's going on. I'm only seeing one record. We'll address that in just a minute.
First, let's fix that button, the meal list button. Over here, we don't need you to open up the meal list. We just need to open up the MealF. Save it. I'm going to close this.
Sometimes when I'm working on stuff off camera, I use this project explorer. I don't use it as much in my videos. Okay, so that fixes that. That'll open up the right form now. Let's slide it over here.
Now we can get rid of the form current event in this guy. Click on it once to get the subform, then click on it another time to get this form's properties. Open that up. You should see the form properties over here. We don't need this on current event. Actually, we do the on current event, but we're going to do something different.
Instead of opening up this other form, get rid of that. What we're going to do is say, based on whichever record you click on in here, I want to filter the parent form to show that record. So if I click on Rix Coffee or Rix Breakfast, I want to show that down here. So it's kind of going backwards, because normally the parent controls what's shown in the subform. We're going the other way around.
In fact, we don't want the parent to control the subform. That's why we're only seeing one record. So let's do this first - let's go up here.
I'm jumping around a little bit. Let's go up here. I get excited about this stuff. Click on the subform object and go to data and see here, we have the master and link child fields. Get rid of those. Because this is actually going to control the parent, not the other way around, but this parent still controls this subform, because this has the details for the meal. This is the meal list, that's going to control what parent record shows, and the parent record is still going to be linked to this subform.
So let's go back into here. Back into the events. Actually, while I'm looking at it here, you can leave that there. I was going to get rid of the order by - leave that. Go to the events and go back to the on current event.
When I click on a meal in this subform, I want to filter the parent form to show just that meal. You can search for it if you want to do it that way, but I think filtering is just fine in this case. Maybe we'll change it later.
So how's this going to look? We have to go: parent.filter equals show me the current meal. So MealID equals the MealID of the record I'm on now in the subform. Then parent.filteron equals true.
Save it. Debug, compile once in a while. Let's close this, close it, close it, close it, open it.
Okay, here are all my meals. Click on this one - look at that! See? Click on that. Isn't that cute? Click on this one. See, this is a subform in the header that controls the main parent form, which is basically just this: rice, fish, and veggies, just for coffee, and these are the food items inside this meal. So it's behaving very similarly to this guy. I'll move over here now. You click on this and you see the details in the footer. We don't want to confuse our users, so this kind of behaves the same way.
Now the next thing I want to do is the colors here are very confusing. You get dark blue, light blue, light blue, dark blue. I'm going to make it so the entire top of this form is light blue, and the entire bottom is dark blue, so it separates them even more.
Same thing with the food. Let's make the entire top a lighter blue, and the entire bottom like it is - darker green. All right, so let's come into here. Now, this is one of the reasons why I left some space in here.
What I like to do is - let's make the top light blue. So I'm going to grab this color in here. Open up this property section here. I'm going to copy it right out of here. It's this color right there - that's the light blue. Copy it there. Let's put it up here, let's put it in here, and it's already in here. I think that takes care of that whole section.
Now for down below, the detail section, I want all dark blue. So I want this shade of blue that's in here, which is that one. That's going to go in here and out here. I think I got it.
Save, close, open.
Much better. So this much more clearly shows that this is the meal list, this is the meal down here below.
Let's do the same thing with the food form. It's not quite so bad. I think really all we have to do is bring this light color up top. If memory serves, I used - yeah, I used these stupid theme colors. I hate the theme colors. Let's pick a different green from the standard colors. Let's go with this guy. Let's see what that looks like. That's okay. Let's pick something slightly different. More colors. Let's go a little bit this way and a little bit that way. Yeah, that looks better. Copy that and we'll go down here with it.
Up here, I'm going to do a lighter green. So I'm still going to paste that in so I get a non-theme color, and then I'm going to go back to this palette guy and just slide that up. Save it, close it. No, I have to get in here too. Design, paste it. No wait, I got this - copy, copy it, paste it. There we go. Save it, close it, open it.
That looks much better. There's my food items, and there's my meal list. Looking good.
I also want to maintain the same standard of having that yellow highlight whenever we click. We didn't put that in over here, so it's little things like that we've got to take care of too.
Right click, design view. That is done with conditional formatting.
We'll start up here. Format, conditional formatting. The rule: field has focus, and that yellow. Okay.
Now this guy is going to be the same. We have to make sure we include the bold. So format, conditional formatting, new rule, field has focus, and go to this and make sure you turn that on too. Otherwise you'll lose your bold.
Down here, this one and this one. You can select them both together. Same thing.
Da da da. Da da da. Da da da. Da da da. Blah blah blah. Blah blah. Alex Lifeson's Hall of Fame speech - blah blah blah. Blah blah blah blah. Blah blah blah. Epic.
He'll list. Why save it? Save it, close it, open it.
Now you can clearly tell where you're sitting. Beautiful. I like it.
Now that I'm looking at these two side by side, this one down here allows you to use these buttons to scroll. This one doesn't because we're filtering. Maybe we'll change that. We'll do a seek and we'll find the right item so you can still scroll back and forth this way.
We need a way to add, because right now if we hit the add new button, we get an error message. So we've got to fix that too. We'll start there in tomorrow's video.
Tune in tomorrow, same bat time, same bat channel. I just read something interesting about how they tried many different voiceover actors to do that narration for the original 60s Batman show, and they couldn't find the right guy. So one of the producers just stepped in and did it and he ended up doing it for the whole run of the show. That's pretty cool.
Anyways, random thoughts.
Members, you can watch right now because I have to do a bunch of recording today, so I'm probably going to do at least three or four of these.
But yeah, that's going to do it for part 17. I hope you learned something. That was a pretty cool trick. I don't think I've ever done that before in a video where I made a subform in a header that controlled the body of the parent form that had a subform in it.
Pretty cool stuff. The things you can do with Access.
Anyways, live long and prosper folks. I'll see you tomorrow.
TOPICS:
Embedding a subform inside a form header
Configuring subform layout and resizing
Removing label objects from subforms
Fixing command buttons to open correct forms
Filtering the parent form from a header subform
Removing master and child link fields from subform setup
Using the subform On Current event to filter the parent form
Writing VBA for parent filter and filteron properties
Synchronizing displays between meal list and details
Adjusting form color schemes for clarity
Copying and applying custom colors in form sections
Using standard and custom non-theme colors
Implementing conditional formatting for focused fields
Retaining bold formatting with conditional formatting
Applying conditional formatting across multiple controls
COMMERCIAL:
In today's video, we're continuing with part 17 of the fitness database series. You will learn how to create a form layout with a meal list as a subform in the header, allowing it to control the main parent form below. I will show you how to set up color schemes for better visual separation, use conditional formatting to highlight the focused field, and handle issues that come with working with aggregate queries and subforms. You will see how to keep your forms consistent and user-friendly, and I will talk about some of the problems that come up when filtering records versus scrolling through them. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.
Quiz
Q1. Why was it necessary to change the structure of the meal list form in the video?
A. To add the ability to print reports directly from the form
B. To allow for consistent behavior and appearance across forms
C. To increase the maximum number of meals stored
D. To enable editing of aggregate query data directly
Q2. Why can't records from the MealListQ query be edited directly?
A. The query uses aggregate functions, which make records read-only
B. The table is locked by another user
C. The form's read-only property is set to Yes
D. Linked child fields prevent editing
Q3. What is the main reason for using a subform in the header of the main form?
A. To simplify navigation between records
B. To allow the subform to control which parent record is displayed
C. To make printing easier
D. To increase database security
Q4. What setting needed to be removed from the subform to allow the child (subform) to control the parent?
A. Control source from the form
B. Master and child link fields
C. Conditional formatting rules
D. Record source of the parent form
Q5. What action does the on current event in the subform now perform?
A. Refreshes the subform with random records
B. Filters the parent form based on the selected meal
C. Resets the form colors to default
D. Adds a new meal record automatically
Q6. Why was it necessary to adjust the coloring of the form sections?
A. To match the corporate branding guidelines
B. To visually distinguish between the meal list (top) and meal details (bottom)
C. To make the database accessible for colorblind users
D. To remove all color so the form prints better
Q7. How was the color scheme applied to the form and subform?
A. By choosing only theme colors provided by Access
B. By copying the property from one section and applying it to others for consistency
C. By writing VBA code to set colors at runtime
D. By importing a custom CSS file
Q8. What was used to create a yellow highlight when a field had focus?
A. Changing the border style in design view
B. Applying conditional formatting with a rule for field focus
C. Manually setting the background color for each field
D. Using a macro to change field color
Q9. What limitation arose as a result of using filtering to display the current record?
A. The add new button stopped working
B. The user could no longer scroll through records with navigation buttons
C. The form could only display a single record at a time
D. The subform would not refresh automatically
Q10. What was planned for a future video due to limitations in the current design?
A. Adding printing functionality to the form
B. Implementing search and seek to maintain record navigation
C. Creating reports for data analysis
D. Exporting data to Excel
Answers: 1-B; 2-A; 3-B; 4-B; 5-B; 6-B; 7-B; 8-B; 9-B; 10-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 TechHelp tutorial from Access Learning Zone continues with part 17 of my fitness database series. If you are new to this series or have not followed along with the earlier lessons, I recommend checking out parts one through sixteen first.
Even if tracking fitness and food is not of particular interest to you, the techniques I am demonstrating with Microsoft Access are broadly useful and can be adapted to any database project. The goal is to teach powerful skills applicable to many contexts.
At this point in our series, I am quite pleased with our progress. We have built a unified form where users can interact with their data efficiently. Moving to the meal list, I noticed that it presents a new challenge. I want the interface to allow users to see a list of meals across the top area of the form, with details for the selected meal shown beneath. However, implementing this is trickier than it first appears because the meal list itself is set up as a continuous form. Adding a subform to the footer of a continuous form is not straightforward in Access.
What might work better is the opposite arrangement: insert our meal list as a subform inside the header of another form. This provides a consistent look and feel across different forms in the database and prevents multiple, conflicting interfaces. Having predictable and consistent layouts is very important for user experience.
There's another complexity. The meal list is based on an aggregate query, MealListQ. In Access, you cannot edit records that come from aggregate queries. That means fields shown from this query cannot be modified directly in the form, nor can you add new records from it.
The solution is to place the MealListF subform into the header of the parent form. Doing this for the first time in a video tutorial is exciting, as it is a bit unorthodox, but entirely doable.
So here's how I approach this: I switch to design view in the parent form, expand the header area to give us some workspace, and then drag MealListF as a subform into that header. I get rid of any unnecessary label controls, resize everything so the subform fits neatly, and prepare it for our interface.
After saving and reviewing the changes, I noticed the "meal list" button still tries to open the original form. That is quickly fixed to point only to the new MealF. Cleaning up some code, I remove or update the form's On Current event to direct the correct behavior.
Originally, the subform and parent form were linked using the Master and Child fields, which is standard for letting the parent control the subform. In this scenario, I actually want interaction in the subform to drive the main form instead. To enable this, I remove the link in the subform's data properties.
From there, the On Current event becomes key. Instead of opening a separate form when a record is clicked, I set the filter property on the parent form to match the currently selected MealID in the meal list subform. The filter is activated immediately, so as you select different items in the meal list, the details below update accordingly. This reverses the usual parent-subform relationship and makes the interface much more intuitive and responsive.
Upon reviewing in form view, clicking different meals in the header subform instantly updates the details area below. The database now behaves consistently: just like in other areas, user actions in one section immediately control another, which reduces user confusion.
Next, I turned my attention to the form's colors, which were visually confusing. To improve clarity, I unified the meal list area at the top using a consistent light blue, and switched the details area at the bottom to a solid dark blue. The same process was applied to the food form, with light and dark green tones used to match the intended theme and clearly divide sections. I avoided using Microsoft Access's theme colors, preferring to pick standard or custom shades for a more polished look.
To improve user feedback and maintain consistency, I also revisited conditional formatting for the fields. Adding a yellow highlight on fields with focus helps users easily see where they are in the form. This is set up using the conditional formatting rules for both the meal and food forms, making sure they remain bold when active.
Finally, while reviewing side by side, I noticed that the navigation differs between the two interfaces. The filtered approach in the new design does not allow for quick scrolling through records using navigation buttons, unlike the other forms. This could be improved by implementing a search or seek feature, making navigation even easier for users without losing the visual feedback.
There is also a minor issue with the "add new" button triggering an error, which will be addressed in the next lesson.
To wrap things up, the work in this episode demonstrates a technique that, to my knowledge, I have not shown before: using a subform inside a header to let a continuous list control a parent form's main section. It opens up interesting possibilities for Access form design.
If you want a complete video tutorial with all the step-by-step instructions on everything discussed here, be sure to visit my website at the link below. Live long and prosper, my friends.
Topic List
Embedding a subform inside a form header
Configuring subform layout and resizing
Removing label objects from subforms
Fixing command buttons to open correct forms
Filtering the parent form from a header subform
Removing master and child link fields from subform setup
Using the subform On Current event to filter the parent form
Writing VBA for parent filter and filteron properties
Synchronizing displays between meal list and details
Adjusting form color schemes for clarity
Copying and applying custom colors in form sections
Using standard and custom non-theme colors
Implementing conditional formatting for focused fields
Retaining bold formatting with conditional formatting
Applying conditional formatting across multiple controls
|
