Auto Numbers

T

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.
 
J

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.
Click to expand...

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]
 
Top