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

[Guest]

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.

 

[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.

 

[Joseph Meehan]

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.

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

If you are not using the ID for anything other than linking tables etc.
and you will not be showing it to people, and if that text is nothing but
numbers (0-9) no spaces etc. I believe you can change the field type to
number save the table and then make it auto number. Autonumber will
generally start off using the next number higher than that already in the
table. BTW I suggest you do a repair and compact on that table sometime
before changing to autonumber to get rid of any possible deleted records.

 

[Guest]

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?

 

[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.

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]

 

[Joseph Meehan]

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?

Autonumber will work (assuming everything is a number type). Remember
however that those autonumber may not be consecutive. If you get someone
trying to watch for each number or try to use numbers to order the records
in date order, it may cause them some problems.