Auto suggest institution code?

I have a table consisting approx 400 records. Fields are Institution
Code; Name; Address.

The code is Letter followed by 2 digit number e.g. A20, B40, C25.

I have had a request from a colleague who wishes to create a new record
for a new institution, and wants the functionality of either a) prompt
to advise that code already in use (easiest option); b) display list of
available codes or c) automatically suggest a code according to
institution name (i.e. if user types in name Bedfordshire University,
the code returned will begin B## with B## being a code not already in
use.

I would appreciate some advice on how to go about creating this
functionality and opinion on how difficult it would be to create an
automatic code suggestion, or whether it would be best to have some
other method to make it easier to select a code from what in theory
could be a list of up to 2574 codes.

Help!

What happens when you have more than 99 B's, but only 3 Z's? Are you going
to use B100, or Z04? Then suppose you get a real Z4? Now what happens?
Suppose Bedfordshire University changes its name? Now what happens?

By making a key dependent on values from within the row (in this case, the
first letter of a field) and repeating the information in every row, you are
violating Second and Third Normal Forms.
--
Arvin Meyer, MCP, MVP
Free MS-Access downloads:
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"KLR" wrote in message
oups.com...
I have a table consisting approx 400 records. Fields are Institution
Code; Name; Address.

The code is Letter followed by 2 digit number e.g. A20, B40, C25.

I have had a request from a colleague who wishes to create a new record
for a new institution, and wants the functionality of either a) prompt
to advise that code already in use (easiest option); b) display list of
available codes or c) automatically suggest a code according to
institution name (i.e. if user types in name Bedfordshire University,
the code returned will begin B## with B## being a code not already in
use.

I would appreciate some advice on how to go about creating this
functionality and opinion on how difficult it would be to create an
automatic code suggestion, or whether it would be best to have some
other method to make it easier to select a code from what in theory
could be a list of up to 2574 codes.

Help!