Microsoft Access Form with a SubForm Tutorial

welcome to a free tip of the week brought to you by access learning zone comm I'm your instructor Richard Ross in today's lesson I'm going to show you how to set up a form with a subform so that you can have multiple related records with your form now if you haven't yet watched my tip on making relational tables go to my website right now access learning zone.com slash tips and find the free tip tutorial called making relational tables here's my Tips & Tricks page scroll down so you find the making relational tables tutorial and this tutorial will cover exactly what relational tables are and we'll talk about proper table relationships and something called normalizing your data if you don't understand this primary foreign key relationship then you might have a little trouble with this tutorial now you can apply the technique I'm going to show you in this lesson to pretty much any two things that relate together for example students and what courses they're taking or patients and what medications they're on or customers and their list of orders or products and the vendors that you buy them from any kind of one-to-many relationship this will work just fine so I'll start off with a blank database I'm using Access 2007 but you can use 2000 ro3 or any of the previous versions this will be my form subform database but you can call it whatever you want I'll start off by creating a table I'll click table design and I want a table first of all for my students so I'll start off with students ID that'll be my auto number for this table and then the student name text and you can include address and phone number first name last name all that stuff whatever you want to put in here I'll save it this will be my student table and access yells because I have no primary key - fine I'll say yes it'll put a little key there so that's my primary key and I'll put some data in this table real quick and my student name table let's just put in here mark and Sue and Joe and Bill yeah that's good enough for now I'll close that table and create a new table this table is going to store the courses that each of these students are taking so we'll start off with the ID for this table is going to be course ID and that'll be the auto number for this table the course name which will make text that'll be like English 101 or math 102 right then we need to know which student so we'll say students ID right that's the foreign key for this table it relates back to the student and any other information you might want to have in here like notes you know or the date time of enrollment that kind of stuff whatever you want to put in here that's fine let's save this table this would be my course tea again it yells at me about the primary key I almost always forget to do that that's when they click this primary key field here and hit the primary key button I almost always let access do that for me it just finds the first auto number all right let's save this table and close it I'm not going to put any sample data in that one for now now let's create our forms click on student T and then click on create and then form that'll automatically make a form for you for the student t get the student ID and the student name and this little header information up top here I'm going to go to design view I'm gonna get rid of this stuff up top here it's wasting some space on my screen and just slide that details section up just to keep things from getting cluttered alright student name doesn't need to be that big okay now I'm gonna grab this form footer section and drag it down so we've got some extra room in here now to make the subform there's a wizard for this so up here in the controls or in the control panel if you have the access 2003 version or 2000 yet little toolbox or your controls are make sure your wizards are turned on the control wizards and find the subform control right here click on that and then draw a box down here we want your subform to go now the wizard should start right up and the first step of the wizard says you want to use an existing form or an existing table we don't have any other forms we'll let wizard build it for us so I'll say use existing tables and queries next which other table do you want to use for your data while the main form is students so courses course T is going to be the subform which fields do you want to bring over into your subform well let's bring them all over alright next next one wants to know the relationship between the two tables and access is pretty good at defining this relationship for you if you've named your fields right and here it says show course T for each record in student T using student ID C since we have a student ID field in both of those tables it related them together and it will automatically make that relationship for you as long as your fields are named the same thing I'll hit next give it a name I'll call this my course subform F and then finish alright now here's my subform down here inside of the main form it gives me a little label there I'm just gonna click and delete that label and then slide the subform over a little bit and then just kind of compress some of this stuff to save space let's save this alright the main form is going to be my students F I like the N all my form is in F it automatically saved the course subform down here let's take a look at it now in form view and see what we've got okay here student 1 mark we got course name I'm gonna resize some of these columns because they're pretty big the student ID the notes the date time and down here we'd simply put in whatever courses mark is taking let's say he's taking English 101 alright and you can put a date/time in here if I don't know 1 109 all right his next course notice the student ID is populated automatically because of the relationship that the wizard set up all right let's say he's taking math 102 all right on to 109 or whatever right computer science 101 and so on alright let's go to the next student now notice if you got two sets of navigation buttons down here this one is for the subform this one is for the main student form there's soo alright soo is taking art 101 philosophy 101 and science 101 let's say alright let's go to the next student alright Joe is taking astronomy 301 okay now if I go back through my records here my students you can see it saved all of their course information and that's how you set up a form subform relationship if you'd like to learn more about form subform relationships I cover more in my access 202 class which deals specifically with setting up sub forms here you can see a subform we set up to store contacts for each customer in our database and in Access 301 we actually build an order entry system where we have an order we pick a customer from a drop-down and then we enter all the line items the parts and such to learn more about access forms and sub forms or any other topic about access come to my website at access learning zone comm and make sure to stop by the free tips and tricks section attention YouTube viewers if you stop by access learning zone.com slash youtube you'll get a huge discount on any of my full tutorials and you can watch a full free copy of my full 90 minute long access one-on-one tutorial so again stop by access learning zone comm slash YouTube

Channel: Computer Learning Zone

Views: 206,634

Rating: 4.753304 out of 5

Keywords: microsoft access, microsoft, access, form, subform, tutorial, relational database

Id: YZKN_-P6wck

Length: 8min 13sec (493 seconds)

Published: Mon Jul 06 2009