16. (Advanced Programming In Access 2013) Linking Parent Form Controls to Sub-Forms

hello everyone and welcome to programming in Access 2013 the Advanced Course my name is Steve Bishop and in today's video we're going to be talking about linking parent farm controls to subforms so in our last video we actually linked our parent form and the data that was being presented in the parent form to filtering out our sub forms so that we could have the active orders based upon the current employee that was being viewed on the parent form well this is not entirely the functionality that we can see if we look at the Northwind database so let's go ahead and hop out here and our example that we've been using the last couple of videos was this ability to select from the combo box an employee and then based upon that selection we would filter out this active order subform so if I have Nancy selected here we get just her orders if I select Andrew we get his orders so based upon this drop down box is actually filtering out the active orders not necessarily which record we're on and just to reinforce that idea of what's going on here you can see that the record source for the parent form here is this table called strings and the strings table if we just open that up really has absolutely nothing to do with the employee that's being selected so how is it working that this combo box is what's dictating what's in the active orders in heck you can even see the combo box is unbound it's not bound to anything so clearly something else is going on here for the relationship between this subform and the combo box and that's what we're going to go over today so let's go ahead and exit out of that and I'm going to go over to the form that we've been working on here the form active orders and let's go into the design view here now I'm going to go ahead and actually remove these three these three fields here are these text fields and I'm also going to go into the linked master fields and the link child fields I'm just going to delete everything that's in there so there's no link whatsoever between these two things and then one last step here is I'm actually going to go to the record source and remove it entirely okay so there's really nothing in this parent form anymore that we can utilize to filter out our active orders and just to reinforce that you can see active orders everything is here we've got every active order that's out there right now and we don't have the ability to go back and forth between different employees because there aren't any employees on the parent form so let's go ahead and go back into the design view here and let's establish our combo box that's going to show our employees so let's we've done this a few times we're going to do a employee label here and make this a little wider because we're going to have to display the employee names in this and let's change this combo box name so CBO employee and the data we're going to save the row source we need to go to the ellipsis here we're going to fill in the row source with the employees table and it's going to be the ID is going to be the first column and then we're going to do first name last name and even more so I'm going to go into the sequel view here and concatenate these two together so we'll have first name and a space between the last name there and let's also give this an alias so as employee name and then last but not least I'm going to go ahead and order by the first name you need to do this in brackets first name that way we actually have a specific order to the way that these come out and there we go so we can see the employee named Andrew and Jan Laura etc so that's the order that they're going to show up in our combo box and here is the field that's going to be returned right the ID field is what we want to have returned back as the value for the combo box so let's go and save that and we can see bound column is one so that means the first filled in our query that employee oops that employees ID sorry kind of goofed up there that employees ID field is our first column of our row source so therefore that's what's going to get returned when the user selects something from the combo box now we need to go to the format and we need to say the column count is two because we've got the ID field and the employee name field so there's two columns the column widths if I just take a look at this right now you'll see that the combo box shows me the ID for the employee that's really unnecessary so I'm going to actually margin that out I'm going to take that out of the view by setting the column width equal to zero and that's all I really need to do because the second column will take up the rest of it will take up the rest of the view of the combo box and now when we look at it we have our employee combo box alphabetically-ordered and when I select on one I'm going to be returning back from this combo box the employee ID right so I'm going to be returning back Jan's ID number but we don't have any linking going on right now right this not it's still not filtering out our active orders we still see everything so what you're going to want to do is you're going to go back into the subform here you're going to go to the data tab and you can see link master fields before what we did was we selected here and clicked on the ellipsis and you'll see we get an error can't build a link between unbound forms okay and that's because again we don't have any data going through our parent form but what we can do is we can actually type it in ourselves of what we want it to be linked with and the master field the master field the field that's going to be used in the parent form is this combo box the CBO employee so that's what we're going to put here for the master fields it's going to be CBO employee I'm just putting it in brackets just for the heck of it just to make sure okay the linked child fields is going to be going into here and looking at the record source and looking for the employee ID that's the foreign key remember the foreign key that is the actual ID that we want to the employee ID that we want to filter it by so we're going to select employee ID as what we want for our for our child field okay so I'm going to enter that and again I need the brackets here especially because I've got a space in employee ID oops so we're linking the combo box to the employee ID field okay and you'll notice there is no employee ID text box okay the child field needs to be a an actual column that you get from the query or from the table that is part of the record source of the subform so we've got that employee ID field here that's part of the record source of our subform and that is what we're putting down as the child field that we're going to be filtering okay and we're going to be filtering it where it's equal to the employee combo box the CBO employee box so the actual parent the master field is actually the combo box okay it's a little confusing but I hope that makes sense to you now when we view this you can see we get the functionality that we're looking for so there's Andrew when I select Andrew we get his active orders when I select Nancy we're going to get her active orders and again there's no VBA code that's being used here in this specific example okay this is all strictly based upon the parent-child relationship okay so that being said there is a VBA way to do this and I'm going to show you how to do this in VBA in the next video

Channel: Programming Made EZ

Views: 54,742

Rating: 4.9567566 out of 5

Keywords: Microsoft, Access, 2013, Programming, VBA, Visual Basic, Applications, tutorial, lesson, guide, database, SQL, advanced, software, microsoft access, table, query, form, code, coding, development, visual basic for applications, computer programming, microsoft SQL server, SQL server, programming language, sub-form, subform, parent, child, link

Id: s2yeGsrnp2M

Length: 8min 39sec (519 seconds)

Published: Sun Jun 14 2015