If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Convert a field from long integer to Autonumber
Hi there
In my database, there is field called Student_ID which is a long integer field which someone has created manually. I want to have all new students to have IDs that are autonumbers. Is it possible to keep the current IDs as is and have any new people have their IDs created automatically? Thank you in advance for your help |
#2
|
|||
|
|||
Convert a field from long integer to Autonumber
forest8,
Unfortunately, unless you have access to an SQL Server no way to turn that field into an Autonumber. Fortunately you can use... Dmax("YourIDField","YourTable") + 1 You can place that on the Before_Insert of your form and achieve the same results as having an Autonumber field. -- Gina Whipp 2010 Microsoft MVP (Access) "I feel I have been denied critical, need to know, information!" - Tremors II http://www.regina-whipp.com/index_files/TipList.htm "forest8" wrote in message ... Hi there In my database, there is field called Student_ID which is a long integer field which someone has created manually. I want to have all new students to have IDs that are autonumbers. Is it possible to keep the current IDs as is and have any new people have their IDs created automatically? Thank you in advance for your help |
#3
|
|||
|
|||
Convert a field from long integer to Autonumber
On Fri, 21 May 2010 20:52:01 -0700, forest8
wrote: Hi there In my database, there is field called Student_ID which is a long integer field which someone has created manually. I want to have all new students to have IDs that are autonumbers. Is it possible to keep the current IDs as is and have any new people have their IDs created automatically? Thank you in advance for your help As Gina says, you can't change an existing Number to an Autonumber. What you can do is tedious but works: - MAKE A BACKUP! of your database - Turn off Name Autocorrect (if it's on, leave it off if it's off, and *leave it off when you're done*, it does more harm than good!) - Check the backup, make sure it works - Open the Relationships window, add this table, and click the Direct Relationships button to show all relationships to this table - Note down which tables the student table is related to - Select each join line (the line, not the table icon!) in turn and press the Delete key to delete all the relationships to this table - Close the relationships window - Click on (but don't open) the table in the Tables window - Press Ctrl-C then Ctrl-V to copy and paste the table to a new copy of the table. Select the option "Design View Only" to create an empty table. - Open it in design view, change the Student_ID to Autonumber - Run an Append query to migrate all the data from the old table into the new one (you can append into an Autonumber, the only way you can control its value) - Rename the old database (to Students_OLD say) - Reestablish all the relationships to the new table - Test everything - If all is well, delete Students_OLD and then compact & repair -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|