Students & Parents 4
By Richard Rost
17 months ago
Relate Students & Parents in MS Access Database Part 4 In this Microsoft Access tutorial, I will show you how to efficiently navigate between student and parent records using buttons and VBA code. Whether you prefer a no-programming solution or want to learn a bit of VBA, you'll learn two methods to link your student and parent forms seamlessly. This is part 4. MembersThere is no extended cut, but here is the database 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!
PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, student-parent relationships, Access database tutorial, linking records, subform navigation, open form button, VBA double-click event, event handling, form design, Access macro, table relationships, string concatenation, Access reports, open specific record, parent-student database, Access database programming
Transcript
Today we are continuing with our students and parents database. Got a nice little project going on. If you haven't watched parts 1 through 3, go watch those first, then come on back.
Alright, whenever we build a little project like this, I'm still fighting a cold folks. I apologize. I'm all hyped up on Mountain Dew and cough syrup. I can't even talk right.
Anyway, whenever we build a project like this, we've got students, we've got the parents, and people always ask me, well, how do I jump back and forth between these? I've got Bobby Kirk up here. Well, now I want to go look at Mr. Spock's record. So it would be nice if I could click a button and just open up Mr. Spock. I could see Mr. Spock's kids. I could jump between the kids. So how do we go between these things automatically?
I'm going to show you two different ways to do it. First, I'm going to show you the non-programming way because I know a lot of you out there, you want to build great databases, but you don't want to learn how to be a programmer, that's OK. Then, after that, I'm going to show you a little VBA so you can do it with just one line of code.
Let's do the non-programmer way first for all of you who have no desire to learn how to program. We'll put a button on the bottom of the subform.
These are students in here, to jump to the student form for that particular student. All right, so let's start with the student sub-F. All right, design view. We're going to put that in the form footer down here on the bottom. Come here. Give me the bottom. Right there. There it is. You got to get the bottom.
All right. I was trying to grab it over here where it didn't exist. If you don't have a form footer, right-click on the detail section and turn on the form header footer, not the page header footer. We don't use the page header footer for forms because the page header footer is for printing and we don't print forms, we print reports.
Anyways, down here, let's drop a button, form design. I'm going to grab me a button, drop it down here, Form Operations, open a form, next, what form are we opening? These are students, so I'm going to open up the student form, where are you? Student form. Next, I want to open the form and find specific data to display.
All right, I want to open up whatever student I happen to be on. Next, now, here's how you're going to match it up. Match up the student combo box, which is that field there, with the student ID on the form you're opening, and then make sure you click this button right there. Click. See that? So you get matching fields.
Okay, next, what button do you want? Open student. Next, give it a meaningful name, Open Student Button, BTN, and then finish. And here's your Open Student Button. Slide it right up under here. Make it nice and pretty. Close that up. Make it however wide you want it.
Alright. Save it. Close it. Now, if I open up the parent form, you can see right down here I've got Open Student. It'll open up whichever student you're on. So if I happen to click on Sue Kirk and hit Open Student, bam, there you go. There's Sue Kirk's record. I'll slide this down here. Keep the students down below. See?
I click on Bobby Kirk, Open Student, now it jumps to Bobby Kirk's record. All right, let's do the same thing for the parents. So we'll go to the Parents, sub F, Design View. And what I was trying to do before was grab the bottom of the form footer, but it doesn't actually exist until you're over here, see? Because right there, that's where the edge of the form is. You've got to be right there.
All right. Ready? Let's do it again. Same thing with parents. Drop a button, form operations, open a form. Next, what are we opening up? A parent form. Next, find specific data. Next, parent combo is linked to parent ID. Don't forget to click that button. Next, open parent. Next, open parent button, and finish. There you go.
Parent, slide it up underneath there. Make it all nice and purdy. Close it. Open up the parent form, go to Sue Kirk, there you go. Alright, let's go to Carol Marcus, and I can jump back and forth to Bobby, right? Mr. Spock. I think it's up back and forth between them.
Now that's how you can do it with a button. I personally don't like a button. It's not that intuitive. Yeah, sure, it's a teaching moment. You can just teach your users how to do this. Right, click on the student first, hit open student. I like double clicking myself.
Now, to do a double-click event requires a little bit of programming. Now, if you've never done any VBA programming before, go watch this video. It's about 20 minutes long. It'll teach you everything you need to know to get started, but I'm going to show you what you need to know right now.
Alright, so instead of hitting a button, I want to be able to double-click on the student to do the same thing. So let's go back to those subforms. Let's go to the parent subform, design view. Now this thing here, when you create a button, it creates something called an embedded macro.
If you right-click on that and go to build event, the build event is what happens when you click on that. It's an open form command, and it's opening the parent form where the parent ID equals parent combo. Okay? We can do something very similar with a little bit of VBA code, and I think it's actually easier than dealing with that embedded macro.
So click on the object, click on the combo box here, all right? Double-click on it to bring up the property sheet, or you can click on the property sheet up here wherever. Okay?
Now, go to events. There's a bunch of different events. They all kick off at different times. We're looking for the on double-click event right there. You're gonna click on the dot dot dot button. Now that might bring up another little menu. Excuse me, that says what builder would you like?
Pick the code builder, code builder. I have mine turned off and I show you how to do that in that VBA video that I mentioned a minute ago. Now, that's gonna bring up the VBA editor which looks like this. And right in here inside this private sub between the private sub and the end sub, this is what happens when you double-click on the parent combo box.
And we need one line of code right here. We want to open up the parent form. So it's docmd.openform. What's the form name? Parent F. Now there's some options we don't need here so just go comma, comma, comma. It's not like the chameleon. Okay, I'm sick. Leave me alone.
All right. Now, see down here where it says where condition? This is the option we're on now, the where condition. I want to open up the parent form where the parent ID equals whatever's in that parent combo. We're going to use a little string concatenation there. That's gonna take the value that's in parent combo, could be a 6, could be a 13.
It's gonna add it to this string, so it's gonna be parent ID equals 13. That's our where condition and it's gonna send it to the open form command. It's gonna open up the parent form where the parent ID on that form equals the parent combo. Okay, you with me? Save that. Come back over here. Close it. Let's open it back up again.
Now if I double-click on Bobby Kirk, whoops, I'm on the wrong one. I'm opening up the parent form. So I want to be on the student form. There we go. If I double-click on Carol Marcus, there you go. See? It opened up Carol Marcus. I forgot I was on this thing over here. The parent subform is in the student form and vice versa.
What I like to do to make sure that I know there's an event there is I like to take this guy here and make it a different color. I use a lot I use like a light blue. So I'm gonna come in here and maybe pick that blue. That just tells the user visually. Hey, you can double-click on this field, and something's gonna happen.
So now when I open it up, excuse me, now when I open it up I can see these are blue. Oh, what happens if I double-click on Jim Kirk? Oh, there's Jim Kirk. Okay. See that? And now we can do the same thing in the student subform. Right? Student subform, right-click, design view, pick this guy, and I think I got my colors wrong in the footer there.
Yeah, this should be blue. Come up here and go with that. Yeah, there we go. All right, click on you. In fact, let's use the blue first right there. All right, events on double-click, dot, dot, dot button, code builder, new command, open form, student f, where the student ID equals the student combo.
Alright, easy enough. Save it. Once in a while, if you're doing some programming, throw in a debug compile. The compiler runs through all your code to make sure everything is legit.
Alright, close it, close it, open it. Let's go to Carol Marcus. Let's go back to Sue Kirk. Let's go back to Jim Kirk. Let's go to Sue Kirk again. Whatever. Bobby Kirk.
Alright. You can see how you can jump back and forth between those records with your little events or with your little buttons or with whatever you want.
Now, I've already gotten some feedback on this series so far. Today is, well, it's Thursday the 11th as I'm recording this, but members can watch the videos as soon as they're posted to my website or YouTube.
I've already gotten some good feedback on what you guys want to see. A lot of you have said, a couple of you have said, well a few of you have said...a lot, that, hey, Rick, you don't spend enough time with reporting. So we're going to make some reports.
We're going to make a call list. We're going to take each student, and we're going to start that in the next video which will be part 5. But that's going to be the end of part 4.
That's your TechHelp video for today. I hope you learned something my friends. Live long and prosper. I'll see you tomorrow for part 5. A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They are manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
TOPICS:
Students and Parents Database overview
Non-programming way to jump between records
Adding a button to open a Student form
Setting the button's action to open specific data
Customizing the button's appearance and placement
Testing the button to open Student records
Adding a button to open a Parent form
Setting the button's action for the Parent form
Customizing the Parent button and placement
Testing the button to open Parent records
Introduction to using VBA for double-click events
Creating double-click events in subforms
Editing embedded macros in forms
Using VBA code to open forms based on combo box values
Changing combo box appearance to indicate double-click events
Testing double-click events in Student and Parent forms
COMMERCIAL:
In today's video from Access Learning Zone, we're tackling the continued development of our students and parents database. I'll guide you through setting up navigation buttons to jump between student and parent records effortlessly. We start with a button method for those who prefer not to code, placing buttons on forms to open specific records. Then, for those interested in a little VBA, I'll show you how to accomplish the same thing with a simple double-click event. By the end, you'll have two ways to navigate your database seamlessly. 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. What is the primary purpose of creating a button on the subform in the tutorial?
A. To print reports directly from the form
B. To facilitate data entry for new records
C. To open a form and find specific data related to the selected record
D. To change the theme and design of the database
Q2. Where should you place the button in the subform to open another form?
A. In the form header
B. In the detail section
C. In the form footer
D. In the page header
Q3. What is the correct form operation to select when setting up the button to open another form?
A. Print a form
B. Import data into a form
C. Open a form
D. Close a form
Q4. Which type of fields should be matched to open specific data in another form?
A. Combo box and text field
B. None; matching fields are not required
C. Parent combo box and student name
D. Combo box and corresponding ID field
Q5. What changes are made to the button created for students when setting it up to work with parents?
A. Different combo box and corresponding ID fields are matched
B. Button text is changed only
C. Nothing; the button remains the same
D. A new visual design is applied
Q6. For a more intuitive user interface, what alternative to buttons is suggested in the tutorial?
A. Single-click event
B. Mouse hover event
C. Data entry fields
D. Double-click event
Q7. Why does the tutorial suggest changing the color of the combo box field to blue?
A. To indicate to users that a single-click event is available
B. To improve database performance
C. To visually signal that a double-click event is attached to the field
D. To apply company branding
Q8. What VBA command is used to open a specific form in the tutorial?
A. DoCmd.CloseForm
B. DoCmd.TransferDatabase
C. DoCmd.OpenForm
D. DoCmd.RunMacro
Q9. In the VBA code, what represents the condition to match the combo box selection with the appropriate record in the new form?
A. table.filter.combo=field
B. form.recordID = value
C. query.matchID = combo
D. form ID = combo value
Q10. Why is the "debug compile" command suggested after writing VBA code?
A. To make sure the VBA editor is updated
B. To run through code and ensure there are no errors
C. To delete unnecessary lines of code
D. To format the VBA code for readability
Answers: 1-C; 2-C; 3-C; 4-D; 5-A; 6-D; 7-C; 8-C; 9-D; 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 focuses on the continued development of our students and parents database. If you are just joining in, I recommend starting with parts 1 through 3 before proceeding with today's lesson to get all the background and context.
When working with a database that includes both students and parents, a frequent question comes up: how can we quickly move between related records? For example, when looking at a particular student, you might want to immediately jump to their parent's record, or vice versa. The ability to navigate back and forth like this is a real convenience in managing individual records, especially as your database grows.
Today, I will show you two methods for adding this kind of navigation: one that requires no programming at all, and a second that uses a bit of VBA for those who are comfortable with a small amount of coding. We will begin with the solution for non-programmers.
The first method involves adding buttons to your forms so users can simply click to open the corresponding record. For example, you can place a button at the bottom of the student subform, which, when pressed, opens the student's individual form. To do this, switch to design view for your subform and look for the form footer section at the bottom. If you do not see it, right-click the detail section and enable the form header/footer – be sure to pick the correct option, since you do not want the page header/footer which is used only for printing.
Once the footer is visible, use the form design tools to insert a button in that area. Assign this button to perform a form operation, specifically to open a form. Have it target your student form, and set it to find specific data so that when the button is clicked, it opens the form to the currently selected student. Link the relevant fields - typically the combo box used to pick the student and the student ID field on the target form. Give the button a clear label such as "Open Student," assign it a helpful name for identification, and complete the process. You can adjust and resize the button as needed.
Now, after saving your changes and reopening the form, you will see the "Open Student" button. When you select a student and click this button, it will open their detailed record. You can repeat a similar process for the parent subform, this time setting the button to open the parent form and linking to the parent ID.
This straightforward approach using buttons is very accessible for beginners or those who do not want to get involved with VBA. However, I find that buttons are not always the most intuitive navigation method for users. Personally, I prefer using a double-click action on the relevant field, which is not only faster but mimics the behavior many users expect from other software.
For this, we will need to use a little bit of VBA. If you have never worked with VBA before, I suggest reviewing my introductory video on the subject – it will get you up to speed quickly. For the double-click method, we are going to create an event handler for the combo box that lists the related records.
Go back to your subform in design view and select the combo box you want to use for record selection. In the property sheet, go to the events tab and find the "On Dbl Click" event. Select the option to use the code builder. This opens the VBA editor, where you simply need to add a single line of code that instructs Access to open the relevant form and display only the record that matches the selected item in the combo box. You do this by providing a where condition that matches the appropriate ID field to the selected value.
This simple routine means that double-clicking on a student's name in the subform opens that student's detailed form. The same approach works for jumping from students to parents by setting up the corresponding event for the parent combo box.
I recommend adjusting the appearance of these combo boxes so users know there is something special about them, such as applying a light blue background color. This serves as a visual cue – when someone sees the blue field, they will recognize that double-clicking will perform an action.
It is good practice to periodically use the "Debug Compile" feature in the VBA editor to check your code for errors as you work.
After updating your forms with this new functionality, you will find it easy to move between related student and parent records, either via navigation buttons or by using intuitive double-click events.
Before wrapping up, I want to note that I have been receiving helpful feedback from viewers, especially regarding the need for more content on reporting. Many of you are interested in generating reports such as call lists for your students. That will be our focus in the next lesson, which will be part 5 of this series.
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
Students and Parents database navigation
Adding a button to open the Student form
Configuring button to open specific Student records
Adding a button to open the Parent form
Configuring button to open specific Parent records
Enabling form footer for form design
Matching combo box fields for record lookup
Testing button navigation between Student and Parent records
Creating double-click events using VBA
Using VBA to open forms based on combo box value
Setting up the On Double Click event in form properties
Color coding combo boxes to indicate double-click capability
Testing double-click navigation between Student and Parent forms
|
