FrontPage Tips by Ward Cameron Enterprises
Take Your Web Site to New Levels

Creating a Pick-List in your Access Form

MS Access 2002 makes it very easy to create a drop-down box (combo box) that will allow you to select from a list of values and instantly go to the corresponding record. This tutorial will show you how to easily accomplish this.

Step 1 - Create the Pick List
Step 2 - Change the Sort Order
Step 3 - Troubleshooting Errors

Step 1 - Let's Get Started

To create a simple combo box that will synchronize your from with whatever value you select in the combo box, open your form in design mode. Now, within the form header, click the combo box icon on the tool bar and then click within the header areas. If the header is not visible, select Form Header/Footer from the View menu. You may also need to drag the form Detail pane down a bit to make some room in the Form Header area. For this example, we'll use the Northwind Database that is included with Microsoft Access.

Open the Suppliers Form and then change to design view. Drag the Detail Pane down a bit to make some room in the Form Header region. Your form should look like this:

Now click on the combo box tool (the one in the following graphic)

Click anywhere in the Form Header area  to open the Combo Box Wizard. As you move over the Form Header region, you'll notice that your mouse cursor has changed to the above symbol, along with a plus sign. This indicates the tool is selected.

In the first screen of the Combo Box Wizard, select "Find a record on my form based on the value I selected in my combo box" and click the Next button.

For this example, select CompanyName as the field to be used for the combo box. You could easily use any other field that you would like. Once you have selected CompanyName, click Next.

In Step 3 of the Combo Box Wizard, leave "Hide key column" selected and drag the Company Name field to make sure you can see all of your values. When everything looks good, click the Next button.

Finally, give the field a name. In this case, we'll call it "Find Company". Click Finish.

You'll now see the Find Company combo box placed with the word Unbound visible in the field. This simply means that the value is not being sent to (bound to) a particular table field. Simply put, you will not be storing the value, simply using it for a moment.

Save the form, and return to Form View. If you now click on the drop down arrow and select a company, your form will be instantly transported to that record. Alternatively, as you type, you will find the text will autofill with the value that closest represents what you are typing. Just hit enter to move to the correct value.

Changing the Sort Order

One of the things that this technique seems to lack is an easy way to make the list sort alphabetically or numerically, depending on the situation. Here's how to solve this. Go back to design mode and click on the combo box you have just created. Now Select View-Properties to open the Properties window.

The 7th line in the Properties window will be the Row Source. Currently, it will show the following value:

SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers;

To change the combo box so that it sorts alphabetically, delete the semi-colon at the end of the above code and add the following:

ORDER BY Suppliers.CompanyName

The entire line should now be:

SELECT Suppliers.SupplierID, Suppliers.CompanyName FROM Suppliers ORDER BY Suppliers.CompanyName

Switch back to Form View and you'll notice that your list is now sorted alphabetically.

Troubleshooting Potential Problems

There are certain situations where this procedure can have problems. Sometimes, when your create a list for picking names, the procedure will choke on names that contain apostrophes (like O'Darby). Suddenly, your lovely system comes up with a frightening error. It may be something like the following:

"Run-time error '3077', syntax error (missing operator) in expression"

Very Scary. There is usually a Debug button. Click it and you will see the screen where the actual code that makes the selection lives. It will look something like this:

Private Sub Combo44_AfterUpdate()
     ' Find the record that matches the control.
     Dim rs As Object

     Set rs = Me.Recordset.Clone
     rs.FindFirst "[LASTNAME] = '" & Me![Combo44] & "'"
     If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Look for the following line:

rs.FindFirst "[LASTNAME] = '" &  Me![Combo44] & "'"

The problem lies in the apostrophe after the = sign and the second apostrophe placed within quotes at the end of the line. Changing the code to the following will usually correct the error.

rs.FindFirst "[LASTNAME] = " & Chr(34) & Me![Combo44] & Chr(34)

Don't forget to remove the single apostrophe immediately after the = sign and to make sure the line does not end with any punctuation.