How do I... Populate an Access list control with the results of a dynamic search?

This information is also available as a download that includes a sample Access database demonstrating the solution we'll build here.

Most applications have at least one search task. For instance, you might populate a form with product data by choosing the product's name from a combo box. That solution is simple to implement, but it's a bit limited. You won't always have the product's name. Alternatively, perhaps the customer online wants a rundown on all your chocolate products, not just one specific item. In this case, you might find it easier to populate a list control with the results of a query. You could use a subform, but list controls offer events that subforms don't. In addition, list controls are easier to reference in code. Last but not least, they look nice.

If you're an expert at Jet SQL, you can write a dynamic SQL statement that handles all the search possibilities your users might encounter. Fortunately, you don't need that level of expertise. You can use underlying queries instead. The tradeoff is that each search task requires a dedicated fixed query, but there's nothing wrong with using fixed queries. Access certainly doesn't care which solution you use, and you can still consider the results dynamic because the same control displays different results.

Create the search queries

The search form in Figure A populates the same list control with product information from three different searches. Each search has a dedicated fixed query. (You could just as easily use a combo box.)

Figure A: Use one list control to display the results of three different search tasks.

The first step is to decide what information you want to view in the list control. Each fixed query retrieves data from the same three tables from Northwind, the sample database that comes with Access: Products, Categories, and Suppliers. The basic SQL statement follows:

SELECT Products.ProductID, Products.ProductName, Suppliers.CompanyName, Categories.CategoryName, Products.QuantityPerUnit, Products.UnitPrice
FROM Categories
(Suppliers INNER JOIN Products ON Suppliers.SupplierID = Products.SupplierID)
ON Categories.CategoryID = Products.CategoryID
ORDER BY Products.ProductName
Create a query using this SQL and name it qryLookupAll, as shown in Figure B.

Figure B: Display all product records.

Copy the above query and add the criteria expression:

to the ProductID criteria cell, as shown in Figure C. Don't worry that the form and control don't exist yet. If you run the query at this point, Access will complain, but it will let you create the query. Name the query qryLookupID.

Figure C: The query gleans user input from a search form to filter its results.

Copy the first query a second time and add the following expression to the ProductName criteria cell, as shown in Figure D:
Like "*" & [Forms]![frmLookup]![txtDescription] & "*"

Name this query qryLookupDescription.

Figure D: This criteria expression creates a versatile search string.

Create the search form

The unbound form shown in Figure A performs three searches:

  • You can search by a product identification number, which is the best method if you know the value.
  • You can search by a string. This is helpful when you don't know a specific product's identification value or if you're looking for similar products.
  • The All button displays all the products. Users may have a need to review all the products if they can't find what they're looking for any other way.
Refer to Table A for all the form's properties and controls. Save the form as frmLookup.

Object Property Setting
form Caption Lookup Products
Scroll Bars Neither
Record Selectors No
Navigation Buttons No
text box Name txtID
text box Name txtDescription
command button Name cmdSearch
Caption Search
command button Name cmdClear
Caption Clear
command button Name cmdAll
Caption All
list box Name lstResults
Row Source Type Table/Query
Column Count 6
Column Heads Yes
Column Widths 0";2";2";1";1.5";0.5";0"
Bound Column 1

Table A: Search form controls and property settings.

With the form still in Design view, launch the Visual Basic Editor (VBE) by clicking the Code button on the Form Design toolbar. Enter the event procedures in Listing A through Listing F in the form's module. Return to Access and save the form. Listing A
Private Sub cmdAll_Click()
  'Display all records.
  lstResults.RowSource = "qryLookupAll"
End Sub
Listing B
Private Sub cmdClear_Click()
  'Clear controls.
  txtID.Enabled = True
  txtID = vbNullString
  txtDescription.Enabled = True
  txtDescription = vbNullString
  lstResults.RowSource = vbNullString
End Sub
Listing C
Private Sub cmdSearch_Click()
End Sub

Listing D
Private Sub Form_Load()
  txtID.Enabled = True
  txtDescription.Enabled = True
End Sub
Listing E
Private Sub txtDescription_AfterUpdate()
  'Populate list control by search string.
  txtID = vbNullString
  txtID.Enabled = False
  lstResults.RowSource = "qryLookupDescription"
End Sub
Listing F
Private Sub txtID_AfterUpdate()
  'Populate list control by ProductID value.
  txtDescription = vbNullString
  txtDescription.Enabled = False
  lstResults.RowSource = "qryLookupID"
End Sub

Use the search options

With the form in Form view, you're ready to search. If you know the product's ProductID value, the task is simple. For instance, if the user enters 3 in the Product ID control and presses Enter, the code disables the Description control and runs the query automatically. Alternately, the user can click Search. Either way, the txtID control's After Update event sets the list control's Row Source property to qryLookupID. Figure E shows the query's results in the list control.

Figure E: Search by a product's identification value.

Click Clear so you can search again. Search for all the products with chocolate in its name by entering choc in the Description control. This time, the code uses qryLookupDescription as the list control's Row Source property. That query's criteria expression concatenates the string you entered, choc, with two asterisk characters to create the search string *choc*. The list control displays the matching records -- those records that contain the string choc anywhere in the ProductName field, as shown in Figure F. As you might expect, clicking All displays a list of all the product records in the list control.

Figure F: Display records using a search string.

It only looks hard!

The sample form is for searching, but this type of dynamic search can do much more. If you're building a quote or order, use the list control's Click or Double Click event to copy record(s) to a temporary table. It's easy to implement using a fixed query for each search task.

Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is Mastering Microsoft SQL Server 2005 Express, with Mike Gunderloy, published by Sybex. Other collaborations with Gunderloy are Automating Microsoft Access 2003 with VBA, Upgrader's Guide to Microsoft Office System 2003, ICDL Exam Cram 2, and Absolute Beginner's Guide to Microsoft Access 2003, all published by Que. Currently, Susan volunteers as the Publications Director for Database Advisors. You can reach her at