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, may be 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 depends 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.

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 in 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 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 Category (we have imported the table) of items and they all got mixed up in that list.

    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 of 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 combo box, 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 into 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 with the size of others.
  13. Display the Form's Code Module. (View - - > 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
    Me.ProductID.Requery
    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 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 Category 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, CategoryID number corresponding to that item is recorded in the Combo Box control. We are using this number to filter the items that belongs to this category from the Products Table by building a SQL string and using the CategoryID Value as 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 field 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 are welcome.

Share:

5 comments:

  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.

    Sincerely,
    Bill Beck
    Project Mgr.
    The Best Of Blog Awards
    Email:Bloggerbeck@aol.com

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

    ReplyDelete
  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 [...]

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

    ReplyDelete

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter ByVal and ByRef Usage

Before taking up the above subject let us look at some fundamentals on variables for the benefit of novices. When we define a variable in VB...

Labels

Blog Archive

Recent Posts