Combo Box

Combo Boxes

Combo Boxes are the most fascinating and useful “Controls” you can have on your Form. They have Many uses. Combo Boxes can be manipulated in all sorts of ways without using VBA. Once you’ve mastered the combo-box, and once you have mastered Queries, the building of queries in the query builder grid, a Query you have designed to provide your Combo-Box with a unique set of Records, then it’s a short step to becoming a VBA programmer. One of the most powerful things you can do with VBA is control what your Combo Box does. You can add fantastic and clever features to your Form with minimal VBA, and minimal effort! It is a small stepping stone, on the way to becoming a VBA programmer. The first step, learn the inside outs of Combo Boxes. Learn how to make queries in the query builder grid, and then you will be ready! You will be chomping at the bit to take that Next step into VBA!

Combo Boxes - In the Beginning

The Most Basic Combo Box

Video 1 (4:45)

In this video I show the creation of a very simple combobox. The combobox is unbound and stores it’s values within itself in a “Value List”. I demonstrate creating a combo box both with and without the control wizard. I demonstrate enabling the ability for the user to add new values to the Combobox list. I demonstrate how to stop the user changing values. I demonstrate how you can allow the user to type “Free Text” – anything they like in the combobox.

Video 1 (4:45)

The Following links take you to key places in the video:-

00:30 select a control from the designer tab

00:33 add a combobox with wizard support

00:45 switch the wizard off

00:57 add a combo box without wizard support

01:15 using the wizard; type in the values you want

01:55 the combobox is “unbound”

02:25 allow free typing entries

02:45 set “Limit to List” to Yes

03:00 ask user if they want typed value saved to the list

03:17 shows the item added to the list

03:37 property sheet displaying the values in the “Value List”

03:47 set “Allow Value List Edits” to “NO”

04:12 the “Agile Programming Method”

… …

Get Info From a Combo-box

Get Info From a Combo-box

Video 2 (5:50)

This video shows how to display information from a Combo Box in a text box. Consider a combo box in which you select the customer’s name, then the “After Update Event” causes the address details for that customer to be displayed on the form in text boxes…

Video 2 (5:50)

… …

Form with Combo Box Lookup

Form with Combo Box Lookup

Video 3 (4:56)

Combo boxes are one of the most difficult things to understand. In this video I hope I make them a bit clearer.

Video 3 (4:56)

… …

Cascading Combo Box

Cascading Combo Box

Video 4 (10:19)

This video shows you how to change the Row Source of the “cascade” combo, with an SQL Statement and the after update event of the Selection combobox

Video 4 (10:19)

… …

Combobox - Change Query Criteria

Change Query Criteria with a Combobox

Video 5 (12:26) - 4-Min Excerpt

Demonstration of how to set up a Combo-box and a subform. This demonstration uses the older version of the Northwind Database. The Combo is Shown restricting the records displayed in the subform. This method utilises the Query that the subform is based on. The Selection in the Combo Box provides the Criteria for the Query.  This method requires the Subform/Subreport Control be refreshed, the method employed is demonstrated in the video.. Nifty Access is Here to  help Establish you as the “Go To” person in your organisation for database improvements! Nifty Access – will quickly elevate you to “Power User Level!”

Video 5 (12:26) - 4-Min Excerpt

More Info on th Northwind Sample dB:-

… …

Combobox - Displaying a Number?

Combobox - Displaying a Number?

Video 6 (7:17) - 5-Min Excerpt

Your combobox is displaying a number instead of text? In this video I demonstrate one way you can fix this problem…

I recently answered a question on Access World Forums HERE:- Report only shows Combo box primary key instead of text — I referred the OP to this web page, and Video No# 6. I also so wrote a text explanation on AWF, and I have copied it here because I think it will prove useful to anyone with similar problem. In Video No# 6, I demonstrate this technique on a combo box on a form. However, the Access World Forums (AWF) Question was the same problem, but on a report. The solution is the same, the Report ComboBox needs to reference the information in the Customer table by changing the Combo’s properties slightly… The following is my text explanation to accompany the video explanation:-

Video 6 (7:17) - 5-Min Excerpt

Combobox - Displaying a Number?

Good practice is to store information in the smallest possible format. This is normally achieved by storing "repeating information" in a table. Each row in the table is identified by a unique identity an "ID field"… For example you could have a table which stores customer information, it might store:- salutation; surname; first name; address; telephone number. Each row of "information" would have a unique ID. 

When you want to use this information somewhere else, let's say you want to provide the customer details for an orders table, in the orders table you only need one field to identify the customer. You store the ID from the customer table in this field. But that's not a very user friendly method because the user is never going to know which customer any particular number refers to. This is easily solved by using a combo-box as effectively a up look-up agent.

When you create your "Orders Form" based on your orders table, it will very likely create a text-box for the customer field. As suggested this text box will just display the number relating to the customer. However if you change this text box into a combo box, you can do this by right clicking on it, then selecting the third option in the list "Change To" now you have a combo box. It's always a good idea to follow a naming convention so immediately find the combo box name in the property sheet under the tab "Other" and prefix it with "cbo".

Also the Combo Box default name might not mean much, and may contain spaces. Remove the spaces and rename it something meaningful to your programming processes. Now whilst you've got the property sheet open, select the data tab, select the ellipsis (…) at the end of the "Row Source" property and you will be taken to the query building tool. Select a Table, in this case you want the Customers Table. Select the customer ID field and the customer name field so they appear next to each other in the query builder grid. You need a minimum of two Fields, the ID field and the customer name field. You may want other Fields, for instance you may have a Boolean field to flag a discontinued customer. Select this field and set the criteria option to "False". Now discontinued customers will not show in your results. You don't want to see the "Boolean field" in the combo-box so make sure the "Show" checkbox is unchecked.

Close the query builder Grid and re-open the property sheet for the combo-box as we've got just a couple more things to check/set-up. On the data tab make sure that the "bound column" property is set to 1 and "limit to list" is set to true (Yes)… Select the format tab and set the column count property to 2, and the column widths property add the Text "0, 2" … 

The "bound column" is the column where the data from the combo box will be taken and fed back into your orders table, and you want the customer ID which will be bound column 1, the first, the left most part of the SQL/Query, in this case the ID. The column width property of the Combo Box is a way of specifying what actually appears in the actual combo-box, is Visible, is what the user sees. In this case we have have query which returns two columns, the ID and the customer name. But we don't want to see the ID, we just want to see the customer name. To do this set the "column widths" to 0; 2 — This shows Nothing of the ID, but 2cm of the customer name. Some versions of MS Access will not show centimetres but inches so the figures might be slightly different for your version of MS Access.

There are several other properties you can change for a combobox, however these property's I have explain here are the basics and you will seldom find yourself changing anything else….

… …

Combobox - Show Country & Region

Combobox - Country & Region

This DOWNLOAD example is in answer to a particular Question on Access World Forums (AWF) – The OP wanted to enter the name of a country into a text-box on an MS Access Form and then have a combo-box to display the continent/region? related to that selection. I suggested an alternative method, using a combo box to select only the Country and have the continent/region extracted from the ComboBox and displayed in a text box. I used a data-set I downloaded (Excel Format) from the internet to create the entries in the table for this MS Access demonstration from this URL here:- StatvisionThe information for the Microsoft Excel file was created from information provided by this website here:- gsociology.icaap.org You can download the MS Access example from the Nifty Access Digital Downloads Website here:- Country / Region Combo Box Example.

… …

Leave a comment

This website uses third-party software - WordPress Add-Ins to be exact. I don't know what any individual add-in does, but I'm sure that many of them collect information about you. So be aware, if you continue using this site, then you are likely to be sharing your information. I don't know how to disable this sharing for any, or all of the plugins for individual users. So I can't stop the sharing of information. If this worries you then please do not use this site... If you continue to use this site I will assume that you are happy with it.

Do you need a hand in the right direction?

You are in the right place.