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.

• Progress Bar on Form
• Progress Meter
• Keyboard Shortcuts
• Find or Filter Data on Form
• Who is Online