Autonumbers

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

[Rick Brandt]

Re: Autonumbers

T23459 wrote:[color=blue]
> 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.[/color]

First off if the first (and only) alpha character is always "I" then it doesn't
need to be stored at all. You can display a leading "I" with formatting. This
would allow you to change the column to an actual number field which then
greatly simplifies the next step.

Given a number column you can use an event in the data entry form to
automatically determine the next number by finding the highest existing value
and adding 1 to it. In multi-user situations BeforeUpdate is the event best
suited to this task because the record is saved almost instantly after the new
number is calculated.

If Nz(Me![Job No], 0) = 0 Then
Me![Job No] = Nz(DMax("Job No", "TableName" ), 0) + 1
End If

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com