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.
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 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
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"
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.