Customizing AutoNumber field

[Surya]

Can you please help me to customize a field in Access 2000
table?

I want a customized auto-numbering feature in an Access
2000 Table. The number should have 7-digits and the first
digit should always be 1. The next number should
increment by one. Example: 1000001, 1000002, 1000003 etc.
All these numbers should be unique.

Can you please send your answer to my email at:
(e-mail address removed) ?

Thanks,
Surya

 

[Scott McDaniel]

You asked in the newsgroup, you get answers in the newsgroup ...

You cannot do this at table level. You must do this via the form that is
used to add records (your ARE using forms for users to add/browse data??).
The easiest way would be to "autoseed" you column with the first value:
1000000. After that, you would use code like this in the BeforeInsert or
AfterInsert event of your form:

Dim lngID As Long

lngID = Nz(DMax("YourIDField")) +1
Me.YourIDField = lngID

To make sure your numbers are unique, add an Index to the underlying table
that includes the field and set Unique = Yes.

If you are asking about customizing the AutoNumbers field ... well, you can
always Format that field to DISPLAY the number any way you like, but the
underlying data will always be stored as a Long datatype. And, there is NO
guarantee that the values will be non-gapped consecutive numbers ... and
using an Autonumber field like that indicates what could be a serious design
flaw in your data.

 

[Ken Ismert]

Surya,

Use the following SQL:

ALTER TABLE tblTable ALTER COLUMN AutoID COUNTER (1000000,1);

The first number is the Starting value, the second is the increment.

That said, Doug Steele gives good advice, and you'll likely save
yourself trouble if you heed it.

-Ken

 

[Surya]

My email address is wrongly spelt in my earlier request.
The correct email address is: (e-mail address removed)
Thanks,
Surya

 

[John Vinson]

My email address is wrongly spelt in my earlier request.
The correct email address is: (e-mail address removed)
Thanks,
Surya

Than your request is doubly impolite. This is a public newsgroup,
staffed (if that's the word) by unpaid volunteers like me, who donate
our time to reply to messages. I'm a self-employed consultant; I'll be
happy to provide private EMail support at my usual consulting rate,
which I'll send you upon request. But if it's worth my time to
volunteer to answer your question here, it should be worth your time
to come back to the newsgroup for the reply!

An Autonumber is *not* appropriate in this case; the only function of
an Autonumber is to provide a meaningless, almost-guaranteed (there
are bugs) unique key. They will always have gaps.

Instead, use a Long Integer "custom counter" field, created using VBA
code. You must (no option, table datasheets don't have any usable
events) use a Form to enter your data. The code can be very simple if
it's a one-user system or if there is little or no likelihood of two
people entering a new record at the same time, or it can get pretty
complex in a multiuser system. The simple code would use the Form's
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtID = NZ(DMax("[ID]", "[YourTable]"), 1000000) + 1
End Sub