How to Retrieve Values on a Subform from a Parent Form and Vice-Versa in MS Access

i'm your host sean mckenzie thanks for joining me once again on my channel on data engineering in this episode we go back to our microsoft access playlist and we're going to take a look at how to get values from a sub form or a parent form when we're trying to you know do some coding or something like that and our code is running on the parent form or or say a lookup control is on the parent form but we want some values from the subform or we're running things on the subforum and we need to get values from the parent form and this is something that trips up uh developers all the time and it's a great subject for today's video so without further ado let's get to our parent and sub form lookups need help for coaching on your project make sure to check out my patreon the link is in the description okay so we're going to start off here and we're using an access file that we used in some of our previous episodes and as you can see i have these order table there was a you know some orders by division and order id and we had some products that were on the orders and some order items and and uh and so we had these tables and uh what what we did was uh we created this order form and it had two subforms on it one for the items and one for products and we were demonstrating how to do sub forms and we also put those onto tabs so you can see on the first tab here we had some order items on the second tab was a product lookup and uh and and on the third tab we just put one of the fields from the main record set which is actually up above there with john smith and so we demonstrated how to do that and so we have a sub form here with this first row that you can see there is monster puppet and if i go to design mode here you can see if i click on inside of that tab and i i click on the subform container there you see it's called orders item subform and it's got the orders item subform in it and as you can see it's linked using some fields so that that subform only shows related data data that's related to the parent record there and what we're going to do is we're going to look at this i guess we'll take this order item field here and what we'll do is we're going to make an event that you know fires when we double click on it so that we can just demonstrate how to get some values from this form but also from the parent form so click the ellipsis click the code builder and then we'll get our subroutine started and so first of all uh we'll we'll create a couple of variables here i'll just call this one return value i suppose with the variant and i'll just say the return value is equal to me and the order item now note that this code is running in the subform it's the code behind for the subform and so when it executes you can say or you can use me and you can just say me exclamation order item that's going to do a reference so it's going to look that up and so and then we'll sort of output a message box and we'll say this is the item this is the simplest lookup that we can do so now if i go and i run this form now i can double click in this field and you can see this is the item monster puppet i can go okay or if i was on another field it'll you know it'll pick up that value as well and so now what i can do is i can go back to our procedure so you can see it's got the event procedure in there already you just click click the ellipsis and it will open up your code again and now we can add some other fields so you can see on our parent form we have the first name and last name fields and so we can get those values from the you know perspective of the code running in our subform we can go and get those and what we'll do is i'll just create a couple of variables here i'll call it uh you know first and last and and make a note that you know when if you don't specify a data type here or a variable type the default is variant so that means that it can accept null values as well um so what we'll do here is i'll say the last you know i'll call it uh we'll go to me.parent.form so what that's going to do is from the perspective of the subform that's running it says me dot parent and then we'll say dot form and then exclamation then the field that we want to get the value from and we can do the same thing for the first name we'll go me dot parent dot form exclamation first name and that's going to give us the first name from the field that's in there that's on the parent form and so if i change our concatenated string that we're using for our message box then i can change it to include the first name and the last name so now it'll say this is the item uh whatever for you know the two fields on the uh the name in the two fields up top so now if i go back and i run it you can see see that it says this is the puppet for john smith this is the item roller skates for john smith etc etc you can click on a whole bunch of them and you can see how how it goes and and it's contextual right so how the code is running in that moment it's a sub form on whatever the parent form is because you could have a sub form that's on 10 different forms but it's the same sub form so it will work even if you're using it on a different form as long as that form has the fields first name and last name so uh you can also change the the way that you type it there i i put it in in parentheses so you can see that that also works that's also a way that you can look up the the values you can you can specify the field name without using the exclamation and and we that's a very handy way of doing it and and also um this is handy you can also go and you can look up a sub form within a sub form as well using a similar uh style and so what if i was on the parent form though and i'm running some code on the parent form and i want to get some values from the subform well that also that is very uh similar to the way that we do the subform lookup and we can do that so i'll grab a field i grab order id the same thing uh double click event use the ellipsis to open the code builder and i'll create another one i'll call it var order this time i'll grab the order id and i guess i'll grab just the first name and maybe the and the first item so the first order you know give give me the the name and the first item in the order or something like that just to demonstrate um how to get the value so now what i can do is i can say well my order is equal to me order id i'll just check that field is correct there in a sec here and the first name is me and that'll be me and first name and uh also uh we will get the first item that is in the sub form uh just for this purp this particular example so what we'll say here is we'll say me and then exclamation and then we're gonna put the order uh item subform into as the as the con the control name so what we'll do is we'll go and look i want to click on it and okay i it looks like i need an acid order items subform so we'll we'll put that in there order items subform and then in the same way that we did before we can use the dot form so because the control is a form [Music] then we can say dot form and grab the field name that we want which is order item and uh and that will give us the value that's in the first line there because the the subform will open and the first selected record uh will be will have a value in it and so um in this particular case we're just grabbing the first one and so we can create our message box again with the first the first name and the item so we can say you know mary is getting you know this item and then we'll put the order uh the like the order id on it as well and that will sort of round out the example so we'll say you know mary is getting whatever the item is and then the order item the order id in brackets and we'll just say that that's our example and so now we can do that lookup this code is running in the parent form now and by the context it says you know go into my sub item subform and get that value off of the subform and so we can do that and so now if i double click here you can see that john is getting a monster puppet on his order because we grab that first row and you can see it's selected so whatever the value that is selected if you look at the little arrow on the on the left of the subform record but if we scroll through the record set now this is a different order if we double click on it you know alice is getting alice in wonderland on on you know their order and i guess i should change the wording for that um so yeah you can see that um that now we can we can run code on the parent form and reference the subform and we can run code on the subform and reference the parent form and get values for various purposes and just as i mentioned before there's several ways that you can get the value so we could look at this value could use the exclamation point to to reference it more like a an an expression and i should also note that you can use a lot of this in forms in form controls as well so you can actually put in a text control you can say equals you know something from the subform without any code at all [Music] but as you can see if i double click here it's running the code just fine using that exclamation point and that is how you can do uh sub form and parent form lookups in microsoft access hope you enjoyed today's video on how to do uh parent and sub form lookups if you like what you saw please give the video a thumbs up make sure to subscribe to the channel click the bell when you see the bell and put any questions or comments you have in the comment section below have a great day have a safe day and i'll catch you next time

Channel: Sean MacKenzie Data Engineering

Views: 12,569

Keywords: how to retrieve values on a subform from a parent form and vice versa, ms access subform, subform get value, parent form get value ms access, access subform field value, microsoft access, ms access, visual basic for applications, access tutorial, microsoft access tutorial, sean mckenzie data engineering

Id: 7x06xjEjQ78

Length: 12min 39sec (759 seconds)

Published: Tue Nov 09 2021