How can I change a field property from text to Auto number?

J

Joyce

I have a database that tracks donors to our non-profit performing arts
center. Each of the different types of donation is in a seperate table but
joined based on a unique Donor ID number. When I first set up the database,
the table was already in Excel and imported it into Access. The Excel table
had the Donor ID number already assigned as text. Now that I have the
database up and running, I would like to change the properties of the Donor
ID field to auto number so that I do not have to look up the last Donor ID
number used when I add a new donor. Is there a way to do this without losing
or scrambling and data? Or is there way to automatically display the last
number used? Or can anyone think of a solution other than the ones already
mentioned?

Thanks in advance for any help or suggestions.
 
R

Rick B

This is asked and answered all the time. You should use code to locate the
highest used number and increment it by one.

If you need more help, do a search and read the previous posts on the topic.
 
J

Joyce

The Donor ID number is unique for each donor and the DB is set up so that
there will never be a duplicate number. This unique Donor ID follows the
same donor through each table where the donor has made a donation. I only
need the main Donor DB to assign the next highest number. The rest of the
tables are not a problem. Will your suggestion still work for my situtation?
 
J

John Vinson

I have a database that tracks donors to our non-profit performing arts
center. Each of the different types of donation is in a seperate table but
joined based on a unique Donor ID number. When I first set up the database,
the table was already in Excel and imported it into Access. The Excel table
had the Donor ID number already assigned as text. Now that I have the
database up and running, I would like to change the properties of the Donor
ID field to auto number so that I do not have to look up the last Donor ID
number used when I add a new donor. Is there a way to do this without losing
or scrambling and data? Or is there way to automatically display the last
number used? Or can anyone think of a solution other than the ones already
mentioned?

Thanks in advance for any help or suggestions.
Click to expand...

If you're using this field both as the primary key of this table *and*
as a link to other tables, you'll need to change the datatype in all
tables wherein it appears. This is doable, although rather tedious -
you'll need to remove all relationships, change the datatype (to
Number... Long Integer in the child tables), and reestablish the
relationships.

This is in addition to Joseph's very valid warning against using
Autonumber. You could have DonorID 3184 followed by DonorID -228412315
(say if you Replicate the database).

Are the ID's actually numeric? or do they contain text?

John W. Vinson[MVP]
 
Top