Drop Down Search Box

[RowdyE]

I have a Combo Box to select specific records in a table. The Box list a
persons LastName, FirstName, MI. I have been able to set the box up to select
a record based on a persons Last Name. However, if there is more than one
person with the same last name, it will always select the first record. I can
not find a way to make it select the specific record based on the persons
full name. Here's the code as I have it now.

Private Sub FindMember_AfterUpdate()

DoCmd.ShowAllRecords
Me!LastName.SetFocus
DoCmd.FindRecord Me!FindMember

'Set value of combo box equal to an empty string
Me!FindMember.Value = ""

End Sub

Each part of the name is listed in a seperate field. They are [FirstName]
[MI] [LastName].

I have tried many different thing to get this to work, but I can not select
a specific record if there is more than one persons with the same last name.
Any suggestions?

 

[Al Campagna]

RowdyE,
A person's last name is not unique enough to identify one, and only one
record.
You should be searching using a unique key value.
If you designed your table properly, you should have a unique key field
(ex. CustID, or EmpID, etc)... and that would be the value you would search
for.

On my website (below) I have a sample file for A97 and A2003 called
Quick Find Combo.
It locates a single record using a unique value selected from a combo
box.
See my query behind the combo, and the NoOfColumns, and Column Widths
setting.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Beetle]

Your Members table *should* have a field that uniquely identifies each
Member (i.e. an Autonumber or some other value that is unique to
each member). You should be using this Key field to find a match, not
the last name field. So, the properties for your FindMember combo
box might look like;

Row Source: Select MemberID, LastName & ", " & FirstName As
FullName From tblMembers
Bound Column: 1
Column Count: 2
Column Widths: 0", 2"

And your code would look like;

Private Sub FindMember_AfterUpdate ()

With Me.RecordsetClone
.FindFirst "MemberID=" & Me!FindMember
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

End Sub