Get a Value from an Open Form in Microsoft Access using Forms!FormName!FieldName Notation

welcome to another tech help video brought to you by accesslearningzone.com i am your instructor richard ross in today's video i'm going to show you how to get a value from an open form using the forms form name field name notation in your microsoft access databases today's question comes from cole from chandler arizona one of my platinum members cole says i'm using your invoicing template which is very helpful thank you you're welcome when you click to add a new invoice for a customer is there a way to have the customer combo box automatically set to the currently open customer on the customer form yes cole that is certainly possible if you know how to get a value from another open form let me show you how so here's a copy of my tech help free template this is a free download from my website i'll put a link down below cole was referring to my invoicing video which is also free i'll put a link to that down below as well and in that video if you open up a customer click on orders we create this order entry system you can print out a nice invoice right there but the problem is if you go to create a new order notice there's nothing here in the customer combo box so it would be nice if this combo box could get its default value from the currently open customer on the customer form well how do you do that well let me show you another trick first it's a little easier let's say i want to run a query showing all of the customers from florida now i've shown in my other videos how you can do a parameter query where the user can type in the state every time they run the query but let's say 9 times out of 10 i'm in florida so most of my customers if i was a local business you'd think would be floridians so how about on the main menu i put a little text box where florida is the default value i can change it if i want to but then when i click the button to run the query it gets that value from the main menu alright so let's right click design view and i'm going to create a text box right here so design grab a text box put it right here on the main menu alright i'm going to slide over the label and let's make that white so we can actually see it let me open this up permanently there we go let's change this to white i'll change that to state and we'll call this guy let's call this state filter default value let's go over the data tab default value is going to be fl inside of quotes okay so now if i close this save it and open up my main menu again i've got a little text box there with the default state of florida in it now how do we refer to this box the way that you refer to a value on an open form the form has to be open is forms bang that's the exclamation point remember that i got a whole video on bang vs dot go watch that forms bang form name bang field name so in this case the name of that text box is forms main menu f state filter okay that's the name of the text box the name of the form and it's part of the forms collection so now how do i go about getting that well we can use that name as criteria in a query now if you have never done query criteria before go watch my query criteria video again i'll put a link down below in the links section right query criteria so let's create a new query bring in the customer table and i will go customer id first name last name and bring in the state now for the state's criteria let me widen this out a little bit let's set this equal to forms exclamation point main menu f exclamation point state filter there it is let's save this as my customer state q and now let's run it and there you go there's customers just from florida because it got the criteria from that form in the background that's the main menu form state filter now if i close this and i come in here and change this to new york now after you type in new york make sure you hit tab because you have to move off of that field for it to save the value in the form if you don't it won't change it all right now if i run customer state queue look at that just the new yorkers all right close it i think we have someone from texas too texas tab and then run it there you go you could also do it with a button right design view give me a command button drop it there we're going to go to miscellaneous run query next which query customer state queue next and then whatever you want here we'll just go with uh we'll go with text run query or open customers right next give it a name open customer button whatever you want to call it and then finish all right so there's my button to open up the customers save it close it open up your main menu again and now open customers there's your floridians close it type in new york open customers there you go do you have to hit tab this time no because clicking on the button moves off of that field so it's essentially the same thing all right change this to texas open customers there you go okay so that's how you get the value from the open forum now how do we apply this to our order form so when i go to a new record here this combo box gets the default value well right click design view open up the properties for the combo box on the data tab find default value and say equals this time you have to put an equals in front of it equals forms not main menu f that's the other one customer f exclamation point customer id all right give me the customer id off of the customer form and it has to be open if it's not open don't worry it'll just still be blank okay save it close it close that close that just make sure shut everything down reopen it all right go to orders now go to a new record and look at that i'm the default value see that because it's getting it off of the open form in order for this to work the customer form has to be open if you go to a different customer let's go to uh jean-luc picard orders and he doesn't have any orders so the first one is a new order and there he is see that that's how you get the default value off of another form now there is one problem with this method and the problem is technically since this says new up here there's no value in this order id so there's nothing to link to down here if i start typing in a product and hit tab or go to the next record you can see here it's putting products in but if i leave it and come back to it there's nothing in the table why because if you look at the order table okay we didn't enter a record in here so we have order details now with no order id okay the way we can fix that is at the table level come into the order table right design view on the order table the customer id the default value is currently set to zero get rid of that this is going to force the user to have to put customers in all right make required yes save that that just says data rules have changed that's okay now go to the order detail table design view make sure the order id get rid of the zero there and set the required value to yes there as well now you can't put an order in without a customer and you can't put order details in without an order id so now if i go to a customer go to an order go to a new order if i come down here and start putting values in look at that you must enter a value in the order id field in other words it's saying you can't put a record down here without starting a record up here same so you'll have to hit escape and do something up here to initiate a new record okay if you just pick the customer manually that'll do it or put something in the description right new pc anything up here to initiate this id to get set okay it's not the most elegant solution but it works okay in the extended cut for members i'll show you a more elegant solution something that works a little better than just having the user get that error message okay but there you go cole that should answer your question how to get a default value in this combo box if you want to learn more about getting a value from an open form i cover a lot more in the extended cut for members we will create a more elegant solution instead of just throwing an error message up in the user's face if they try typing in a line item without first assigning an order id we'll check to see if that order id is set or if it's null and if it's null then we'll create one all right automatically with a little bit of eb code tiny little bit we'll learn about the before insert event then i'll teach you how to get a value from a sub form that total on the bottom down there where the arrow is right that 12 that's in a sub form a little bit different trick some different nomenclature if you want to get a value from an open sub form all right it's a little more complicated but i'll show you how in the extended cut and we'll do a little tiny bit of error handling right with an on error go to statement all that's covered in the extended cut for members silver members and up get access to all of my extended cut videos and i also cover a lot more with the forms form name field name notation in my access expert 2 class the full course hour and 25 minutes long covers lots of other stuff too database normalization working with the relationships window global relationships referential integrity cascade update and deletes lots of stuff in that class access expert too how do you become a member click the join button below the video after you click the join button you'll see a list of all the different types of membership levels that are available silver members and up will get access to all of the extended cut tech help videos live video and chat sessions and more gold members get access to a download folder containing all the sample databases that i build in my tech help videos plus my code vault where i keep tons of different functions that i use platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses these are the full-length courses found on my website and not just for access i also teach word excel visual basic asp and lots more but don't worry these free tech help videos are going to keep coming as long as you keep watching them i'll keep making more if you like this video please give me a thumbs up and feel free to post any comments that you have i do read them all make sure you subscribe to my channel which is completely free and click the bell icon and select all to receive notifications when new videos are posted click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link to join my mailing list now if you have not yet tried my free access level one course check it out now it covers all the basics of building databases with access it's over three hours long you can find it on my website or on my youtube channel and if you like level one level two is just one dollar and it's also free for all members of my youtube channel at any level want to have your question answered in a video just like this one visit my tech help page and you can send me your question there click here to watch my free access beginner level 1 course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com

Channel: Computer Learning Zone

Views: 5,962

Rating: 4.9157896 out of 5

Keywords: microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, Forms!CustomerF!CustomerID, Before Insert Event, Value from a Subform, Debugging, On Error Goto, Value from Subform, Check for OrderID, Forms!FormName!FieldName, .Form!, value from a different form, criteria from a form

Id: 0B7AOA-zZkU

Length: 12min 52sec (772 seconds)

Published: Sun Mar 14 2021