|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
Subforms By Richard Rost 
4 years ago
How to Create a Form with a Subform in Access In this video I will explain what a Form and Subform are. I'll show you how to create a Single Master Form and a Continuous Subform to go inside it. We will track employees and their mileage reports. Janessa from Durham, North Carolina (a Gold Member) asks: I have a business where we reimburse our employees for mileage. I've been keeping track of their miles for every day in an Excel sheet, but I know there's got to be a better way to store them in my Access employee database. Can you help me? MembersMembers will see how to determine if a specific record has already been added to a subform, and if so, don't allow it to be added again. This is handy to prevent duplicate services for the same customer, or you can use it to see if a product has already been added to an invoice. We'll design a "wizard" to enter mileage where the database will ask you the date, check to see if it's been entered, and if not, ask you for the mileage.
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!
Links
Keywordsmicrosoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, Create a form that contains a subform, One-to-many form, What is the subform in Access, How do you create a subform in Access, What is main form and subform in MS Access, access subform tutorial, how to create subforms, how to use subforms, create a form with subform, Creating a Subform in Microsoft Access, main form and subform in ms access, composite key
IntroIn this video, I will show you how to create a form with a subform to display related records in Microsoft Access. We'll use a real-world example of tracking employee mileage, moving data from Excel into an Access database for improved management. I will walk you through building the necessary tables for employees and mileage, establishing table relationships, creating both single and continuous forms, and embedding a subform within a main form to see related data for each employee. I will also show how to add a total field to the subform to sum mileage for each employee.TranscriptWelcome to another TechHelp 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 create a form with a subform to show related records in your Microsoft Access databases.Today's question comes from Genessa in Durham, North Carolina, one of my Gold members. Genessa says, I have a business where we reimburse our employees for mileage. I've been keeping track of their miles for every day in an Excel sheet, but I know there's got to be a better way to store them in my Access Employee database. Can you help me? Of course, Genessa, we can do this with a couple of tables, one for employees, one for mileage. We'll relate them together, create a form for each, and then I'll show you how to display the mileage inside the employee form using something called a subform. Now, before we get started, you should understand relationships, which is very important. Watch my relationships video if you haven't already, and I want you to learn how to use continuous forms. This is where you can have multiple records on the form at a time instead of just one. Watch each of these videos. These are free. They're on my website. They're on my YouTube channel. Go watch them now and then come back. Here I am in my TechHelp Free template. This is a free database. You can download a copy from my website if you want to. You'll find a link to it down in the links section in the description below the video window. Now, in this database, I've got a customer form, and each customer can have multiple contacts. A contact is every time you talk to the customer. You want to track what was spoken about each time you have a conversation. You want to put the date and time and what was talked about. These are two related tables. They're two related forms, just like each customer has an order that can be associated with one or more orders, and each order has order details. Now, this right here is a subform. If you also look, I have a separate form that I built, which is the customers with the contacts as a subform. You can see I can have multiple contacts listed for each customer. We're going to do the same thing for you, Genessa, with each employee and their mileage. Since I already have these forms built, I already have the customer form, and I already have the contacts form. It's as easy as this. Go to design view, find out where you want to put that subform, and let's put it down here on the bottom. Have the contact form, which is over here, click and drag it and drop it. There it goes. Now you have a subform inside of your main form. If I save this and close it, then open it again, look at that. I got a subform down here. That's only showing one record because I didn't make it big enough. Let's make it bigger. Go to design view, make this guy bigger, and add more records. There's the subform object right there. See that? It comes in with a little label. We'll delete that later. Save it, close it, open it back up again. Now you can see multiple records. That's why on this other one, I had it off to the right because you can see more records. Let's see how we can do this with your database. Before we get started, if you want to see exactly how I built my database, there's the blank database template, the contact templates where you can track all the contacts for each customer, and the invoicing stuff for orders. These are all free videos again. They're on my website. You don't have to watch these now, but if you want to learn more about how I built that database, go watch these videos too. You'll find links to all of these down below in the links section. Genessa, for your database, we're going to create a table to store your employees. We'll start off with the employee ID. That'll be our auto number. Then I'll put first name and last name, and all the other fields related to your employees that you want to track, such as their address, phone number, salary, or whatever. For this database example, it doesn't matter. You can put all the supplemental fields in here that you want. Save this. This will be my EmployeeT, my employee table. Primary key defined, yes. I cover all this in my Access Beginner 1 class if you've never built tables before. Close that up. I like to put sample data in. We've got Jim Kirk, Leonard McCoy, and Will Riker. There are three employees. Save that. Now we've got to track mileage for each of those employees, so we need a second table. Create table design. MileageID is our auto number. Now we have to know who this mileage report is for, so we need an EmployeeID. That'll be a number in this table. It's an auto number in a different table, but auto numbers are just long integers, so this has to be a number of type long integer. We don't want this table assigning new employee IDs for each record, and you can only have one auto number. That's how we know who this is - that's called the foreign key. I cover that in the relationships video that you were supposed to watch. Next, I want to track the date for this mileage. Don't just call it "date." A lot of people do this. "Date" is a reserved word in Access. It's a special word. Don't use the word "date." We're going to use "MileageDate." That's going to be a date/time field. I'm going to default value that to =Date(), that's the date function. If I put that date function in the default value property down here, it says, hey, stick the current date in there. If you use "Now," it'll stick the current date and time in there, but we just want the date. Finally, "TotalMileage." This will be a number again. We can leave it as long integer. We don't reimburse for fractions of a mileage. Sorry. That's enough for now. You could put whatever else you want in here. You could put in notes, or if you reimburse everybody at a different rate, you could put what their rate is in here. This is fine for me, and all my employees get reimbursed the same. We're going to save this now as MileageT for mileage table. Let's go ahead and put some data in here. I'm going to want to see the employees just so I can see what their numbers are. Let's open up the mileage table. I always find it's easier to build the database if you've got some sample dummy data in here first. So the first record, let's say it's Jim Kirk on 1/4 and he drove 300 miles. Then Will Riker puts in a thing on the fourth also - he drove 250 miles. Jim Kirk again on 1/5, he puts in 1701 miles. Star Trek nerds will get it. Then let's say Leonard McCoy puts in on 1/6, he puts in 80 miles. You can see for each employee, that's the date and there's a total miles. I don't want to have to remember these numbers. I want to be able to just type in the mileage amount and the date and see that on the employee form. I don't want to have to do this going between two tables, like you are right now with your Excel sheets. Those are not relational, so you're going to put in either an employee name or an ID or it's a pain. This is why we want to build a subform for this stuff. Let's go ahead and close this and close that. I'll start off building a simple form for my employees. Go to create and form design. I like to start with a splash of color. Go a little bit of color. Let's go with that for him. I'm going to bind this to the employee table. I cover this in my blank database template and in my Access Beginner 1 class if you don't know what I'm doing right now. Let's go to form design, add existing fields. We'll add the employee ID, first name, last name. Click, drag, drop, put them right there. Close that. We don't need to have this this tall. There we go. We'll put our mileage right here. Now save this as EmployeeF, as my employee form. It's pretty straightforward and simple. If I open it up, here are the employees. One, Jim Kirk. Two, Leonard McCoy. Three, Will Riker. The right way to do it is to build the subform first, and then insert it into the parent form like I showed you a minute ago when I was just giving you a demo. There is a subform control in the toolbox. I never use it. I don't like it. It's right there. Subform. You can drop it here like that and a little wizard starts up. I never use this wizard. You can pick an existing form, you can use existing tables and queries. It'll ask you for fields. I don't like this. I'm not going to run you through it. I'm going to cancel that and delete this thing. If you want to just throw a quick subform together, you can actually take a table like the mileage table. Click, drag, drop, and it's going to ask you for the relationship. Fortunately, the field is named the same in both tables. We have an EmployeeID in the employee table and an EmployeeID in the mileage table, so it sees that relationship there. If your fields are named differently, this might not work or you have to define your own relationship. Sometimes people might have, for example, a person table, and in that person table, you could have employees, customers, service technicians, whatever - and they're all people, but they all have different names and different tables. That's acceptable, but in this case, it's the same thing, so I'll hit Next. What's the name of the subform? The wizard will create a subform for you. So like MileageF, we'll call it, and it throws it together for you. I'll just slide it down here a little bit so you can see it better. Save it, close it, and then open it. Where's my employee form? There it is. It's okay. I mean, it's not very pretty. I don't really like it that much. So let's build our own mileage form. This is why I don't like the wizard. Close that, delete that abomination that it just created for me. Where's the mileage form? Goodbye. Delete. Let's create our own. Let's make a continuous form for the mileage. We're going to go to create and then form design. Now, you should have watched the continuous forms video, but I'm going to run through this real quick from scratch. Let's give it a splash of color like I always like to do. Let's give it a slightly different color. Let's go with yellow. That's not bad. I'm going to bind this form to the mileage table. Close that up, go to form design, and existing fields. Now, on this one, I don't need the MileageID. I don't care to see it. It's meaningless in this case. The EmployeeID is necessary, but I don't need to see that field on this form. I'll need it for the relationship, but as long as it's in the table, it's under this form. It's fine. All I really want in here is the MileageDate and the TotalMileage. Click and drag, drop them there. Close that. I'm going to turn this into a continuous form by opening up the form's properties, finding Default View, and changing that to continuous forms. Now, I'm going to turn on the form header and footer by right clicking here and going to form header and footer. Not the page header and footer, the form header and footer. I'm going to take these labels, snip them out, Ctrl+X, cut them out, paste them up top here. Now, since these are just labels, let me give them a little bit of color here. Make them black so we can read them. Since these are just labels, they're just being displayed on the screen, I can change that to "Date." That's only a label. Then for "Total Miles," we'll put a space in it. These are for display purposes only. They're not field names. Remember, we don't put spaces in our actual field names. Slide the MileageDate there. Put the TotalMileage next to it, like this, like that. Line them up nice. Take the bottom section here, drag that up because we don't need to waste all that space. We don't really need a footer for this right now. We can put a total down here - maybe I should add a total down there in a few minutes. For now, though, let's save this as MileageF, my mileage form. If I close that, then open it back up again, let's see what it looks like. A couple little things I don't like - let's left align both of these fields. I hate right aligning stuff. Let's get rid of that alternating background color too. So right click, design view. Select those two fields, format, align left. We're also going to open up the detail properties and get rid of that alternating background color. I don't like that. I like it on reports, but I almost never use it on forms. Save, close. Let's take a peek again. Not bad. This is another one of my Access pet peeves, this date picker. Notice how it just pops up right over the next field. I don't like that. I really hate that. You can turn it off if you want to, and in fact, I'm going to turn it off because this is a case where I almost always am typing it in, or if I'm putting in the mileage receipts for today, I want today's default date, which is that thing right there. So right click, design view. Go to the properties for that and find the Show Date Picker and turn that off. Never. I wish they had an option for it to be over the field instead of over the next field. I put this in Microsoft's suggestion bin. As a former MVP, maybe I got a little call out. I don't know. We'll see. Close that, open it back up again. That looks good. Now, as you can see here, I've got everybody's mileage, all of the employees. How do I break this down and limit it to only see the employee that I want? I'm going to put that inside of this form. We'll get rid of that one first of all. This is something interesting that just happened. I forgot to delete that and save it the last time, so it automatically picked it up because the last form was named "Mileage." We're just going to get this out of here for now. Goodbye, you're not supposed to be there yet. Remember, I built a second ago and I dragged the table in, and it made its own form. I called it "Mileage." So we're going to just go with EmployeeF. Here's our MileageF. I'm going to put this guy inside of this one. How do I do that? Just like I showed you before. We're going to take it like this, click and drag, and drop it. Get rid of that little label that comes in there. We'll make this taller, like so. Come here. There we go. Now we don't need a lot of the space. If we don't need this, we can slide that guy over like that. Save it, close it, open up EmployeeF now, and there you go. There's a nice pretty mileage form. If I move to a different employee, you can see I've got the different records for each employee showing up there. How does Access know to make that relationship? Like I said before, it sees that there is an EmployeeID, which I'm going to left-align that, by the way. There is an EmployeeID on this form, and there is an EmployeeID at least in the table that this form is based on. If you click on the subform, you'll see Link Master Fields and Link Child Fields. Access saw that there is an EmployeeID in both of those tables, the tables that these forms are bound to. So it made that relation for you. If you do this and you have different field names for the relational ID, you might have to manually change this. If this is called WorkerID and you've got EmployeeID in here, you can still relate those together if it's the same bit of data, but you might have to manually make that relationship. Subforms can be a little tricky to work with sometimes. If you click on this thing once, it selects the subform control up here. See "subform"? These are the properties for this subform control: its width, its height, where its top left corner is, all that stuff. If you click on it a second time, now you are on the control in that subform. So now I'm on the text box for TotalMileage. Back in the old days, the oldest versions of Access when I started working with Access, you could not do that. All you got was a blank white control box here. If you wanted to edit these properties, you had to open up that form separately. But now you can actually work on these properties inside of this one. I try not to, though. I try not to play with these objects while this thing is in its parent form. I'll close this and then come back to the MileageF if I want to make changes to this form. That's just me. That's how I grew up. It's how I was raised by the Access books and databases that I started with in 1994. But I think that's the best way to do it. I try not to design or change this guy from inside the parent form. I've seen it cause problems before. So, Genessa, there you go. There is your way to put your employees and your miles together. Nice and easy. If you want to add new miles, just click on the New button, come down here for today, put in 500, and then you're done. Go to the next employee, click the new button, let's say this guy's on 1/7, and you can put in 90 miles for him. Now, I mentioned I might show you how to put the total down here. Would you like to see that? Let me see the total. I will be happy to show you that. But first a word from our sponsor, and that sponsor is me. Real quick advertisement: Make sure if you haven't yet watched it, watch my Access Level 1 Beginner course. It's four hours long and teaches you everything you need to know about forms, tables, reports, all that stuff. If you're beyond the basics, in my expert lessons I cover lots of additional stuff, including subforms, which are covered in Access Expert Level 3. Levels 1 and 2 are mostly with relationships. Level 3 gets into some SQL, creating subforms, form footer totals (which I'm going to show you a little bit right now), and lots more. I have 32 different levels of expert lessons, so check them out. There's the address right there. You'll find it down below in the links section. Are you ready for some bonus material? Here we go. You want to total up the miles for each of these employees? Close this, go to the mileage form. Like I said, I like to do my design work for this form by itself. I'm going to add up these total miles. I'm going to copy this text box, copy, and click on the form footer and paste it down here. Now if I just save that and view it like this, it'll just show whatever record is up top because it's bound to the same field. So what we're going to do is change it just slightly. Open up its properties. First, change its name. Let's call this "SumTotalMiles." Its control source is now going to be =Sum([TotalMileage]), just like that. Here I'll zoom in so you can see that better. Shift+F2, zoom in. That's what it looks like. Now Access puts the square brackets around it. You don't really need it, but Access does it anyway. If you put spaces in your field names, Access will do this stuff for you. The Sum function, which I also cover in that Access Expert 3 class, will add up all the total miles. Hit OK. It's in the control source property. Maybe give it a little bold. Let's bold that, and calculated values, I like to make them gray so the users know they can't edit that. Save it, close it, open up your employee form, and look at that. It's the total miles for this person right here. Add it up in your head - it seems about right. Go to the next one, same: 170. Go to the next one. Yes, you like that. That right there alone was worth the price of admission. Now, if you want to learn more about subforms, I've got a lot of free videos in my TechHelp series that are on my website and on my YouTube channel that cover forms and subforms. For example, my vehicle maintenance database. You've got vehicles and you've got a maintenance history. I have a cool video on widows and orphans - that's preventing a widow or an orphan. That's basically preventing someone from putting a record in the subform without a record in the master form, the parent form. For example, here it's possible to go to a blank record where there is no employee and try to put something in here. Access will let you, and now you have a record in here - that's an orphan. It doesn't have an EmployeeID. Look at it. If I go to the table, where is it? See? I show you how to prevent that and the other way too - how to prevent a widow if you want to force the user to have to have a subform record, like an order with no items in it. I have a video that shows you how to put a subform inside of a continuous form, which Access tries to not let you do. I'll show you how you can do it. I have a video on nested continuous subforms - it's a subform inside of a subform inside of a subform, and they're all continuous forms. Sounds crazy, but it's a lot of fun. I have a video on event enrollment. This one's nice. It's basically classes or seminars you're doing, and then a list of employees or students who are enrolled in it. This is a nice simple form-subform. This is a good video. Watch this one. I'll put links to all of these down below, and they're all free. You can go watch them down in the section in the description. Like I said, if you really want to learn this stuff in detail, my Access Expert Level 3 class (an hour and a half) covers all this stuff: subforms, linking the child and master fields, SQL, and the form record source, all this stuff. For you database nerds out there like me who are into VBA developer programming, in Access Developer 21 I do a lot with subforms, including nested continuous forms and side-by-side subforms. That's where you get two subforms next to each other. If you click on this one, it synchronizes in this one. A whole lot of cool stuff in this class. If you want to learn more about subforms, in the extended cut for members, I will show you how to do some extra cool stuff with a little bit of VB code. How to prevent duplicate dates, how to prevent duplicate entries for the same employee. You don't want the same date entered for the employee more than one time. I'll show you two different ways to do it. It's called the composite key, where we can control things at the table level. Another way is to do it with VB code. I'll show you a little bit of programming. Of course, you should take my Intro to VBA class first or watch the Intro to VBA free video I've got. Anyways, we'll build a little data entry wizard. You click an Add button, it'll pop up and say, give me the date for the mileage report. It'll check to see if it's in the table already for that employee. If so, it'll say, sorry, you've already entered this one. If not, it'll ask you for the mileage. If you type in an invalid number, if it's less than zero or if it's over a thousand and you guys say that requires manager approval, we'll check all those things. We'll validate the entry. If the data works out or it's valid, we'll add it to the table, refresh the list, and show the most current stuff up top. Silver members and up get access to all of my extended cut videos. Gold members can download these databases, so become a member today. 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 will get access to all of my extended cut TechHelp videos, all of them, a free beginner class each month, and more. Gold members get access to download all of 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 decide to submit any TechHelp questions to me. You'll get one free expert class each month after you've finished the beginner series. Platinum members get all the previous perks plus even higher priority for TechHelp questions. You'll get access to all of my full beginner courses for every subject and one free developer class each month after you've finished 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 lots 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. QuizQ1. What is the primary benefit of using a subform inside a parent form in Microsoft Access?A. To allow editing unrelated tables in a single form B. To display related records from another table within the main form C. To create a backup of tables D. To import Excel data into Access directly Q2. Which structure does Genessa need to track employee mileage in an Access database? A. A single flat table for all employee and mileage data B. Two related tables: one for employees and one for mileage C. Multiple separate databases for each employee D. Only a mileage table with employee names entered manually Q3. In relational databases, what do you call a field in a table that uniquely identifies a record from another table? A. Composite key B. Primary key C. Foreign key D. Unique index Q4. When creating a table to store mileage records, why should you NOT use "Date" as a field name? A. It is not descriptive enough B. It is a reserved word in Access C. It requires special formatting D. It cannot be indexed Q5. What view should you use in Access to display multiple records at once in a form? A. Single form view B. Datasheet view only C. Continuous forms view D. PivotTable view Q6. How do you link a subform to a main form in Access so it shows only related records? A. By including matching field names as link master and link child fields B. By coloring the subform differently C. By putting a macro on the main form D. By saving both forms in the same folder Q7. If the related fields in your parent and subform are named differently, what must you do? A. Rename them so they match B. Manually define the Link Master/Child Fields property C. Delete one of the fields D. Convert the tables to flat files Q8. What is a continuous form in Access? A. A form that scrolls horizontally only B. A form that can display multiple related records at once C. A form that locks after the first edit D. A form that only shows totals Q9. When adding a total to a subform footer, which function is used to sum all values in a field? A. =Count([FieldName]) B. =Average([FieldName]) C. =Sum([FieldName]) D. =Total([FieldName]) Q10. Why is it recommended not to edit subform controls directly while in the parent form's design view? A. Access cannot save changes made in subforms this way B. It can sometimes cause problems or confusion in design C. The subform auto-locks in design view D. It makes the main form read-only Q11. What can result if you create a record in a subform when there is no associated record in the main form? A. A calculated field B. A composite key C. An orphan record D. A lookup mismatch Q12. What does Access automatically do when you drag a table or form into a main form as a subform, providing the related fields have the same name? A. Displays an error B. Prompts to rename the fields C. Sets up the Link Master/Child Fields automatically D. Removes unrelated records Q13. What is the main advantage of designing forms and subforms separately before joining them? A. It ensures forms are visually identical B. It avoids design and property confusion or errors C. It automatically links them with macros D. It imports data faster Q14. Why is sample (dummy) data helpful when building Microsoft Access forms and subforms? A. It helps with color selection B. It reduces database size C. It allows you to test relationships and see record displays D. It is necessary for security settings Answers: 1-B; 2-B; 3-C; 4-B; 5-C; 6-A; 7-B; 8-B; 9-C; 10-B; 11-C; 12-C; 13-B; 14-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. SummaryToday's video from Access Learning Zone focuses on how to build a form with a subform in Microsoft Access in order to display related records, which is a very common and useful technique. The scenario that prompted this lesson comes from Genessa, who is currently tracking employee mileage reimbursements with Excel, but wants to bring this process into her Access Employee database for greater efficiency and organization.To properly set this up, there are a few key Access concepts you need to know. If you're not already familiar with relationships and how to work with continuous forms, I highly suggest reviewing those topics. Relationships are fundamental to any relational database and will help you understand how Access connects data across tables. Continuous forms let you display multiple records at once instead of just a single record per form view. Both of these concepts are covered in free videos which are available on my website and YouTube channel, so make sure to check those out if you need a refresher before proceeding. In my TechHelp Free template database, there's an example using customers and their contacts. Each customer can have multiple contact records, and there's a form with a subform that displays these related contacts for any selected customer. The same approach can be applied for employees and their mileage records: an employee form with a subform showing all the mileage entries for that employee. To set this up for your mileage tracking, start by creating two tables: one for your employees with fields like EmployeeID (as the primary key), first name, last name, and any other details you want to track; and a second table for mileage, which will include MileageID as an auto number, EmployeeID as a foreign key (number data type), MileageDate (a date/time field, but not just "date" since that's a reserved word), and TotalMileage as a number. You can add more fields if you want to track notes or different reimbursement rates, but these basics will suffice. Once both tables are created, it helps to enter a few sample records in each to make the design and testing process easier. For example, enter a few employees and then mileage records for different dates and employees. Next, build a simple employee form. Using the Form Design view, bind the form to the employee table, add the fields you need, and save it as EmployeeF, your main employee form. When you open it, you'll see each employee, and the plan is to show their mileage records directly within this form. Now comes the subform. While Access has a subform wizard and control in the toolbox, I rarely use it because I prefer to have more control over the design. Instead, create a separate MileageF form specifically for mileage entries. Set this up as a continuous form, bind it to the mileage table, and include only those fields that you want users to see and interact with, such as MileageDate and TotalMileage. You can customize the appearance, alignments, and even disable the date picker if you prefer manual entry or just want today's date by default. Once your mileage form is looking the way you want, go back to your EmployeeF form in Design view and drag the MileageF form in as a subform. Delete the default label that appears if you do not need it, resize it as necessary, and save your changes. When you open the main employee form now, you'll see only the mileage records related to the current employee, thanks to the matching EmployeeID fields in both tables. If your field names differ, you may need to manually set the Link Master Fields and Link Child Fields properties to create the relationship. A subform allows you to view and add new mileage records for a selected employee without leaving the main form. This is much more efficient than flipping between separate tables like you would in Excel. If you want to take it a step further, add a total mileage calculation to your subform's footer. This involves creating an unbound text box in the footer and setting its control source to sum the TotalMileage field for the displayed records. This gives you an automatic running total for each employee's mileage right within the form. There are lots more free videos covering form and subform setups, including more advanced layouts such as nested continuous forms or scenarios like vehicle maintenance logs, event enrollments, and how to prevent orphaned records when using subforms. All of these are linked on my website. For those who want to go even further, Access Expert Level 3 covers subforms in depth along with more advanced details like SQL behind the scenes and linking fields manually. For developers, my advanced lessons show even more techniques such as validating for duplicate dates, preventing bad data entry, and using VBA to customize your data entry forms and workflows. If you're interested in these advanced features, the Extended Cut for members will show you how to prevent duplicate dates for the same employee, implement composite keys, and validate entries in the subform using VBA. I'll also show how to build a simple data entry wizard for mileage reports that checks for duplicates and validates data before saving. If you are new to Access, my free Access Beginner 1 course covers all the basics, including tables, forms, and reports. More advanced users can look into my Access Expert and Developer courses. Membership levels come with various perks, including access to extended lessons, sample databases, code vaults, and higher support priority. You will always have access to these free TechHelp videos. As long as you keep watching, I will keep producing new content. 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 ListCreating employee and mileage tables in AccessDefining primary and foreign keys for related tables Entering sample data into Access tables Creating a simple employee data entry form Building a continuous subform for mileage entries Formatting Access forms for better usability Configuring form field properties such as date picker Inserting a continuous subform into a main form Establishing parent-child relationships with subforms Customizing Link Master Fields and Link Child Fields Properly aligning and resizing subforms in a parent form Updating and displaying related records in subforms Adding a calculated total to a continuous subform Using =Sum() function in a form footer Limiting subform data to the related parent record |
||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access Create a form that contains a subform, One-to-many form, What is the subform in Access, How do you create a subform in Access, What is main form and subform in MS Access, access subform tutorial, how to create subforms, how to use subforms PermaLink Subforms in Microsoft Access |
