databasedev.co.uk - database solutions and downloads for microsoft access

Microsoft Access List Box

Searching Records using a Microsoft Access List Box

In an earlier article, we discussed Using a Microsoft Access Listbox to pass criteria to a query and also discussed the reasons and benefits of using List boxes. The obvious benefit is that you can allow the user to pick multiple records to then do something with.

In this article, we demonstrate how we can use a list box to pass criteria through to a search, allowing us to open a form (in this case) and return a specified result. This is similar to using a combo box to perform the same action.

Create the List Box Search Form:

Initially we need to create the form, this will include the unbound list box which will hold the results that we will base the search on. The form should also include a search button and a cancel button.

Below shows how the form should appear:

The form containing the unbound listbox, show record and cancel button
The form containing the unbound listbox, show record and cancel button

The listbox gathers it's data using a row source type of Table/Query and is based upon the following statement:

SELECT tblSalespersonContact.strSalespersonID,
            tblSalespersonContact.strSalespersonFirstName,
            tblSalespersonContact.strSalespersonLastName,
            tblSalespersonContact.strRegion
FROM tblSalespersonContact
ORDER BY tblSalespersonContact.strSalespersonID;

This populates the listbox with the values that you can see. Although the listbox only shows three columns rather than the four defined in the SQL statement, this is due to the column width property of the listbox being produced as follows:

0cm;3.995cm;3.995cm;3.995cm - this hides the first column (strSalespersonID) as we do not need to see this.

You will also see the Show Record command button. This is what can be used to run the search (as well as being able to double click on the record in the list), and you will note that this is disabled whilst a record is not selected. To set this property, the button has it's Enabled property set to NO. It is a good idea to disable this button until a user selects a value from the list to search on.

To enable this button, the following code is added to the List Boxes (lstSearch) After Update event:

Private Sub lstSearch_AfterUpdate()
'Once a record is selected in the list, enable the showRecord button
    ShowRecord.Enabled = True
End Sub

Once a value is selected in the list, the button then becomes active:

Show Record button enabled after selection of value from the list
Show Record button enabled after selection of value from the list

Once the user has selected a value from the list, they can either click on the Show Record button or Double Click on the record in the list. This action will run the following code:

Show Record Button:

Private Sub ShowRecord_Click()
'Find a selected record, then close the search dialog box

    DoCmd.OpenForm "frmSalespersonContact", , , _
                   "[tblSalespersonContact.strSalespersonID]=" & _
                   "'" & Me.lstSearch.Column(0) & "'"

    'Close the dialog box
    DoCmd.Close acForm, "frmListBoxSearch"

End Sub

This routine will open the new form (frmSalespersonContact) where the SalespersonID (strSalespersonID) mathces the ID of the value chosen in the list box (Me.lstSearch.Column(0)). This column is the hidden column as detailed above.

Once the new form is displayed the Search form will close: DoCmd.Close acForm, "frmListBoxSeach"

Double-Click Method:

Private Sub lstSearch_DblClick(Cancel As Integer)
'If the user double-clicks in the list, act as though
'the ShowRecord button was clicked
    If Not IsNull(lstSearch) Then
        ShowRecord_Click
    End If
End Sub

This routine checks to make sure that a value is selected in the list box (If Not IsNull (lstSearch)). Obviously there must be a record selected to allow the user to double click, then it will run the ShowRecord_Click event procedure detailed above.

To see this List Box Search in action please download the Microsoft Access 2000 example file from either the Microsoft Access Forms page or Microsoft Access Downloads menu.