In This Issue
	Tip of the Month The Magic of Self Updating Combo Boxes Behind the Scenes The Code and How it Works Trap of the Month

The Magic of Self Updating Combo Boxes

Combo boxes (some people call them dropdowns) make life easier for both Access users and Application Developers. These clever controls allow you to select from a predefined list. They help with data consistency since they can be configured to prevent entry of items that are not on the list. With combo boxes you typically take one of two paths. The first, and most common, is that the item must be on the list to be valid. This technique we'll explore in a future newsletter. This month we'll consider the second path: a list that's not necessarily known in the beginning, may constantly change, and a list that users must be able to add to. It's common for combo boxes to go to a lookup table of valid values. In this self-updating technique we'll look at the actual data table to determine valid values, and we'll make the combo box always return the contents of the current list. You'll want to use this technique only when you store the value that the user sees and selects - a true lookup list. If you're storing a key (typically hidden in column one of the combo box) then this is not the approach you'll want to take. Let's use customers and their cities for our example. At the start of a project, let's say that there is a defined list of cities. As new customers come along we need to expand the list to include new cities. If we use a standard lookup table then someone (or some code) must add to that list as needed. The technique I'll explain here is how we can have the list constantly update itself. Instead of using a lookup list and separate lookup table, we'll go to the customer table itself and select from there. To follow along I've included a sample file that you can pick up at the link below. It's an Access 2000 zipped file. Download, unzip, and then open the file. Self Updating Combo Box Demo »

Behind the Scenes

In the database there's one table (tblCustomer) and one form (frmDemoComboBox). If you open the form and click on the city combo box, you'll see that it lists only those cities that are in the city field of the Customer table. If you open the form in design view, you'll see that the combo box's row source points to the city field of tblCustomer. Only those cities which are in the customer table show up. To prevent duplicates I've set the unique values parameter (that's distinct to the SQL folks out there) of the query to true. To do this, start in the design view of the form, go to the properties of the city combo box (right-click on the combo - choose properties). Click in the row source, click on the ellipsis (...) to the right. This will bring you to the query behind the combo box. Right- click in the grey area of the query design window, click on properties and then select "Yes" next to unique values. In the form when you open the dropdown, it lists only those cities that are in the list. As the user types in that box, it will auto complete and the user needs to just hit return to select the city. If the user types something brand new it asks if the new value is intentional, accepts it as requested, and adds the record. The combo box then shows that new city as a valid city on the list. To show the new city immediately it uses only a single line of code in the form's On Current event (this fires every time you change records or when the form is refreshed or requeried.) Me.cboCity.Requery This line ensures that the combo box always presents the latest list of cities to the user. You want to add it in the design view of the form. Click on the grey button in the top left corner, choose View|Properties from the menu above, click on the On Current Event, click on the ellipsis choose code builder, then place that line in the code. If we stopped here we'd have a good technique; however it would have the drawback of not entirely avoiding the perils of misspellings. That can be handled with some code that fires on the Before Update event of the combo box.

The Code and How it Works

To add the code open the form in design view, click on the combo box, go to properties, click on the events tab, click on the ellipsis to the right of the before update event, and choose code builder. Here's the code, note that in this newsletter it doesn't wrap or indent correctly, so I've numbered the lines so you can get the idea: 1. Dim strCity As String 2. strCity = Me.cboCity 3. If DCount("City", "tblCustomer", "city = '" & strCity & "'") = 0 Then 4. If MsgBox(strCity & " is not currently in the City list, would you like to add it?", vbYesNo, "Add City?") = vbNo Then 5. Cancel = True 6. Me.cboCity.Dropdown 7. End If 8. End If Line 2 puts the value selected or typed into the variable strCity. On line 3 it determines whether or not the city is in the existing list of cities, if it's not, the code asks the user whether they want to use this city. If they see they've made a mistake they can back out (line 5) and choose from the existing list. You'll want to change the code of course to reflect your form and combo box. The critical lines to change are 2 and 6 to reflect the name of your combo box, and line 3 to include your table and field name. This self-updating technique has the advantages of keeping a current list of acceptable values, while allowing easy additions to the list with a minimum of code. This technique means that there's no further work to do once it gets rolling; so you can relax on the beach and enjoy the rest of your summer, maybe even take an extended vacation!

Trap of the Month

Access by default will correct your spelling mistakes. However sometimes what looks like a mistake is actually intentional. Say you have a person with the name "Harry Thier" and you notice that Access changes the name to "Harry Their" To fix this you have to turn off autocorrect for that control. To change the autocorrect open the form, go to the control, go to properties. On the Other tab, you'll see an Allow Autocorrect property, set this to No, and you'll be all set.