MS Access - Dynamically Updating A Combo Box From Another Combo Box

hello and welcome to access all-in-one comm in this video I'm going to be showing you how you can dynamically update one combo box from another combo box the idea is that you have two combo boxes on the form and whatever you select in the first combo box will determine what you see in the second combo box this is a very very common question that people ask me so I thought I'd actually make a video and deal with it okay I'm going to be using a database for films and I hesitate to call this a database it's just a couple of tables and what we have is we have a list of directors and a list of films and I've combined them in this one form so you can see the name of the film and the director that's obviously a look up and there's a one-to-many relationship because one director can appear in many films so this is the kind of relationship where you would do this with the combo boxes it makes perfect sense because what we would want is a combo box displaying all the directors and then when we select the director we want the second combo box to display films that director is directed so if you look at films the s if I were to select Spielberg for example in the first combo box in the second combo box I would want to see et I would want to see Jurassic Park I want to see jaws so that's kind of the idea so let's get going should mention I this isn't like I said much of a database but I'll I'll put a link below the video to this this whatever this is anyway so you can download this file and follow along exactly if you wish okay so let's get cracking so create a blank form so this is going to be my test form so I'm going to save that call it form test such a great name and next thing I'm going to do is add the two combo boxes so this is going to be the combo box for film directors and this is going to be for films let's change the label directors films okay and let's just make sure they're named properly so this default name is combo zero I'm going to change the CBO directors and I'm going to change this to CBO films okay now what we're going to do first of all is I'm just going to get this combo box to display all the directors I want to get this combo box to display all the films and then I'm going to show you how choosing the director can filter the film's so we click on the directors combo box go to data in a property sheet and click on row source and direct this table and we want ID let's just got a last name for now and we shall sort them sending okay now format we need two columns we've got ID and the name now the column widths what I would normally do is 0 comma 4 so you see this you just see the name of the director because I'm demonstrating something I'm going to change that and I'm going to say 1 comma 4 so I can have both fields so the reason I've done this is I want you to understand that the second column the name is what we choose but that's not what's actually stored in this combo box what's actually stored as the value is the ID so we can see a name but the actual ID of the director is stored in the combo box this is useful because in the films table the director ID field stores the idea of the director and this will become relevant in a minute but first of all let's add D films so same again data row source films ID film name sending save that and this one I don't need to see the first column at all it's like the concepts exactly the same if you choose the film in this combo box we see the name of the film but the actual combo box stores the ID value is just I've hidden that column okay so over all the directors I want and I've got all of the films I want and what I want to do now is somehow by choosing a director here I want only that directors films to show up here how do we do it thirdly simple layout view and palmy layout view even data and we're going back to the row source for the films and what we're going to do is we're going to drop in that director ID field so now we've got the direct ID field wouldn't it be useful if we could say to this query okay I only want to see films where the director ID is equal to what's stored in this combo box in this case too and we can do that by referencing that combo box from here and click in the criteria director ID click on builder and to reference that combo box we first we'll need the forms collection then exclamation mark the name of the form is form test another exclamation mark for the controls are interested in CBO directors so actually referencing CBO directors here and if we run it we can see that director idea is 2 and the director idea is 2 so we've got the query here but does what we want but we need to do one other little thing when it because at the moment if I click on for example Spielberg I've still got 2001 a Space Oddity and the shining which was all we had for Kubrick the reason is whenever I update this combo box I need to make sure that this combo box Ari queries so very simple make sure you clicked on the directors combo box go to event go to after update so we're just going to write one line macro well yeah one one macro action and it's wreak weary and control work requiring the CBO films so now whenever I choose the director it will immediately wreak weary discs on the box with the films of the director okay and last thing I'm going to do is I'm just going to change that so we don't see that ID that was purely for demonstration purposes so now we know that McQueen directed 12 years of slave we know that Nolan did inception mentor The Dark Knight and we know that Spielberg did ET Jaws and Jurassic Park okey dokey that is how you do it hope that works for you and if you're still a little bit vague about understanding why that works watch the video again that's why the videos and if you're still a little bit vague after that just follow the process if you follow the process you don't need to always understand every little thing that's going on if you followed that process you would always get the same result so thank you for watching and see you next time

Channel: Access All In One

Views: 221,748

Rating: 4.9018002 out of 5

Keywords: Microsoft, Access, Combo, Combo-box, Combo Box, Update, Dynamic, Drop Down, Drop-Down, Drop Down Menu, update one combo box from another

Id: I_wwlZofCgk

Length: 7min 27sec (447 seconds)

Published: Tue Feb 25 2014