Find records with the same name

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box based on "Find a record on form based on the value
selected in the combo box". The field the value is based on is "LastName".
However, it will only find the first occurrence of a last name, not any
others. For instance, If John Smith is record no. 6 and Sue Smith is record
no. 105, it will only find John Smith. It will bring up record no. 7 if I
ask it to find Smith again.
Thanks very much.
Anneg
 
Anneg

The way Access combo boxes work is based on an underlying query. While it
is possible to set a query to only return a single record/value, it would be
unusual in the circumstance you described.

Are you saying that you can begin typing "Smith" in the combo box, drop it
down, and there is only one record displayed?

Please post the SQL statement representing your combo box's Row Source.
 
Hi Jeff
If there is an instance of a second surname in the combo box, it does appear
in the drop down list, but I would like to be able to type the name in the
box, hit return and it finds a surname. If it is not the right record, I
would like to be able to hit return again and it brings up the next instance
of the said surname. Am I asking too much? Is there another way of
accomplashing this. Sorry, I am not the best Access designer on the planet.
Here is the statement I think you wanted:
SELECT tblDetails.ID, tblDetails.LastName FROM tblDetails;
Many thanks
Anneg
 
Might I suggest that you expand your SQL statement to include a FirstName
field as well as a LastName field. That way, your user would not have to go
elsewhere to look up further information, and could simply drop down the
combo box and select which individual with LastName=Smith was needed.
 
I did think of that and indeed I tried it. But when you have over 1000 names
I wanted to avoid use of the drop down list. Thanks.
 
Anneg

If you set the AutoExpand property to "Yes", a user can begin typing the
sought-after name in the combo box to "narrow" their search. That is,
typing only an "s" would move the list down to the last names starting with
"s". Typing "Smith" would move down to last names starting with "Smith".

A user would then not have to scroll through a 1000 names.
 
Hi Jeff
It's finding the second and subsequent instances of the same surname that's
the problem. It finds the first instance okay using the autoexpand method.
I've improved the situation by setting the combo box list in alpha order and
if the first Smith is not the right one, at least the subsequent Smiths are
grouped together in the drop down list.
Many thanks Jeff.
Anne
 
Back
Top