Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Refresh Dependant Combo Box Contents


Creating a Combo box on a Data Entry/Editing Screen is easy. Click on the Combo box Tool Button on the Toolbox, select the required Fields from the Source Table or Query on the Wizard, select the target field on the Form and you are through. Well, maybe not quite, few more changes may require on the Property Sheet of the combo box to adjust the width of individual Columns of List, Total List Width property, List Rows Property, Limit to List, and On Not in List Event Procedure to prevent entering values other than not defined on the combo box.

There are instances of more than one combo box on a form and it is likely that the contents of one combo box depend on the values of another one. In such situations limiting the contents of the second combo box with the data related to the current item selected on the first combo box is more appropriate, rather than displaying all of them always. The User can then select the item that he wants from a limited list instead of scrolling through a lengthy list to find the required one.

With few lines of Code on the On Click Event Procedure of the first combo box will do the trick and can make life easier for the user. He/She can do more work with the extra time otherwise spent on scrolling the combo box.

Preparing for the Test Run.

We need three Tables from the Northwind.mdb sample database for our test run. We are hitting this database more often to take ready-made data for our examples. If you are not sure where you can find this sample database on your PC, visit the Page Saving Data on Form not in Table for its location references.

Beginners of Microsoft Access can find plenty of sample Tutorial materials in this database to keep them engaged for some time and to learn the basics of database design. When you grow out of it or you want something different and more interesting then start hitting this site for help. I will be happy if you find time for a change of scene and drops in here for clues. I have plans to keep you occupied for a long time.

  1. Import the following Tables from the above sample database into your current project:
    • Categories
    • Products
    • Order Details
  2. Click on the Order Details Table, select Form from Insert Menu, and select Auto Form: Columnar from the Wizard, and click OK.
  3. Save the Form with the name Order Details.

    Before going for changes on the Form let us have a look at the contents appearing on the Form just now created. Open the Form in a normal view. There is already a Combo box on the Form for ProductID Field.

    Click on the Combo box and scroll down the list and read through some of the items appearing in the list, if you can understand them. To tell you the truth many of the names appearing in there are alien to me, especially the ones with one or more dots above the letters, besides others. But, one thing I am sure about is that they belong to different Categories (we have imported the table) of items and they all got mixed up in that list.

    Design Change of Form

    We will create another Combo box to organize and show them in a better way. We will now proceed with the design changes to the Form.

  4. Select Design View from View Menu, if you are still keeping the Form open in normal view. If you are not comfortable without a heading for your Form, expand the Header Section of the Form create a Label there and change the caption to Order Details and change the font size to make the heading look like a Heading.
  5. Select all the controls and labels except the OrderID control and Label, drag them down to make room to create a combo box below the OrderID field.

    You can select all the controls by clicking somewhere down on an empty area on the form, hold the mouse button down and drag over all the controls to select them except the OrderID field and label. When you are sure that you have covered all the controls and all of them are selected release the mouse button. Hover the mouse pointer over the selected controls to turn the mouse pointer to the image of a hand. Now click and hold the mouse pointer and drag them down to make room for another field and label above. We are going to create another Combo box with the source data from the Categories table.

  6. If the Toolbox is not visible select Toolbox from View Menu. Make sure the Control Wizard button (top right button) on the Toolbox is selected.
  7. Click on the Combo box button on the Toolbox and draw a Combo box above the ProductID combo box, about the same size as the ProductId combo box.
  8. Select the Categories Table from the displayed list and click Next.
  9. Click the >> button to select both fields displayed from the Categories Table and click Next, and Next again.
  10. On the displayed control ensure that the option Remember the Values for later use (in our Code) is selected and click Finish.

    We have not created a new field on the Order Details table to store the value from this Combobox when selected. That is not necessary here because we will take the Product Category Code inserted by the user into the Unbound Textbox (combo box) and use it to filter the contents of the Products combo box. The item selected from the Products combo box is only stored in the Order Details Table.

  11. Click on the combo box, display the Property Sheet (View - -> Properties) and change the Name property to cboCat.
  12. Change the size of the Label control to match the size of others.
  13. Display the Form's Code Module. (View - - > Code)

    The Form Class Module VBA Code

  14. Copy and paste the following code in the VBA Module:

    Private Sub cboCat_Click()
    Dim xsql0 As String, xsql2 As String, xsql As String
    Dim crit As String
    xsql0 = "SELECT DISTINCTROW [ProductID], " & "[ProductName] FROM Products WHERE ("
    xsql2 = " ORDER BY [ProductName];"
    crit = "[CategoryID] = " & cboCat & ") "
    xsql = xsql0 & crit & xsql2
    Me.ProductID.RowSource = xsql
    End Sub

  15. Save and close the VBA Window (File - -> Close and Return to Microsoft Access). Save the Order Details Form with the changes.
  16. Open the Form in the normal view. Click and select an item from the new Combo box, say Meat/Poultry.
  17. Now click the Products Combo box. The List of items appearing there belongs to the Meat/Poultry Category only. Experiment with other Categories of items also.

How it works.

Even though the Combo Box that we have created displays only one Column of Items (Description) we have selected two Fields CategoryID and CategoryName from the Categories Table through the Combo Box Wizard. If you display the property sheets of both Combo Boxes and look at the Column Widths property you can see that the value set for the first column width is zero in both cases, which will hide the CategoryID Code Number that we are selecting by clicking the Description. The Description is more important to the User and easy to understand which item he/she is selecting.

But, when a selection is made in the combo box, the CategoryID number corresponding to that item is recorded in the Combo Box control. We are using this number to filter the items that belong to this category from the Products Table by building a SQL string and using the CategoryID Value as the criterion. This SQL string is used as Row Source for the Products Combo Box.

If you open the Products Table and view the Data, you can see that Category Description is part of this Table also. When you click on one of the Category fields you can see that it is a Combo Box. Here also the same kind of Combo box is created (no filtering of data) while designing the Table Structure to display the Products Category from the Category Table.

Open the Products Table in Design View. Click on the CategoryID field and select the Lookup Tab from the Property Sheet displayed below. On the Property Sheet, you can see similar settings that we found on our Combo box on the Form. The CategoryID Source Data for the Combo box on the Products table is taken from the Category Table and Column width Property is set to zero to hide the numeric code and to display the Category Name instead.

NB: Any suggestions for improvement or showing a better method to arrive at the same results is welcome.



  1. Congratulations!!! You have been nominated for a 2007 Best Of Blog Award!! Especially designed to bring attention to lower profile bloggers, The BoB’s as we like to call them are currently taking nominations in over 20 different categories. To find out more about how your site has a chance to become one of this year’s Best Of’s and how to nominate other bloggers, visit us at www.thebestofblogs.com. Remember voting begins April 14th so make sure you pass the word to your friends, family, and faithful followers.

    Bill Beck
    Project Mgr.
    The Best Of Blog Awards

  2. [...] combo box, when selected. msaccesstips.com [...]

  3. [...] 1 Minute Ago Take a look at the following Article for guidance to build your own: Refresh Dependant Combobox Contents   http://www.msaccesstips.com Learn MS-Access Tips and Tricks Learn advanced Microsoft [...]

  4. It doesn't work! When you change the Category Name you get a 424 Run-time error. Object required


Comments subject to moderation before publishing.

MSA GURU : Access Tips & Tricks App

  • Download Android App 'MSA Guru' Version of LEARN MS-ACCESS TIPS AND TRICKS from Google Play Store.

Want to Post Free Ads on the Web


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.


Forms Functions How Tos MS-Access Security Reports msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Class Module External Links Queries Array msaccess reports Accesstips WithEvents msaccess tips Downloads Objects Menus and Toolbars Collection Object MsaccessLinks Process Controls Art Work Property msaccess How Tos Combo Boxes Dictionary Object Graph Charts Query VBA msaccessQuery Calculation Event List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form ImageList Control Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button ListView Control Report msaccess menus msaccessprocess security advanced Access Security Add Auto-Number Field Type Form Instances ImageList Item Macros Menus Nodes RaiseEvent Recordset Top Values Variables Wrapper Classes msaccess email progressmeter Access2007 Copy Excel Export Expression Fields Join Methods Microsoft Numbering System Records Security Split SubForm Table Tables Time Difference Utility WScript Workgroup database function msaccess wizards tutorial Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Common Controls Conditional Formatting Data Filtering Database Records Defining Pages Desktop Shortcuts Diagram Disk Dynamic Lookup Error Handler External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Message Box Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif ms-access msaccess msaccess alerts pdf files reference restore switch text toolbar updating upload vba code