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.

---

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