search or filter method using lastname,firstname,middle

[Guy Story]

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

 

[Allen Browne]

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

 

[Guy Story]

Allen, what I was trying in my macro was different that what you have
posted. I will give it a try and let you know. I think the & ","
section is what I am missing.

Thanks,

Guy