Autonumber with Text etc

[JMM]

A table I have inherited has some 3500 records. The old unique identifier required manual entry which was sequenced starting from 1000. Over time some records have been deleted.<br>
<br>
I want to automate the unique identifier by adding an autonumber field (not so straight forward -see below) AND I need each ID to begin with the letter K. <br>
<br>
Questions: <br>
1) How can I update the table by adding an autonumber field which produces exactly the same number as the old identifier (allowing for the gaps where records have been deleted)?<br>
2) How does the letter K automatically become the first character in the ID field?<br>
<br>
Thanks

 

[DougP]

Well you can not use and autonumber field, but you can still keep track of a unique number.<br>
<br>
It will be a multi step process.<br>
1. Make a copy of the old table in case of a mess up.<br>
2. Create a new table. Again Copy old table but just the Structure. Add a new another Text field.<br>
This will become the new &quot;AutoNumber&quot; field. But it's property can not be Autonumber of course. It has to be TEXT because of the &quot;K&quot; .<br>
Now to create a set of numbers from 1 to 3500 will be up to you.<br>
If you have Microsft Excel I would create a blank sheet <br>
Put the number 1 in the cell &quot;A1&quot;.<br>
Highlight the Column A down to 3500 cells and use &quot;Edit&quot; Menu &quot;Fill Series&quot; to create a number sequence from 1 to 3500.<br>
<br>
Copy the whole column and open your new table and Click the &quot;Add&quot; record button in the Navigation bars at the bottom. Press &quot;Ctrl-C&quot; It should add 3500 new records.<br>
4. Create an update query to add a &quot;K&quot; in front of it.<br>
5. create an Update query to merge your old data to the new table.<br>
Now then you have to add new records and keep the number sequnce intact.<br>
I use this method to keep track of a number with great success. Put it in a Add new button on your form.<br>
--------------------------------------------<br>
'open table and get the last RA number used<br>
Dim MyDB As Database, MySet As Recordset<br>
Set MyDB = DBEngine.Workspaces(0).Databases(0)<br>
Set MySet = MyDB.OpenRecordset(&quot;Last-RA-Number&quot<br>
MySet.MoveFirst<br>
MySet.Edit<br>
Temp1 = MySet.Fields(&quot;Last_RA_Number&quot<br>
Temp2 = Temp1 + 1<br>
MySet.Fields(&quot;Last_RA_Number&quot = Temp2<br>
MySet.Update<br>
MySet.Close<br>
MyDB.Close<br>
---------------------------------------------<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[JMM]

Doug, Thanks very much for your help. Your suggestion re the update query works. Obvious really!<br>
<br>
However the code part confuses me a little. Debugger doesn't like Temp1 and Temp2 - says 'variable not defined'. And is this code a module or is it put on the OnOpen part of the Addnew button? And will it automatically add the K to the new number? <br>
The idea is hopeful though. Any further help appreciated.