Alphabetize lookup lists after additions/edits

[Christina Ethridge]

Hi - I have several tables that draw from each other into
a main table/database.

For example, I have a table with a full list of people's
names (as well as all of their contact information). From
the main table I have a drop down list so I can pull in
that person's name. I have a of couple questions:

1) The people table is added to as new names come up - how
do you get it to automatically alphabetize in the main
table drop down after each edit/add.

2) Although I specified in the lookup wizard to search by
last name/first name - it's only showing the last name
once selected - how do I get it to show last name and
first name?

Thanks!

 

[Nikos Yannacopoulos]

1) If my guess is correct, you have set up the lookup
wizzard to read directly from the table. Alternatively,
you could set it up to read from a query that reads from
the table, in which you also can set the sorting order.

2)Do you mean you want to see both last and first names in
the drop-down list? If yes, what you need to do is set
column count to 2 in the lookup tab of the table field
(assuming last name is in field 1 and first name in field
2 - that's easy to do in the aforementioned query, even if
it is different in the original table).

Nikos

 

[John Vinson]

1) The people table is added to as new names come up - how
do you get it to automatically alphabetize in the main
table drop down after each edit/add.

You don't. A Table HAS NO ORDER - it's an unordered bucket of
information. Instead, base your Combo Box on a Query sorting by name,
rather than directly on the table.

2) Although I specified in the lookup wizard to search by
last name/first name - it's only showing the last name
once selected - how do I get it to show last name and
first name?

The Lookup Wizard is VERY limited, confusing, and causes many
problems. I'd suggest never using it AT ALL. See
http://www.mvps.org/access and search for "Lookup" for a discussion of
its problems.

Instead, use a Form for all user interaction with your data. You can
and should still use the lookup *table* - just not the Lookup field
type in your table datasheet! Base a Combo Box on a query in which the
first visible field is calculated by concatenating the names: e.g.

SELECT PersonID, [LastName] & ", " & [FirstName]
FROM People
ORDER BY LastName, FirstName;