Results 1 to 2 of 2
Thread: Combo Box Search (XP)
2004-03-26, 17:35 #1
- Join Date
- Jan 2002
- Thanked 0 Times in 0 Posts
Combo Box Search (XP)
I have a form with a combo box search field. You type in a person's last name and the form jumps to that record. Unfortunately, if there are several people with the same name, the form goes to the first person with that last name. Is it possible to have a combo box that is bound to two columns and uses both as the search criteria? If not, is there some way to find the exact record without scrolling through everyone with the same last name? Thanks.
2004-03-26, 19:16 #2
- Join Date
- Nov 2001
- Arlington, Virginia, USA
- Thanked 3 Times in 3 Posts
Re: Combo Box Search (XP)
Would not recommend using last name (or even last name & first name) as search criteria. You should use the primary key for the table as search criteria because it will always be a unique value in the table. (If table does not have a PK you need to add one, use AutoNumber if there is no logical choice for PK such as an SSN field.) The Personnel tables I use normally have an SSN field as PK so that's what I use. Example of query SQL that populates the combo box:
SELECT PERSONNEL.SSN, Trim$([PERSONNEL]![LNAME] & " " & [PERSONNEL]![FNAME] & " " & Nz([PERSONNEL]![MI])) AS FULLNAME
ORDER BY PERSONNEL.LNAME, PERSONNEL.FNAME, PERSONNEL.MI;
Note that the FULLNAME calculated field concatenates the first, last, & (optional) MI for display purposes (use Trim & Nz functions in case no MI), but SSN is used to locate record on form. The combobox Bound Column is set to 1 (SSN), while Column Width for first column is set to 0" to hide SSN from user. To locate record, use event procedure like this:
Private Sub Combo_Name_AfterUpdate()
Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone
.FindFirst "<!t>[SSN]<!/t> = '" & Me.Combo_Name & "'"
If Not .NoMatch Then
Me.Bookmark = .Bookmark
MsgBox "Name not found.", vbExclamation, "NOT FOUND"
Set rst = Nothing
If you use this method to locate record, you should not have problem with duplicate last names. You could search for the concatenated first & last names, but there's no guarantee there won't be 2 John Smith's, 2 Paul Jones, etc.