Are you sure the names are appearing in the combobox as
Allen, James
Allen, Kevin
Allen, Leon
with a comma between the last name and first name? This would indicate that
the complete name is held in one field, and if this is true, then the
combobox should retrieve the correct record. If, however, the last name and
first name are held in separate fields, as is usually done, and actually
appear as
Allen James
Allen Kevin
Allen Leon
with simply a space between the first and last name, this would explain the
problem. The command the combobox uses to retrieve a record is rs.FindFirst
and it does exactly as its name suggests, finds the FIRST record that matches.
If it's matching on last names, it'll find the first record with the last
name of, in this case, Allen, regardless of which Allen you click on.
If this is the case, the fix involves using a calculated field in a query. If
the form is based directly on a table, you need to make a simple query
including all the fields of the table, and then change the RecordSource for
the form from the table to the query. If the form is already based on a query,
more's the better. All form really should be based on queries anyway, for
situations just like this.
Now open the query in Design View, and in a blank "Field" box enter this code:
CompleteName: [LastName] & " " & [FirstName]
Be sure to include the colon after CompleteName and before [LastName] & " " &
[FirstName] (I say this because it's very hard to see on my monitor)
Save and Exit Design View
Run the query and you should have a new field named CompleteName and the data
should look like
Allen James
Allen Kevin
Allen Leon
Now, delete your current combobox, create another combobx based on your
calculated fiels CompleteName that you just added to the query. Not only will
Access take you to the correct Allen, if you keep entereing the name (typing
Allen L for instance will take you to Allen Leon, without scrolling) when you
click on Allen Leon it’ll take you to his record.
It may be that in the previous DBs where this worked before, your name fields
had last name and first name in a single field.
--
There's ALWAYS more than one way to skin a cat!
Answers/posts based on Access 2000/2003
Message posted via AccessMonster.com