Pam

Add a new field to your table. This will be your new "job number"

Create an update query that puts the Autonumber value in the new field.

In your form based on the table, add in the "custom autonumber" routine
described earlier.

I still can't tell from your description how you propose to tell apart two
jobs with the same job number, which it sounded like you were trying to
figure out in your original post. Tell me again why you feel you need to
"start over"?

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Pam" wrote in message
...
Jeff,

Thank you for the suggestions. Per your suggestion:

"Rather than start another database, and have to look in more than one
place, what about the idea of creating a new field that holds [JobNumber].
You can start out filling it with the values you already have in your
Autonumber field. Then you can use the custom function to generate new
[JobNumber] values, and let the Access Autonumber field continue to be the
primary key ... it IS the primary key for your records, right?"

Will you please explain further how I may use this? Are you saying I
could put the job numbers that are still open and in use in an new field
along with the new list of numbers?

Like this:

11095 open job
11096 open job
1000 new job number sequence

If this is possible, how might I go about doing so?

And yes, the Access Autonumber field is the primary key. We've been using
this setup for about 9 years now. It's not critical that we have a job
for each and every number. If a number is deleted, we just go to the next
number available. The jobs aren't tracked as closely as say a list of
purchase orders where they all need to be accounted for.

Thanks for your help.
Pam


"Jeff Boyce" wrote in message
...
Pam

First, if you are referring to the Access Autonumber data type, be aware
that it really isn't fit for human consumption. It's designed to provide
a unique row identifier ... that's it.

If you/your users need a sequential numbering system, you'll need to
"roll your own" (but just search on "custom autonumber" for several idea
on how to).

You/your users may have a more serious issue though ...

If you are identifying your jobs with a job number, and if you "start
over", how will you be able to tell the difference between Job Number
12345 (from 1993), and Job Number 12345 (from 2010)?

Rather than start another database, and have to look in more than one
place, what about the idea of creating a new field that holds
[JobNumber]. You can start out filling it with the values you already
have in your Autonumber field. Then you can use the custom function to
generate new [JobNumber] values, and let the Access Autonumber field
continue to be the primary key ... it IS the primary key for your
records, right?

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"Pam" wrote in message
...
Hi,

I have a table that holds information for jobs. The job number is an
auto number field. It has grown to a 5-digit number and users want to
start over at 1000 due to problems with number spaces in accounting
system. I could start a new database, but the problem is with jobs that
are still working the the current db. That would cause users to run two
db's until the jobs in the first db are completed. I thought about
making the current job number field a regular number and then entering a
new auto-number field for job numbers, but how would they search for
both the 5-digit numbers and the new numbers?

Does anyone have any suggestons as to how I can start a new list of
numbers?

Any help is appreciated.
Thanks,
Pam