Using A Combo Box To Find A Record
By Tom Wickerath 
Note: These instructions were written for Microsoft Access 2002, using the frmEmployerPositions form in the Jobs.mdb database to find a record for a selected contact person. The dialog boxes and resulting code may be slightly different for other versions of Access.
To add a combo box to a form so that you can use it to find existing records, complete the following steps:
- Open the form in design view. Display a form header if it is not already present (View -> Form Header/Footer).
- Display the toolbox with the Control Wizards button selected.
- Click on "Combo Box" to select it. Drag a new combo box into the form header. (Do not put it in the page header — it won't work properly if placed in this section.) If you had the Control Wizards button depressed, then you should be presented with the screen shown below. Select the third choice, as shown below:
 |
If you only see the first two choices listed, then you are working with an unbound form. A SQL statement in the form's RecordSource Property will not provide the third option in the dialog window. You need to bind your form to a saved TableDef or QueryDef object first, in order to be able to select the third option.
- Select the appropriate fields for your database. Make sure to include a field which uniquely identifies the record which, in this case, is the pkEmployerID Primary Key. For this example, we will add the ContactFirstName field later, by adjusting the SQL statement. Click on the "Next" button.
 |
- Size the ContactLastName column slightly wider to allow room for adding the ContactFirstName field later, and then click "Next." Leave the check mark in the "Hide key column (recommended)" check box.
- Enter "Find a Contact" or any other appropriate text for your combo box label.
- Click the "Finish" button.
At this point, you should see an unbound combo box in design view. You may need to align the label and combo box controls so that they display properly. This is a good time to rename your combo box, since the Control Wizard gives it a brain dead name, such as "Combo12." I recommend renaming it to something like "cboFindRecord."
- In design view, with Properties displayed, select your new combo box.
- Select the "Other" tab and enter cboFindRecord as the name of the control.
You must now make the same adjustment in your VBA code:
- Click on the Code toolbar button, or click on View -> Code menu, or select the "Events" tab on the Properties dialog window, then click on the ellipses (...) button next to the [Event Procedure] for the "On Click" event.
- Find a block of code for the After Update event procedure for the combo box, which looks similar to the following: (Note: The code written by other versions of Access may be slightly different.)
Option Compare Database
Option Explicit '<-- If you are missing this
' statement, then add it manually. See the
' Gem Tip, "VB Editor Option Settings," posted at:
' http://www.Access.QBuilt.com/html/gem_tips.html
Private Sub Combo12_AfterUpdate( )
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[pkEmployerID] = '" & Me![Combo12] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
- Make the changes to the subroutine, as indicated below in blue:
Private Sub cboFindRecord_AfterUpdate( )
' Find the record that matches the control.
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[pkEmployerID] = '" & Me![cboFindRecord] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub
- Now would be a good time to add error handling to this subroutine, since every procedure should include error handling. Add the lines of code shown in blue below:
Private Sub cboFindRecord_AfterUpdate( )
' Find the record that matches the control.
On Error GoTo ProcError
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[pkEmployerID] = '" & Me![cboFindRecord] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
ExitProc:
Exit Sub
ProcError:
MsgBox "Error: " & Err.Number & ". " & Err.Description
Resume ExitProc
End Sub
- Click on the "Save" button to save your code changes. Click on Debug -> Compile DatabaseName to make sure that your code compiles correctly.
- In form view, with the drop down arrow selected, you should see something similar to the following:
Note: In this example shown below, I have also set the form caption with VBA code to display a concatenated contact name (Sarah Tasker in the blue title bar of the form). The code required to do this is shown at the end of this document.
 |
- We need to make our combo box more useful by sorting the last names ascending. We will also concatenate the ContactFirstName at the same time. We accomplish this by making an adjustment to the RowSource property, so that the names are displayed as one field.
- Select the Data tab of the Properties for the combo box. Notice that the Row Source includes a SELECT statement (i.e., a query). Click into the Row Source cell to display the build button, then click the build button.
 |
This should open the Query Builder. Create a new calculated field such as the following:
Contact: [ContactFirstName] & " " & [ContactLastName]
- Place a check mark in "Show" for this new field. Add the ContactLastName and ContactFirstName fields, in that order. Apply an ascending sort order to these two new fields, and deselect the "Show" box. Close the query builder. Answer "Yes" when prompted if you want to update the RowSource property. If you zoom your new RowSource by pressing <SHIFT><F2>, you should see something similar to the following:
SELECT tblEmployer.pkEmployerID, [ContactFirstName] & " " & [ContactLastName] AS Contact
FROM tblEmployer
ORDER BY tblEmployer.ContactLastName, tblEmployer.ContactFirstName;
- We have one more thing to do to make this fully functional. We want to synchronize our combo box with the form's navigation buttons, so that when we use the navigation buttons to navigate to a new record our combo box will be updated appropriately. Add the following subroutine to the form's code module:
Private Sub Form_Current( )
On Error GoTo ProcError
' Synchronize name in combo box with displayed record.
cboFindRecord = pkEmployerID
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub
 Top
Question: How can I change the form's caption to display the contact name for the currently selected record?
Answer: Insert the code shown in blue into the Form_Current event procedure:
Private Sub Form_Current( )
On Error GoTo ProcError
' Do magic tricks with the form's caption!
If Me.NewRecord Then
' Use appropriate prompt for your database.
Me.Caption = "Enter Contact Information..."
Else
Me.Caption = [ContactFirstName] & " " & [ContactLastName]
End If
' Synchronize name in combo box with displayed record.
cboFindContact = pkEmployerID
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub
Question: My "Find a Contact" combo box does not work after I set the form's Allow Edits property to false. How can I fix that?
Answer: Setting Allow Edits on the form to False helps to prevent accidental changes to the data. If you have your form locked down with Allow Edits = False, then the combo box will not work. In this case, you need to add the following two procedures to temporarily set your form to allow edits when the combo box receives focus, and then lock the form back down when the user clicks out of the combo box.
Private Sub cboFindRecord_Enter( )
On Error GoTo ProcError
Me.AllowEdits = True
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub
You then set the AllowEdits Property back to False as a part of the combo box Exit event procedure:
Private Sub cboFindRecord_Exit (Cancel As Integer)
On Error GoTo ProcError
Me.AllowEdits = False
ExitProc:
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume ExitProc
End Sub
Top
Author's note:
This set of instructions was originally prepared for the college course I taught, and this technique is suitable for combo lists that don't become excessively large (like say, 200 records max). A modification can be made in the case that there are many thousands of records (http://msdn2.microsoft.com/en-us/library/aa188218(office.10).aspx), which is especially important if the database is split with the BE on a file server.
Created: Jan. 2003
Last Updated: Feb. 3, 2007
Copyright © 2003-2007 Tom Wickerath. All rights reserved. Reprinted by permission.
Top
|
|
About the author:
Tom Wickerath is a chemist at The Boeing Company in Seattle, Washington, USA. He works in the Analytical Chemistry Group of Boeing's Materials & Process Technology (M&PT) organization. In the early 1990's, Tom became interested in the use of database technology to avoid many of the inefficiencies involved in using spreadsheets for the analysis of large amounts of data.
Tom has taught Microsoft Access courses to college students for three years at Bellevue Community College in Bellevue, Washington. He has also been an active member of the Pacific Northwest Access Developer's Group (PNWADG) since the days of Access 2.0 and served as an officer of this group from 1998 to 2002. As a longtime Access expert, Tom has given presentations on complex Access subjects, including tools designed for Access developers, to the Seattle Access Group, the PNWADG and to Boeing Company personnel.
Tom has provided his expertise to Microsoft Access developers and users for years in the Microsoft Access Newsgroups in UseNet, and he was awarded MVP for Microsoft Access in April, 2006. Check the Microsoft.Public.Access Newsgroup for Tom's latest expert advice and feel free to post any questions in these Newsgroups.
Special Note From The Author: "If you find that any of these tips save you a lot of time and frustration with your database development, please consider making a tax deductible contribution to the Northwest Kidney Center, located in Seattle, Washington, USA. I've seen first hand how valuable their work is. With your help, they can do more research in the treatment and prevention of kidney disease, provide increased financial assistance to patients in need, and generally make kidney patients' lives more comfortable. You can even use PayPal to make a quick and easy donation online, and you'll feel better knowing that you have contributed to a better quality of life for those in need. Thanks."
-- Tom Wickerath
http://www.nwkidney.org/ways2help/donatenow/
For questions regarding this tutorial, please contact Tom at:
|
Tom does not accept unsolicited requests for help. Contact Tom only if you have questions or feedback on one of his articles or tips, or you have been specifically invited by Tom in a newsgroup posting to contact him. If your question references a question in the newsgroups, please include the URL to the post
Top
Visitors since 10 Aug. '04:
|
