Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Jan 2002
    Posts
    31
    Thanks
    0
    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.

  2. #2
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    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
    FROM PERSONNEL
    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

    With rst
    .FindFirst "<!t>[SSN]<!/t> = '" & Me.Combo_Name & "'"
    If Not .NoMatch Then
    Me.Bookmark = .Bookmark
    Else
    MsgBox "Name not found.", vbExclamation, "NOT FOUND"
    End If
    End With

    Set rst = Nothing

    End Sub

    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.

    HTH

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •