How to Retrieve Any Column Value from a Combo Box or List Box in Microsoft Access

In this episode, we're going to look at how to get not just our bound values from Combo Boxes and List Boxes, but also how to get the values of any column on a selected row. This is an extremely handy method, as many programmers have "pre-loaded" many values into multi-column combos/listboxes. When on screen form or report calculations are required, they do not have to do a lookup on the database to get those same values.. .. they can just grab them from the on-screen combos/listboxes instead of having to do additional hits/queries on the database. You'll also watch me build a multi-column combo box and listbox from scratch in this episode, so you might like the techniques you find there as well.
Related Videos:
2 Ways to Create Command Buttons on MS Access Forms
• 2 Ways to Create Comma...
How to Make a Listbox in MS Access Part 1: Single-selection
• How to Make a Listbox ...
How to Create a Multi-Column Combo Box in MS Access, Adjust the Width and Bind it to the Table
• How to Create a Multi-...
How to Use Form and Subform Variables in Microsoft Access
• How to Use Form and Su...
How to Retrieve Any Column Value from a Combo Box or List Box in Microsoft Access
You are watching this video now!
How to Create a Subform in MS Access
• How to Create a Subfor...
How to Use the Tab Control in Access: Tabs with Subforms Example
• How to Use the Tab Con...
MS Access Option Groups - How to Use Radio Buttons
• MS Access Option Group...
How to Use Reference Expressions to Get Form Values in MS Access
• How to Use Reference E...
How to Use Nz in Microsoft Access to Handle Null and Empty Values
• How to Use Nz in Micro...
After Update on Form Controls in Microsoft Access
• After Update on Form C...
For developers looking for a new role, check out and sign up:
www.toptal.com/qKaO2b/worlds-...
Needing to hire technical resources for your project? Get the best:
www.toptal.com/qKaO2b/worlds-...
Want to get access to premium content made just for you and have a chat with me? Find me on Patreon :
/ mackenziedataengineering
Want to work with me 1:1? Contact me today and book your free 20 min consultation!
Contact form you can find at www.mackenziemackenzie.com/
Follow me on social media:
/ mackenziedataanalytics
/ seamacke
/ seamacke
/ seamacke
/ psmackenzie
Get Microsoft Office including Access:
click.linksynergy.com/fs-bin/...
Got a KZbin Channel? I use TubeBuddy, it is awesome. Give it a try:
www.tubebuddy.com/seanmackenz...
#msaccess #vba

[@neshaponesixty]

Thank you for this. It had never occurred to me that it was possible to reference the other unbound columns. As indicated in your video description, I was looking up values that were already available via the combo box.

[@seanmackenziedataengineering]

No more trips to the database! Just grab the value from the list/combo box. I remember when I discovered this, it made my life so much easier. Glad it worked for you!

[@ecco222]

This is great. Works very well for getting values from a table (via form list box) to use as query criteria without writing a custom function or running script to modify the query SQL. But I'm also confused now. Control.Column is a VBA property. It's not listed in the Expression Builder yet works fine in the Control Source property of a text box on a form. But it can't be used in query SQL. I guess that just means that VBA properties can be used in form controls' Property Sheets. I thought I was limited to what is in the Expression Builder.

[@seanmackenziedataengineering]

Thanks for sharing! That's pretty interesting. Even though I have called the Column property for years by just typing it in, I didn't know that it was not available in the Expression Builder. So, this is good information for any of you other viewers who are using the Expression Builder. If you need to use it in a query, give it a try by just typing it in the designer.

[@dalskiBo]

Thanks Sean

[@seanmackenziedataengineering]

Welcome!

[@mitchellfolbe8729]

Trying to make an tourney bracket using combo boxes. This is a good step. Thanks.

[@seanmackenziedataengineering]

Nice! Cool project

[@pathtolightbd]

Great tutorial as always!

[@seanmackenziedataengineering]

Thanks!

[@pathtolightbd]

@@seanmackenziedataengineering Thank you sir. Could you pls consider uploading a detailed video tutorial on using Access as front-end of SQL Server back-end.

[@user-dd9fk1ys9p]

I want to enter the monthly performance of the personnel in Access every month, of course, by uploading the Excel file, but I want the performance of each month to be in exactly one column and by using the personnel code, the performance of each person should be placed in front of him. Please use the combo box to select the month of performance. Thankful

[@seanmackenziedataengineering]

I think this one will help you: kzbin.info/www/bejne/mWWYn6xqq7Z6fNU

[@argieamora8716]

Good day Sir, I am looking to one of your videos on how to show records in a joined query with missing values from fields. I have three tables Activity, Participant, and Junction of these two tables. I want to show all activities attended by a certain participant. The query works if there is no missing value in the fields of the participant table. If the participant got a missing value(ex. last name) it will not return the activities attended by that certain participant. I hope you can spare some time for this problem. Thank you.

[@seanmackenziedataengineering]

Interesting problem! What fields did you use for your join? Can you post the SQL from the query? This problem will occur if you joined based on the name. If you have numeric ID columns and joined using those, it will be fine.

[@dbdata9836]

thanks

[@seanmackenziedataengineering]

Welcome!

[@digitalentrepreneur3126]

If I have a list box and want to use a update query to retrieve the values and update them to an existing cell. What criteria could I use?

[@seanmackenziedataengineering]

Update MyTable Set MyField = Forms!MyForm!MyListbox Where ID = Forms!MyForm!MyIDField; Or something like that. Or you could set the control source of the listbox to a field in the recordsource of your form.

[@digitalentrepreneur3126]

@@seanmackenziedataengineering Would the figues auto populate in the table after I run the query or I would have to manual select each option from a drop down list?

[@rogerio74c]

Hi Sean! I have a question. How do I retrieve a column value without using vba? I ask this because my intuition says it shoud be easy, but I haven´t found any way to do this without vba. Thanks

[@rogerio74c]

Hey, I thought you were going to use vba to retrieve the values. But on 13:14 you just anwered my question. Thanks a lot master!

[@seanmackenziedataengineering]

Nice! Yes you can use an expression just about anywhere. So, you could use it in controls and Access macros etc.

[@justinlangdon]

Sean, How do I store these values into my table?

[@seanmackenziedataengineering]

You can make an Append query that references the value(s). Then run the Append query and the values will be stored in your table. kzbin.info/www/bejne/apu0d4V6n5ZjnKs

[@justinlangdon]

Thanks Sean! It worked great!

[@dbdata9836]

great

[@seanmackenziedataengineering]

Thanks!