Autonumbering existing records

[junkaccount]

Hello again,

I have a field that contains numbers that were manually entered. I
would like to change this field to have the numbers automatically
increase sequentially instead of the user manually entering the number.
I must keep the existing numbers in the field for later reference.
The field is not a primary key. I've tried fooling around with
Autonumber and Dmax, but apparently only know just enough to be
dangerous to my existing data. I appreciate the assistance.

Jason

 

[Roger Carlson]

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "AutonumberProblem.mdg" which illustrates how to use DMax to
create a sequential numbering system.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

Hello again,

I have a field that contains numbers that were manually entered. I
would like to change this field to have the numbers automatically
increase sequentially instead of the user manually entering the number.
I must keep the existing numbers in the field for later reference.
The field is not a primary key. I've tried fooling around with
Autonumber and Dmax, but apparently only know just enough to be
dangerous to my existing data. I appreciate the assistance.

Jason

Autonumber would not be appropriate in this case - they'll always have
gaps and are basically uneditable and uncontrollable.

I'd suggest doing the incrementing using a Form. You MUST use a form
to do this - table and query datasheets have no usable events. You can
pick some suitable Form event (the form's BeforeInsert event if you
always want to increment and see the value; BeforeUpdate if there are
multiple users who might assign a number simultaneously and if you
don't care about seeing it; a command button or doubleclick event if
you want the user to be able to assign it on demand):

Private Sub <event>()
Me!txtSeqNo = DMax("[SeqNo]", "[Tablename]") + 1
End Sub

You can put a line

Me.Dirty = False

immediately after the assignment if you wish to save the record
(preventing duplicates).

John W. Vinson[MVP]

 

[junkaccount]

Thanks to your responses the situation has been resolved. I appreciate
the advice. Using the DMax allowed me to keep my existing numbers
intact, which was of utmost importance.

Thank you again - Jason