Sequential numbering of records

[Jomark]

Having read the various postings on the subject i am more confused than ever.

what I want to do is to create a field that will contain sequential numbers
starting with an initial number of say 20000 and incrementing by 1 such that
record 1 is 20000, record 2 is 20001, record 3 is 20003 etc as each record is
entered using an input form. Something similar perhaps to the series fill in
Excel or a customisable autonumber field.
my question is what is the simplest way of doing this for what would seem to
be a fairly common problem

 

[Amy Blankenship]

in the Form_Current Sub

Me.CustomNumberField.DefaultValue = 200000 + Me.CurrentRecord

Note that this will cause duplicates if you ever delete any records. But it
is simple....

 

[Jeff Boyce]

Use your favorite search engine and look for "Custom Autonumber".

The general idea is that you'll build a procedure that checks the value of
the field used for your sequence number, add one, and use that resulting
value as the "next" number.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[BruceM]

One method is outlined here:
http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb
Note in particular the method for dealing with a multi-user environment.
You could add the Nz function to take care of the first record, which may
not have a value:
=Nz(DMax("SomeField","SomeTable"),20000)+1
You could also use the code in the form's Current event, with modified
syntax:
Me.SomeField.DefaultValue = Nz(DMax("SomeField","SomeTable"),20000)+1
or
If Me.NewRecord Then
Me.SomeField = Nz(DMax("SomeField","SomeTable"),20000)+1
End If

If you already have records that are numbered as you would like you can omit
the Nz:
=DMax("SomeField","SomeTable")+1

 

[Jomark]

Thank you everyone for your suggestions. I will now try to add this to my
database application.
I must say that for what seems to be something simple it would be really
helpful to have provided this as one of the data types in Access.

from searching the web there seems to be an almost infinite number of
variations and solutions

 

[BruceM]

DMax is a common solution to this problem. Access has autonumber, but it
will almost invariably leave gaps in the numbering. A very small bit of
coding, or a simple expression, will do what you need. The main question is
where to apply the DMax function.

 

[Jomark]

Thank you everyone for your suggestions. I will now try to add this to my
database application.
I must say that for what seems to be something simple it would be really
helpful to have provided this as one of the data types in Access.

from searching the web there seems to be an almost infinite number of
variations and solutions

 

[Jomark]

Thanks again the rogersaccesslibrary website was most helpfull and i think I
am now on the right path for my application