List Items Set Value
By Richard Rost
4 years ago
Set Return Value After Using List Items Edit Form
In this Microsoft Access tutorial, I'm going to show you how to return the value that was just added or edited to the combo box you called the List Items Edit Form with, so that you don't have to scroll through the whole box to find it.
Willis from Juno Beach, Florida (a Platinum Member) asks: I love the video you did last week on the List Items Edit Form, however, is there anyway to get that new item you've just entered and set that value in the combo box you came from? As it is now, I have to type in a new value, close the Edit Form, and then scroll through the combo box to find it.
Members
Members will learn how to return a value to multiple calling forms. We'll add a default shipping method to our customer form, and you can now use both that and the order form to call the ShippingF List Items Edit Form and it will know where to return the value.
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!
 Links
Suggested Courses
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, list items edit form set value, on unload event, on close event, default shipping method, return value to combo box, ms access list items edit form return value, editing value lists
Intro In this video, I will show you how to set the return value in a combo box after using the list items edit form in Microsoft Access. You will learn how to use a little bit of VBA code to automatically select the new value you add to the list so it appears in the combo box without extra steps. I will explain the difference between form events like On Close and On Unload, and guide you through adding this functionality step by step.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to set the return value in a combo box after using the list items edit form in Microsoft Access.
Today's question comes from Willis in Juno Beach, Florida, one of my Platinum members. This is on the other side of the state. I'm on the west coast of Florida, Fort Myers. Juno Beach is over on the east coast side. So we're kind of neighbors, and there's your Florida geography lesson for the day.
Willis says, I love the video you did last week on the list items edit form. However, is there any way to get that new item you've just entered and set that value in the combo box you came from? As it is now, I have to type in a new value, close the edit form, and then scroll through the combo box to find it.
Well, yes, Willis, we can definitely do this. It's going to involve one line of programming, and I'll show you exactly where to put it. But first, for the rest of you, if you have not yet watched this Fast Tips video, go watch it right now. Pause this video and go watch that one.
A list items edit form is basically when you're working with a combo box. If you want to add another item, like let's say a shipping method, you don't have to close the order form and then go back to the shipping form and add it there, and then close that and then go back. You can do it all right from inside the combo box, and that Fast Tips video explains how to do it.
But the problem we have is if we come in here and we add someone new, let's go to the list items edit box. If I add something new in here, let's say ABC Shipping. If I close this form, it'd be nice if that populated right in that box instead of still having to drop this down, open it up, and find it in here.
So Willis wants to know if there is a way we can just take that new value and drop it right inside the combo box. And yes, we can with one line of code. Let me show you how.
First off, if you've never done any VBA programming before, go watch my Intro to VBA video right now. It's about 20 minutes long. It teaches you all the basics. Don't be scared; VBA is not scary. I'll walk you through it step by step. But it would be helpful if you watched this video first before continuing on.
So how are we going to do this? Well, what basically happens is this button here loads up this form, which is modal. You can't click behind it. So we have a new value, and then when this closes, it requeries this combo box for you, so the value shows up there, but it doesn't select it. Now we can select it with a little code.
How do we do that? Well, let's go to the original form. Find our shipping F over here. Go to Design View. Now there's a couple of events that fire when you close a form. There's On Close and there's On Unload, and they're very similar with a tiny bit of a difference.
On Unload actually runs first, but before the form is actually exited. In other words, you still have access to all of the information on that form, including the fields. Whereas On Close is already basically saying, okay, I just closed the form. I don't know what was on it. It's too late, but you can still do something else. But by that point, it's too late. We can't grab a value from it.
So we're going to use the On Unload event. Another benefit of On Unload is you can cancel it if you want to. We're going to go to On Unload, hit dot dot dot. Now if you watch my Intro to VBA, you'll know that this window pops up in the VBA window. If you get asked what kind of builder you want, pick the Code Builder, which I explained in the Intro to VBA.
Now right here, the form Unload event is going to run when you've indicated to Access, hey, I want to close this form now. But you can do other stuff in here and check things and set values before it actually closes, and you can optionally cancel if you want, which we're not going to do. But this is the perfect spot to set the value on the previous form to whatever value we have on this form.
Now the other form is Forms!OrderF. And what's the field over there? We want to set ShippingCombo, and we're going to set that equal to whatever the ShippingID is on this form.
Even though the ShippingID field isn't physically on this - where did you go? Oh, it's right here. Even though the ShippingID isn't on here, we can still use its value because it's in the record set underneath, which is ShippingT.
So when we close this form, I want you to set the shipping combo box on OrderF equal to whatever the current ShippingID is. So you have to make sure you stay on that record when you close the form.
Let's go over here and test it. Ready? All right. Drop this down. Now, go to the edit form. Here it is. Now if you just pick something like FedEx and close this, it'll take that value and stick it in that combo box.
There's a side effect you can get here. I like to close this because when you open up a modal form, it shuts the navigation pane. If I open this up and go to the shipping form, I'm going to bring this back over here where it was. Let's see if I can grab it. There we go.
If I pick Starfleet Courier and close this, boom. There it goes. So we can also now add an item. XYZ Corp. And as long as you stay on that field - don't hit Tab, don't go to the next one - close this, and it pops XYZ Corp in there. See that?
If you do by mistake, type XYZ Corp and hit Tab and close it, you're going to set that to null because this guy was sitting on null. That's a null value.
So if you type someone in, like Rush Shipping Company, and then close it, boom. There's your Rush Shipping Company. Isn't that cool? See what I mean? Folks, with just one line of code, we added a whole new level of functionality to this database.
Now, here's another thing for you. What happens if I want to use that same shipping edit form for a different form? What if I want to add a default shipping method to my customers, so that for each customer, I can pick, okay, Pony Express is my default shipping method, and I want to be able to use the same form here to edit my list of shipping options?
This form is specifically calling and returning its value to the order form. So how do I get it to know which form called it and send the value back there? We'll cover that in the extended cut for the members. So members, we'll use one edit form with multiple calling forms. So whether it's your order form or your customer form, it's going to open up the same list items edit form with the shipping information and know where to return it to the calling combo box. That'll be covered in the extended cut for the members.
Silver members and up get access to all of my extended cut videos. Gold members can download these databases and get access to my Code Vault. If you want to learn more about list items edit forms, check out my Access Beginner 8 and Access Expert 4 classes.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more. Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my Code Vault where I keep tons of different functions that I use. You'll also get a higher priority if you decide to submit any TechHelp questions to me, and you'll get one free expert class each month after you've finished the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full length courses found on my website, not just for Access too. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What is the main problem Willis wants to solve with the combo box and the list items edit form?
A. Automatically set the newly added item as the selected value in the combo box
B. Prevent users from adding duplicate items in the combo box
C. Lock the combo box after editing the item list
D. Require a password to add new items
Q2. What is the benefit of using the On Unload event instead of the On Close event when setting the value in the combo box?
A. On Unload runs after the form has closed, preventing errors
B. On Unload allows access to the form's data before it has closed
C. On Close is faster than On Unload
D. On Close requires less programming
Q3. What is the main functionality added to the database with just one line of code as described in the video?
A. Refreshing the entire database after closing the edit form
B. Setting the combo box's value to the record just added or selected
C. Deleting an unwanted item from the combo box
D. Printing a report of all combo box items
Q4. When closing the list items edit form, what must you make sure to do for the correct value to be set in the combo box?
A. Always go to the next record before closing
B. Stay on the desired record when closing the form
C. Click the Save button twice before closing
D. Minimize the form before closing
Q5. What happens if you type a new value and then hit Tab before closing the edit form?
A. The combo box will set to the previous value
B. The combo box receives a null value
C. The form will not close
D. The combo box will select the first item
Q6. How can you reuse the list items edit form for different forms (e.g., Orders and Customers) and return values to the correct combo box?
A. Create a separate edit form for each calling form
B. The method for this is covered in the extended cut for members
C. It is not possible to reuse the form in this way
D. Use a macro to copy values between forms
Q7. What must you do in the VBA window to connect your code to the On Unload event?
A. Select the "Macro Builder" option
B. Write code in a separate module
C. Choose "Code Builder" and enter your code in the event procedure
D. Enter your code in the On Load event
Q8. Why do you not need to have the ShippingID field visible on the edit form for the code to work?
A. The combo box value is not important
B. The field does not exist in the table
C. The value is available in the form's recordset underneath
D. You must always show all fields
Q9. What is a modal form in Microsoft Access as described in the video?
A. A form that allows you to click behind it
B. A form that locks all other forms until it is closed
C. A form that maximizes automatically
D. A form without any data controls
Q10. What is one of the perks of being a Gold member at AccessLearningZone.com?
A. Access to all beginner courses in every subject
B. Ability to download sample databases and access to the Code Vault
C. Free merchandise with every membership
D. Unlimited one-on-one coaching
Answers: 1-A; 2-B; 3-B; 4-B; 5-B; 6-B; 7-C; 8-C; 9-B; 10-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.Summary Today's video from Access Learning Zone focuses on a common issue that comes up when using the list items edit form in Microsoft Access. I am Richard Rost, your instructor, and I will be showing you how to automatically set the selected value in a combo box to the new item you have just added using a list items edit form.
The question for today is about improving the user experience in a form that allows users to edit the choices available in a combo box. Specifically, after adding a new entry using the edit form, is there a way to have that new value immediately appear as the selected item in the combo box on the original form, rather than making the user go back and search for it? As it turns out, yes, you can absolutely do this with a single line of VBA code.
Before I walk through the solution, it's important to understand what a list items edit form does. When using a combo box in a form, let's say for selecting a shipping method, the list items edit form allows you to add new options without having to leave the main order entry form. Instead of navigating out to a separate table or form to add the shipping method, you can do it directly through the combo box using the edit feature.
However, the current behavior leaves something to be desired. If, for example, you add a new shipping company like ABC Shipping, after you close the edit form the new value is present in the list but is not automatically selected. You still need to locate and pick it manually. The solution is to use a bit of VBA to have Access select that new entry for you.
If you are new to VBA programming, I recommend watching my Intro to VBA video. It only takes about 20 minutes and covers all the foundational concepts you will need. Don't worry if you have no programming experience; I will guide you through the necessary steps.
Here is how the process works. When you press the button to edit the list, it opens up a modal form. While this window is open, you cannot click behind it, which ensures that you handle the new value right away. Once you close this modal form after adding a new entry, the combo box on your original form is refreshed with the new list. However, by default, it does not select your new entry. This is where our code comes into play.
To set this up, you need to go to the design view of your edit form and add some VBA to the Unload event of the form. Access provides two main events when closing a form: On Close and On Unload. The key difference between these is that On Unload occurs before the form actually closes and still has access to all of the form's data, while On Close is fired as the form is finishing closing and no longer has that access. To achieve our goal of passing the new value back and selecting it, we use the On Unload event.
In the On Unload event procedure, you can tell Access to set the value of the combo box on your calling form (for example, the ShippingCombo box on OrderF) to the value of the newly created ShippingID from the current record in your edit form. It does not matter if the ShippingID field is not visibly on the form, since the value is still available in the underlying data.
When you implement this, and close the edit form after adding a record, the combo box on the original form instantly updates to show the new value as the selected option. The important thing is to make sure you have not moved away from the newly added record before closing the form, or else you might not pass the correct value. If you accidentally move to a new record before closing, you could inadvertently set the combo box to a null value.
It is a very effective way to enhance your forms with very little code required. With just one line in the right place, you can make your database much more user-friendly.
Now, you might be wondering if you can use the same edit form for other combo boxes in different forms. For example, perhaps you also want to manage default shipping methods for customers using the same edit form. In today's Extended Cut, I will show members how to have a single edit form know which form called it, so it can return the selected value to the correct combo box, regardless of which form launched it.
If you would like to learn more, my Silver members and above get access to all of my extended cut videos, while Gold members can download the sample databases and access my Code Vault with ready-to-use functions. All membership levels come with their own perks, including higher priority for questions and access to more courses for higher levels. These benefits can help you expand your knowledge in Access and beyond, with courses available for Word, Excel, Visual Basic, and other topics.
For more details, you can find step-by-step instructions covering everything discussed here, as well as the complete video tutorial, on my website at the link below.
Live long and prosper, my friends.Topic List Setting combo box return value after using list items edit form
Understanding list items edit forms in Access
Using modal forms to edit combo box lists
Requerying a combo box after closing the edit form
Difference between On Close and On Unload form events
Placing code in the On Unload event to update combo box
Referencing form controls and fields in VBA
Ensuring the correct record value is returned to combo box
Handling null values when closing the edit form
Testing the value return functionality in live forms |
