Auto Numbers

[T23459]

I am using Access 2000.
I have a field called Job No.
There are already a bunch of records stored in this field. They begin with
the letter I.
ie. I2345 I2346 etc
I would like to now automate the numbering system where the data entry
person starts a new record and access give you the next job number
sequentially.

In the Data Type section of the table design... i have tried using the
Autonumber selection but it won't give me the flexibility to start
automating the numbering at a given number.
eg. Begin with job no. I2345 and from then it would start automating
the sequence from that number.
Any help would be appreciated.

 

[Joseph Meehan]

I am using Access 2000.
I have a field called Job No.
There are already a bunch of records stored in this field. They begin
with the letter I.
ie. I2345 I2346 etc
I would like to now automate the numbering system where the data entry
person starts a new record and access give you the next job number
sequentially.

Auto number does not provide sequential numbers. It provides unique
numbers that are usually sequential. There are other ways of providing
sequential numbers if needed. I don't have the code handy, but basically
you program it to look at the last records and add one.

Also auto numbers can not include letters. Of course there is no reason
to include a letter if all you want it s a number. You can make it look
like it has a letter in displays or if the letter is not always the same you
can place it in another field and combine the two fields when displaying it
as needed.

There is no way a letter can be included in a number filed. Letters are
not numbers.

 

[John Vinson]

I am using Access 2000.
I have a field called Job No.
There are already a bunch of records stored in this field. They begin with
the letter I.
ie. I2345 I2346 etc
I would like to now automate the numbering system where the data entry
person starts a new record and access give you the next job number
sequentially.

In the Data Type section of the table design... i have tried using the
Autonumber selection but it won't give me the flexibility to start
automating the numbering at a given number.
eg. Begin with job no. I2345 and from then it would start automating
the sequence from that number.
Any help would be appreciated.

As Joseph says, an Autonumber isn't suitable in this case.

I'd suggest creating a new, empty table. Make its Primary Key a Long
Integer field; I'd name it JobNo (it's best not to use special
characters or numbers in fieldnames), and set its Format property to

"\I0000"

to display the value as I0001, I0002, ..., I9999. (What you'll do with
your 10000th record is something you'll need to resolve).

Run an Append query appending your current data into this table; into
this new field, append

Val(Mid([Job No], 2))

This will extract the numeric portion of the field and put it into the
new Long Integer job number.

Finally to get the automatic counter effect, you must - no option! -
use a Form to do all your data entry (table datasheets don't have any
usable events). In the Form's BeforeInsert event click the ... icon,
invoke the Code Builder, and put

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!JobNo = DMax("JobNo", "your-table-name") + 1
End Sub

John W. Vinson[MVP]