Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Refresh Dependant Combo Box Contents

Introduction.

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 in the first one 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 long list of items to find the required one.

A few lines of Code in 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.

Microsoft Access new users 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 visit 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, 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 in 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 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.

    Change The Form Design.

    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, and 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, holding the mouse button down, and dragging 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 into 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 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 a 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 the 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

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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 ListView Control Query VBA msaccessQuery Calculation Event Graph Charts ImageList Control List Boxes TreeView Control Command Buttons Controls Data Emails and Alerts Form Custom Functions Custom Wizards DOS Commands Data Type Key Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Command Button 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