|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Value From a Form By Richard Rost 
5 years ago
Get Value from a Form. Forms!FormName!FieldName In this video, you will learn how to get a value from another open form in Microsoft Access. I will show you how to default a new order to the currently open customer. Kole from Chandler, Arizona (a Platinum Member) asks: I'm using your Invoicing Template which is very helpful. Thank you. When you click to add a new invoice for a customer, is there any way to have the customer combo box automatically set to the currently open customer on the customer form? MembersMembers will learn a more elegant solution to check to see if the OrderID is null when the user starts entering a line item in the order detail form. If so, one will be automatically assigned. This will prevent an error message. Also, members will learn how to reference a value on a subform and some simple debugging with On Error Goto.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today! Syntax
Links
Learn More
Keywordsmicrosoft 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
IntroIn this video, I will show you how to reference a value from an open form in Microsoft Access using the Forms!FormName!FieldName notation. We will look at setting a default value in a combo box on an order form based on a value from an open customer form, and I will also demonstrate how to use values from a main menu form to filter queries with a parameter. You'll see how to set up default values, how to use form control references in queries, and how these techniques can streamline your workflow in Access.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, I am 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 am using your invoicing template, which is very helpful. Thank you." You are 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 is a copy of my TechHelp free template. This is a free download from my website. I will put a link down below. Cole was referring to my invoicing video, which is also free. I will put a link to that down below as well. In that video, if you open up a customer and 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 is 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. How do you do that? Let me show you another trick first. It is a little easier. Let's say I want to run a query showing all of the customers from Florida. I have 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 nine times out of ten, I am in Florida. So most of my customers, if I was a local business, you would 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. Then, when I click the button to run the query, it gets that value from the main menu. Right-click, then go to design view. I am going to create a text box right here. Go to design, grab a text box, and put it right here on the main menu. I am going to slide over the label and make that white so we can actually see it. Let me open this up permanently. There we go. Change this to white. Change that to "state." We will call this "state filter." Default value: go over to the data tab and set the default value to FL inside quotes. Now, if I close this, save it, and open up my main menu again, I have a little text box there with the default state of Florida in it. 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 is the exclamation point, remember that?). I have a whole video on bang versus dot. Go watch that. Forms, bang, form name, bang, field name. In this case, the name of that text box is Forms!MainMenuF!StateFilter. That is the name of the text box, the name of the form, and it is part of the forms collection. How do I go about getting that? We can use that name as criteria in a query. If you have never done query criteria before, go watch my query criteria video. Again, I will put a link down below in the links section. Let's create a new query, bring in the customer table, and go CustomerID, FirstName, LastName, and State. Now, for the state's criteria, let me widen this out a little bit. Set this equal to Forms!MainMenuF!StateFilter. Let's save this as MyCustomerStateQ and run it. There you go. There are customers just from Florida because it got the criteria from that form in the background. That is the MainMenu form StateFilter. If I close this and come in here and change this to New York (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 do not, it will not change it), then, if I run CustomerStateQ, you will see just the New Yorkers. Close it. I think we have someone from Texas, too. Type "Texas," hit Tab, and then run it. There you go. You could also do it with a button. Go to design view, give me a command button, and drop it there. We are going to go to Miscellaneous -> Run Query. Next, which query? CustomerStateQ. Next. Whatever you want here, we will just go with text "Run Query" or "Open Customers." Next, give it a name, "OpenCustomerButton," whatever you want to call it, then finish. There is my button to open up the customers. Save it, close it, open up your main menu again, and now click "Open Customers." There are 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 is essentially the same thing. Change this to Texas. Open Customers. There you go. So that is how you get the value from the open form. 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? 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: =Forms!CustomerF!CustomerID. Give me the CustomerID off of the customer form, and it has to be open. If not open, do not worry. It will just still be blank. Save it. Close it. Close that. Close that. Just make sure I shut everything down. Re-open it. Go to orders now, go to a new record, and look at that. I am the default value. See that? Because it is 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 Jean-Luc Picard, orders, and he does not have any orders, so the first one is a new order - there he is. See that? That is how you get the default value off of another form. Now, there is one problem with this method. The problem is, technically, since this says "new" up here, there is no value in this OrderID, so there is 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 is putting products in, but if I leave it and come back to it, there is nothing in the table. Why? Because if you look at the order table, we did not enter a record in here, so we have order details now with no OrderID. The way we can fix that is at the table level: come into the order table, design view on the order table. The CustomerID default value is currently set to zero. Get rid of that. This will force the user to put customers in. Make Required = Yes. Save that. That just says data rules have changed. That is okay. Now go to the order detail table, design view. Make sure the OrderID, get rid of the zero there, and set the Required value to Yes there as well. Now you cannot put an order in without a customer, and you cannot put order details in without an OrderID. 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 OrderID field. In other words, you cannot put a record down here without starting a record up here. You will have to hit Escape and do something up here to initiate a new record. If you just pick the customer manually, that will do it, or put something in the description. "New PC." Anything up here to initiate this ID to get set. It is not the most elegant solution, but it works. In the extended cut for members, I will show you a more elegant solution, something that works a little better than just having the user get that error message. 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 OrderID. We will check to see if that OrderID is set, or if it is null, and if it is null, then we will create one automatically with a little bit of VBA code - a tiny little bit. We will learn about the Before Insert event. Then I will teach you how to get a value from a subform, that total on the bottom down there, where the arrow is, that is on a subform. A little bit different trick, some different nomenclature, if you want to get a value from an open subform. It is a little more complicated, but I will show you how in the extended cut, and we will do a little tiny bit of error handling with an On Error GoTo statement. All that is covered in the extended cut for members. Silver members and up get access to all of my extended cut videos. I also cover a lot more with the Forms Form Name, Field Name Notation in my Access Expert 2 class. The full course, an 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, and lots of stuff in that class, Access Expert 2. How do you become a member? Click the Join button below the video. After you click the Join button, you will 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 TechHelp 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 TechHelp 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 do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked 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 will 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 would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it is 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 TechHelp page, and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the primary technique shown in this video for setting the default value of a combo box on a form?A. Using the Forms form name, field name notation to get a value from an open form B. Writing a complex VBA macro to copy values between forms C. Linking tables directly in the Relationships window D. Using referential integrity with cascade update Q2. What must be true for Forms!FormName!FieldName notation to work in referencing a value? A. The referenced form must be closed B. The referenced form must be open C. The query must be run in SQL Server D. The database must be compiled Q3. When assigning a default value from another form, which symbol is important for separating form and field names? A. Ampersand (&) B. Period (.) C. Exclamation point (!) D. Colon (:) Q4. If you want a value in a query to be set based on a control on another open form, where should you specify Forms!FormName!FieldName? A. In the Output field B. In the Criteria field C. In the Table name D. In the Group By field Q5. What does the video mention must be added in front of the default value in the combo box on the order form for it to work? A. A plus sign (+) B. An asterisk (*) C. An equals sign (=) D. A minus sign (-) Q6. What is the result if you reference a control on a form that is not currently open? A. An error message is displayed and Access crashes B. The reference returns a blank or null value C. The field displays an error code D. The database automatically opens the form Q7. When using a textbox as a state filter for queries, what was set as the default value in the example? A. NY B. CA C. FL D. TX Q8. Why must the user hit the Tab key after editing the filter textbox before running the query manually? A. To save the value to the form so the query can read it B. To refresh the lookup values C. To lock the textbox value D. To open the properties window Q9. What happens if you create order details without first creating an order (i.e., without an OrderID)? A. The order details are saved with a blank OrderID B. Order details cannot be saved and an error is displayed C. Access automatically assigns an OrderID D. Nothing unusual happens Q10. How did the video recommend preventing unlinked order details from being created? A. By setting the Required property to Yes and removing default values in the table design B. By hiding the order details subform C. By disabling data entry in the detail form D. By deleting all orphan records manually Q11. Which membership level grants access to extended cut TechHelp videos and live chat sessions? A. Gold B. Silver C. Platinum D. Basic (free) Q12. What additional trick is covered in the extended cut for members related to setting the OrderID? A. Using lookup tables instead of forms B. Automatically creating an OrderID using VBA and the Before Insert event C. Running update queries on a schedule D. Using SQL Server linked tables Q13. What is a benefit of using the button on the main menu to run the customer filter query instead of just changing the textbox and running the query manually? A. It automatically saves the textbox value by moving focus from the control B. It refreshes all linked tables automatically C. It resets all form settings to default values D. It prevents users from entering new state filters Q14. What does the exclamation point (!) in Forms!FormName!FieldName represent? A. Concatenation of two fields B. It separates the form or collection from the field or control C. Starting a formula D. Ending a statement Q15. If you want to get a value from a subform, what did the video say? A. It uses the same syntax as for main forms B. It requires different nomenclature and is more complicated C. It is not possible in Access D. You must use only macros, not VBA Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-B; 7-C; 8-A; 9-B; 10-A; 11-B; 12-B; 13-A; 14-B; 15-B DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone focuses on how you can retrieve a value from an open form using the Forms form name, field name notation in Microsoft Access databases.Cole asked about using my invoicing template. He wanted to know if it was possible to have the customer combo box set automatically to the currently open customer whenever a new invoice is created. This is a common question, and it can really improve the usability of forms when you need to pass data between them. Let me walk you through a similar example using my free TechHelp template, which is available for download on my website. In my invoicing video, when you open a customer and move into the orders section, there is an order entry system where you can create and print invoices. However, when you create a new order, the customer combo box is blank by default. It would be convenient if this field could pick up the customer currently selected on the main customer form. To achieve this, you need to retrieve a value from another open form in your database. Let me explain with an easier example first. Suppose you regularly need to run a query showing customers from a particular state, like Florida. Instead of always entering the state manually, you can place a text box on your main menu form with "FL" as the default value. Whenever you run your query, it will use the value in that text box. To set this up, you create a text box on your main menu form, label it as "state," and set its default value to "FL." Now, if you want your queries to use this value, you refer to it using the Forms collection in Access: Forms!MainMenuF!StateFilter. The "MainMenuF" is the name of my main menu form, and "StateFilter" is the name of the text box. In your query's criteria for the state field, you can use this exact reference. For example, in a customer query, you set the criteria for the State field to Forms!MainMenuF!StateFilter. Now, whenever you run the query, it filters customers by whatever state is entered in your form. You can still change the state in the text box and rerun the query to get results for another state. If you add a button to the form that runs the query, clicking the button will also update the results based on your current selection. This method of referencing open forms is very useful. The essential rule is that the source form must be open for Access to pull the value successfully. If it is not open, the criteria will not work. Now, back to the original question about setting a default value in the order form's combo box. When you create a new order, you want the combo box to show the CustomerID from the customer form that is currently open. To do this, go to design view on the combo box in the order form, open its properties, and set the Default Value property to =Forms!CustomerF!CustomerID. This sets the default value of the combo box to match the CustomerID from the open customer form. Again, make sure the customer form is open, or else this will not work, and the combo box will remain blank. There is a limitation to be aware of with this approach. When you first create a new order, there is no OrderID assigned yet. If you try to add order details (such as line items) before the order record itself is saved, you can end up with orphaned order details because no OrderID exists yet. To avoid this, you should enforce data integrity rules at the table level. Set the CustomerID in the orders table to Required and remove any default value of zero. Do the same for the OrderID in the order details table. This way, the database will not let users enter order details without a corresponding order and customer. With this enforced, if a user tries to add a line item before the order record exists, Access will prompt for the missing OrderID. The user will then need to initiate the order (perhaps by picking a customer or entering a description) to ensure a valid OrderID is assigned before adding details. While this works, it is not the most user-friendly solution. In today's Extended Cut, I show members how to create a much smoother experience. Instead of showing the user a confusing error, we use a little bit of VBA to automatically create the OrderID as soon as it is needed. This involves checking if the OrderID is present and, if not, assigning one before the user tries to add line items. I cover how to use the Before Insert event to handle this process. We also look at how to get values from subforms, which requires a slightly different approach, and introduce some basic error handling using On Error GoTo. If you would like more details about Forms' form name and field name notation, I cover it in depth in my Access Expert 2 class. That course also introduces topics like database normalization, relationships, referential integrity, and cascade updates or deletes. Members at the Silver level or higher get access to all of my extended cut TechHelp videos, live sessions, and more. Gold members also receive a download folder with all sample databases I use in these videos, as well as my Code Vault containing many useful functions. Platinum members get everything previously mentioned plus my full beginner and expert courses, not just for Access but also topics like Word, Excel, and Visual Basic. Rest assured, my TechHelp videos remain free for everyone. If you find these lessons helpful, please like, comment, and subscribe to my channel. Remember, you can find all related resources, links, and more information by expanding the Show More section below the video. If you want email notifications for new videos, you can join my mailing list. Also, my free Access Level 1 course shows all the basics of building databases and is available both on my website and YouTube channel. If you have a question you would like answered in an upcoming video, visit my TechHelp page and submit it there. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListGetting a value from another open form using Forms!FormName!FieldNameSetting a default value in a combo box based on an open form Creating a default value for a text box on a form Using Forms!FormName!FieldName as query criteria Building a parameter query using a value from an open form Creating a button to run a query that uses form criteria Configuring a combo box to default to the current record's value on another form Setting default values using expressions in property sheets Changing field requirements and default values at the table level Enforcing required fields in Access tables to prevent blank related records Troubleshooting issues when adding related records without required foreign keys Triggering record creation by interacting with fields to set IDs |
||||||||||||||||||||||||||||||||
|
| |||
| Keywords: TechHelp Access 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 value on a form PermaLink Value From a Form in Microsoft Access |
