Re: search or filter method using lastname,firstname,middle

Tech-Archive recommends: Speed Up your PC by fixing your registry

From: Allen Browne (AllenBrowne_at_SeeSig.Invalid)
Date: 11/28/04 

Date: Sun, 28 Nov 2004 10:57:59 +0800

Hi Guy. I'm not seeing this problem.

I set up a combo with these properties:
    Column Count: 2
    Column Widths: 0
    RowSource: SELECT ClientNum, [Surname] & "," & [FirstName] AS
FullName
                            FROM tClient ORDER BY Surname, FirstName;
    After Update: [Event Procedure]
The actual event procedure is below.

Not sure if it was significant that the bound column did not contain the
comma, but it worked fine. Tested in Access 2003 SP1, with JET 4 SP8.

Private Sub cboGoto_AfterUpdate()
    Dim rs As DAO.Recordset
    If Not IsNull(Me.cboGoto) Then
        If Me.Dirty Then Me.Dirty = False
        Set rs = Me.RecordsetClone
        rs.FindFirst "ClientNum = " & Me.cboGoto
        If rs.NoMatch Then
            MsgBox "Not found"
        Else
            Me.Bookmark = rs.Bookmark
            Debug.Print Me.ClientNum & " = " & Me.cboGoto
        End If
        Set rs = Nothing
    End If
    Me.cboGoto = Null
End Sub 

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Guy Story" <gstory@pccctx.com> wrote in message
news:%23cPoFoL1EHA.1292@TK2MSFTNGP10.phx.gbl...
>I am looking for a way search for a record using a combo box in a form. 
>What I want to setup for my users is the ability to start typing using the 
>following example:
>
> lastname,firstname,middle
>
> Basically the users would start at a combo box in my form and use a comma 
> to separate each part of a name.  I can use the filter by form buttons and 
> tab to the fields but I want to setup the ability to forgo that method.  I 
> was looking at building a FindRecord macro but that has not worked like I 
> want.
>
> I have tried an unbound combo box telling it last, first then middle 
> initial but when I insert the comma after the last name then type the 
> first I get a message about the text I entered is not in the list.
>
> Any suggestions?
>
> Thanks,
>
> Guy
Quantcast