Autonumber

[Lynn]

I have a data base for Memberships. In the ID field it was set for
AutoNumber. Yesterday, I had to enter a long time member that already has
had his number for years (appearantly the girl before had deleted his
record). I went into the Design View and set the ID field for number so that
I could assign him the correct membership number he has had for years. When
I went back in to reset the ID back to AutoNumber it will NOT allow me to do
this. The following message appears:

Once you enter data in a table, you can't change the data type of any field
to AutoNumber, even if you haven't yet added data to that field.

I REALLY NEED to get the ID field BACK to AutoNumber, what do I do?

 

[BruceM]

Why do you need to do that? You can just roll your own. One way is to
enter something like the following as the default value of a text box bound
to the ID field:

=DMax("[ID_Field]","[YourTable]") + 1

Substitute your table and field names.

 

[John Spencer]

Do you have a backup of your data? The easiest way is to load the backup.

No backup, lots of work.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Assumption: Your table is Named Members

-- Click on your current table and copy it
-- Paste your current table into the database and select the option STRUCTURE ONLY
-- Open up the table in design view and change the ID field to AUTONUMBER and
save and close the New table
-- NOW build an APPEND query to APPEND ALL the records and ALL the fields from
the old table to the new table.

SQL for that query would look like:
INSERT INTO [Members_NEW]
SELECT * FROM [Members]
-- Select Query: Run from the menu and append all the records when asked.
-- your old table has any relationships set up, remove them
-- Rename the old table to Members_BU (keep it around just in case)
-- Name the new table with the original name "Members"
-- Rebuild the relationships.

Caveats:
-- Turn off all NAME Autocorrect features (Tools: Options: General tab) and
compact your database FIRST

Nest time, DO NOT change the autonumber field. Use a query to insert the
record with the proper ID number. If you have NO required fields other than
the member id field that could look like
INSERT INTO Members (IDField)
Values (223)

If you have some required fields then you would need to expand that a bit to
fill in some default values. Once the record has been created you can access
and edit it.

The SQL for the insert (append) query would look like:
INSERT INTO Members ([IDField], [FirstName], [LastName], [SomeDateField])
Values (223,"John","Spencer",#1/1/2007#)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Tony Toews [MVP]]

I have a data base for Memberships. In the ID field it was set for
AutoNumber. Yesterday, I had to enter a long time member that already has
had his number for years (appearantly the girl before had deleted his
record).

And that's why there should be an inactive flag that most forms and
reports use to display current data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/

 

[Lynn]

Mr. Spencer,
I have followed your instructions throughly on the first half and
successfully completed everything up to the SQL point. Once I got to that
point, it became confusing. I know this sounds stupid, but would you be able
to explain the second half (regarding the SQL) step-by-step (or in other
words, blonde terms). THANK YOU SO MUCH for your help!!!
Lynn

Do you have a backup of your data? The easiest way is to load the backup.

No backup, lots of work.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Assumption: Your table is Named Members

-- Click on your current table and copy it
-- Paste your current table into the database and select the option STRUCTURE ONLY
-- Open up the table in design view and change the ID field to AUTONUMBER and
save and close the New table
-- NOW build an APPEND query to APPEND ALL the records and ALL the fields from
the old table to the new table.

SQL for that query would look like:
INSERT INTO [Members_NEW]
SELECT * FROM [Members]
-- Select Query: Run from the menu and append all the records when asked.
-- your old table has any relationships set up, remove them
-- Rename the old table to Members_BU (keep it around just in case)
-- Name the new table with the original name "Members"
-- Rebuild the relationships.

Caveats:
-- Turn off all NAME Autocorrect features (Tools: Options: General tab) and
compact your database FIRST

Nest time, DO NOT change the autonumber field. Use a query to insert the
record with the proper ID number. If you have NO required fields other than
the member id field that could look like
INSERT INTO Members (IDField)
Values (223)

If you have some required fields then you would need to expand that a bit to
fill in some default values. Once the record has been created you can access
and edit it.

The SQL for the insert (append) query would look like:
INSERT INTO Members ([IDField], [FirstName], [LastName], [SomeDateField])
Values (223,"John","Spencer",#1/1/2007#)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a data base for Memberships. In the ID field it was set for
AutoNumber. Yesterday, I had to enter a long time member that already has
had his number for years (appearantly the girl before had deleted his
record). I went into the Design View and set the ID field for number so that
I could assign him the correct membership number he has had for years. When
I went back in to reset the ID back to AutoNumber it will NOT allow me to do
this. The following message appears:

Once you enter data in a table, you can't change the data type of any field
to AutoNumber, even if you haven't yet added data to that field.

I REALLY NEED to get the ID field BACK to AutoNumber, what do I do?

 

[John Spencer]

To build the query:

--- Add Members table to a new query
--- Add all the fields to the query
--- Select QUERY: Append from the menu
--- Select the Members_New Table to append records to
--- You should get matching field names all the way across
--- SELECT Query: Run from the menu



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Mr. Spencer,
I have followed your instructions throughly on the first half and
successfully completed everything up to the SQL point. Once I got to that
point, it became confusing. I know this sounds stupid, but would you be able
to explain the second half (regarding the SQL) step-by-step (or in other
words, blonde terms). THANK YOU SO MUCH for your help!!!
Lynn

Do you have a backup of your data? The easiest way is to load the backup.

No backup, lots of work.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Assumption: Your table is Named Members

-- Click on your current table and copy it
-- Paste your current table into the database and select the option STRUCTURE ONLY
-- Open up the table in design view and change the ID field to AUTONUMBER and
save and close the New table
-- NOW build an APPEND query to APPEND ALL the records and ALL the fields from
the old table to the new table.

SQL for that query would look like:
INSERT INTO [Members_NEW]
SELECT * FROM [Members]
-- Select Query: Run from the menu and append all the records when asked.
-- your old table has any relationships set up, remove them
-- Rename the old table to Members_BU (keep it around just in case)
-- Name the new table with the original name "Members"
-- Rebuild the relationships.

Caveats:
-- Turn off all NAME Autocorrect features (Tools: Options: General tab) and
compact your database FIRST

Nest time, DO NOT change the autonumber field. Use a query to insert the
record with the proper ID number. If you have NO required fields other than
the member id field that could look like
INSERT INTO Members (IDField)
Values (223)

If you have some required fields then you would need to expand that a bit to
fill in some default values. Once the record has been created you can access
and edit it.

The SQL for the insert (append) query would look like:
INSERT INTO Members ([IDField], [FirstName], [LastName], [SomeDateField])
Values (223,"John","Spencer",#1/1/2007#)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I have a data base for Memberships. In the ID field it was set for
AutoNumber. Yesterday, I had to enter a long time member that already has
had his number for years (appearantly the girl before had deleted his
record). I went into the Design View and set the ID field for number so that
I could assign him the correct membership number he has had for years. When
I went back in to reset the ID back to AutoNumber it will NOT allow me to do
this. The following message appears:

Once you enter data in a table, you can't change the data type of any field
to AutoNumber, even if you haven't yet added data to that field.

I REALLY NEED to get the ID field BACK to AutoNumber, what do I do?

 

[Lynn]

I am SO sorry to bother you, but I followed the instructions step-by-step and
when I get to the Run Query I get the following message:
You tried to assign the Null value to a variable that is not a Variant data
type

What in the world am I doing wrong?
THANK YOU!!
Lynn

To build the query:

--- Add Members table to a new query
--- Add all the fields to the query
--- Select QUERY: Append from the menu
--- Select the Members_New Table to append records to
--- You should get matching field names all the way across
--- SELECT Query: Run from the menu



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Mr. Spencer,
I have followed your instructions throughly on the first half and
successfully completed everything up to the SQL point. Once I got to that
point, it became confusing. I know this sounds stupid, but would you be able
to explain the second half (regarding the SQL) step-by-step (or in other
words, blonde terms). THANK YOU SO MUCH for your help!!!
Lynn

Do you have a backup of your data? The easiest way is to load the backup.

No backup, lots of work.

STEP 1: BACKUP your data before attempting the following.
STEP 2: BACKUP your data before attempting the following.

Without a backup you cannot restore the data if this does not work the way you
expect.

Assumption: Your table is Named Members

-- Click on your current table and copy it
-- Paste your current table into the database and select the option STRUCTURE ONLY
-- Open up the table in design view and change the ID field to AUTONUMBER and
save and close the New table
-- NOW build an APPEND query to APPEND ALL the records and ALL the fields from
the old table to the new table.

SQL for that query would look like:
INSERT INTO [Members_NEW]
SELECT * FROM [Members]
-- Select Query: Run from the menu and append all the records when asked.
-- your old table has any relationships set up, remove them
-- Rename the old table to Members_BU (keep it around just in case)
-- Name the new table with the original name "Members"
-- Rebuild the relationships.

Caveats:
-- Turn off all NAME Autocorrect features (Tools: Options: General tab) and
compact your database FIRST

Nest time, DO NOT change the autonumber field. Use a query to insert the
record with the proper ID number. If you have NO required fields other than
the member id field that could look like
INSERT INTO Members (IDField)
Values (223)

If you have some required fields then you would need to expand that a bit to
fill in some default values. Once the record has been created you can access
and edit it.

The SQL for the insert (append) query would look like:
INSERT INTO Members ([IDField], [FirstName], [LastName], [SomeDateField])
Values (223,"John","Spencer",#1/1/2007#)

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Lynn wrote:
I have a data base for Memberships. In the ID field it was set for
AutoNumber. Yesterday, I had to enter a long time member that already has
had his number for years (appearantly the girl before had deleted his
record). I went into the Design View and set the ID field for number so that
I could assign him the correct membership number he has had for years. When
I went back in to reset the ID back to AutoNumber it will NOT allow me to do
this. The following message appears:

Once you enter data in a table, you can't change the data type of any field
to AutoNumber, even if you haven't yet added data to that field.

I REALLY NEED to get the ID field BACK to AutoNumber, what do I do?

 

[John Spencer]

I have no idea. I would not expect that message at all if the two
tables are identical.

After you create the query, switch to SQL view and copy and paste the
text into your posting. Perhaps we can spot the trouble or can rewrite
the query to work.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================