Using a Combo Box to Search for a Record

In this tip we are going to look at how we can use a Combo Box control on a form to search for a particular record.  It works by clicking an item from the Combo Box's drop down list.  This activates a Visual Basic for Applications (VBA) Procedure using the forms ON CHANGE Event. Once the code executes, the form seamlessly displays the selected record.

Figure 1: Products Form with a Combo Box Search facility.  Selecting an item
searches for the relevant records and displays in on the form.

Lets begin by creating our Combo Box from scratch.

Create Combo Box from Scratch

1. Open your form in Design View.
2. Ensure the Wizard icon is deactivated.  If it is, just click the Wizard Icon so that it is no longer highlighted.
3. Click the Combo Box Icon.
4. When the Mouse Pointer changes to the Add Combo Box Symbol, click an area on the Form Design Grid where you would like it to go.

You now have a empty Combo Box Control on your form.  If you wish, you can resize the box and add some text to the label.  The next task is for us to enter the ROW SOURCE property.  This is the source of the data which will appear in the Combo Box's drop down list (take care not to confuse this property with the combo box CONTROL SOURCE, as we are going to keep our control unbound).

The Row Source Property

1. Select the Combo Box Control by clicking it with the mouse.
2. Click the PROPERTY SHEET icon. This brings up the Combo Box's PROPERTY SHEET.
   
3. Select the DATA tab.
4. Enter the ROW SOURCE property.

When you  enter the Row Source property, you can select a Query/Table from the Drop down list, or write an SQL Statement directly onto the property grid. Another option is to click the three dots symbol at the end of the row to bring up the QUERY DESIGN Window. Then it is just a case of creating your query.

Figure 2: The Combo Box PROPERTY sheet for
the form in figure 1.

In our example shown in figure 1, I used tblProducts as the ROW SOURCE for the Combo Box. This, of course, is the same table that is used as the RECORD SOURCE for the main form.  I set the COLUMN COUNT PROPERTY (from the FORMAT tab) to 2, so that we get two columns in the drop down list - that is, the ID field, and the ItemName field.  I also set the BOUND COLUMN property (from the DATA TAB) to 1, so our Combo Box stores the value from the first column (ie the ID field) when the user makes a selection from the drop down list.

The next stage is to enter the VBA code.  We are going to use the Combo Box's ON CHANGE event.  This

event triggers as soon as the user selects an item from the drop down list.

Enter VBA Code

1. Select the Combo Box Control by clicking it with the mouse.
2. Open the PROPERTIES window.
3. Select the EVENT tab.
4. Select the ON CHANGE event by clicking its row in the grid.
5. Click the three dots symbol on the far right of the row. This opens the CHOOSE BUILDER dialogue box.  
6. Select CODE BUILDER and click the OK button.  This opens the VISUAL BASIC editor.
7. Copy and Paste the code (listed below) between the PRIVATE SUB and END SUB statements.   You may need to edit the FINDFIRST Statement on line 4 (replace ID with the field you are searching for).

On Error GoTo myError
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
rst.FindFirst "ID = " & Me!ctlSearch
Me.Bookmark = rst.Bookmark
leave:
Me!ctlSearch = Null
If Not rst Is Nothing Then Set rst = Nothing
Exit Sub
myError:
MsgBox "Record Not Found"
Resume leave

Figure 4: The VBA Editor

The main section of code works by cloning the forms's record set, which is stored in an object variable called rst. The FINDFIRST method is then used to search the cloned record set for the item selected in the Combo Box by the user.  Once found, the forms Bookmark property is then set to that of the cloned recordset.  This results in the Products Form seamlessly displaying the record selected from the drop down list.

Automating an Update Query Using DoCmd.RunSQL

This tip shows us how to create and execute an Update Query using the VBA DoCmd.RunSQL Statement.  We shall use the example of a Products table, and our objective is to update the CostPerUnit field of a particular record matching a set criteria.  To do this the user will open an unbound form to select a product from a combo box, and enter a new updated price in the textbox.  

Above: Unbound Form to select a product from a Combo Box list, and enter its new price.

The code which creates and executes the SQL Statement will run when the Update command button is clicked.  Information entered by the user is referenced by the code (as Me!ctlProduct and Me!txtNewCost) and integrated into the SQL statement.

Above:  This code runs when the users click the Update command button.

The varSQL variable contains the SQL from the Update Query which we are going to run.  This can be built using the Access Query Design Grid, and switching to SQL view to copy the statement.  The two form controls with our user input are built into this variable, and become part of the Statement. The next line uses the DoCmd.RunSQL statement to the run the SQL contained in our string variable.  This will then act like a standard Update Query: the relevant product is found, and the CostPerUnit field is updated to the new price specified by the user.