|Microsoft Office Access 2003
Microsoft Access 2002
When you need to give users a list of choices, you typically provide those choices in a combo box control or a list box control. Most Access developers use combo boxes because they're more versatile — they allow users to enter text or select an item from a list. If users need to select items from one list and assign them to another, then you use list box controls.
This article explains how to add items (choices) to combo boxes and list boxes programmatically by using Microsoft Visual Basic® for Applications (VBA) code. If you're not a programmer, and you need introductory information about creating combo and list boxes, see The basics of creating combo boxes and list boxes at the end of this article.
Suggested uses for this code sample
You can use this code sample as part of creating a custom print dialog box. You can also use it as part of a custom settings dialog box for a given database, something like the Options dialog box that you start from the Tools menu.
The steps in the following sections explain how to add a list of installed printers to a combo box control by using VBA code. These steps use a combo box control because it provides what most computer users think of as "standard" list behavior — a downward pointing arrow and a list that appears when you click the box. If you'd like, you can repeat these steps with a list box control to see how each control behaves.
The process takes place in two stages. First, you create a user form, add a combo box control to the form, and then set the properties for that control. After that, you start the Visual Basic Editor and add a block of code.
Create the form
- Start Access, open a new, blank database, and create a new user form.
- On the File menu, click New, and then click Blank database.
- On the Objects bar of the the Database window, click Forms, and then double-click Create form in Design view.
Note The Toolbox should also appear. If it doesn't, click the Toolbox button on the main toolbar .
- In Toolbox, clear the Control Wizards button , select the Combo Box control , and place a new combo box on the blank user form.
- Using your mouse, select a location for the upper left corner of the combo box.
- Click once to place a control of standard size onto the form, or click and drag to add a larger control to the form.
- Right-click the control, click Properties, and then click the All tab on the properties sheet.
- Click the field next to the Name property and type cmbPrinter, click the field next to the Row Source Type property and select Value List, and then click the field next to the Before Update property and select [EventProcedure].
- Click the ellipsis button (...) next to the Before Update property. The Visual Basic Editor starts, and displays an empty subroutine. Go on to the next set of steps.
Connect the form to the code
- Copy the following block of code and paste it into the code window below the empty subroutine:
Private Sub Form_Load()
Dim prt As Printer
For Each prt In Application.Printers
' Use the new AddItem method to add the printer name
' to the list box.
- Click Save . This saves the code module and the form.
- Close the Visual Basic Editor.
- In Access, close the property sheet for the combo box.
- At this point, you can either click Open on the Database window toolbar , or you can close and reopen the form to display it.
- Click the down arrow in the combo box and select a printer from the list.
- When you load items into a combo box programmatically, Access does not sort the items. Instead, Access loads the items in the order received. If you want to correct that behavior, see the following Microsoft Knowledge Base article: Programmatically populated combo box or list box is not sorted in the order expected.
- By default, the combo box remains empty until you select an item from the list.
- If you use a list box control, then by default, it takes up more space in the form than a combo box control. You may need to adjust the size of the control to make it easy to use.
A brief introduction to combo boxes and list boxes
Combo boxes and list boxes provide provide users with lists of choices, but the controls have some important differences.
- You use combo boxes when users need to enter text or select an item from the list. You can also use combo boxes as "type ahead" lists, or record selectors, that allow users to type in part of the name of an item and see a list of all items that match what the user typed. Many Access developers prefer to use combo boxes because they're more versatile.
- You use list boxes when you need to create paired list boxes. For example, if you've used the Access Form Wizard, you've used paired list boxes:
Note List box controls contain a property called Multi Select. When you enable this property, users can select multiple items from a list. While you wouldn't normally allow users to select multiple printers, the Multi Select property can be useful in other circumstances. For more information about Multi Select, see Multi Select Property on the Microsoft Developer Network.
The basics of creating combo boxes and list boxes
If you're new to Access, these links take you to basic information about creating and using combo boxes, list boxes, and user forms.
Creating list boxes and combo boxes
- Create a list box, combo box, or drop-down list box
- Customize a list box, combo box, or drop-down list box
- Find a record by selecting a value from a list
- Properties of list boxes, combo boxes, drop-down list boxes, and Lookup fields
- Specify which row is the default value in a list box or combo box
- About creating a list box, combo box, or drop-down list box that looks up values
- Top tips for Access: Forms, reports, and controls
Creating user forms