Auto number automatically next number

[wnicole]

Can you assist?
My db has job number ex: 88965, 88966, etc. When I create a new job i have to remember the last...is there an easier way?


Either a code to automatically create my new job number when I create a new job?

Appreciate it.

[mrojas]

One way of doing this would be to create two new tables, one with one numeric field (long), let's call this the Seed table; the other identical to your existing one with the exception that it would also have a new field, a key AutoNumber, let's call this the NewJobNumbers. Let's call the original job number table OldJobNumbers.
You may want to give the new autonumber field a meaningful name, JobNoID or similar.
Note the lowest job number you have in the OldJobNumbers table; jot this down.

In the Seed table add a new record with its numeric field equal to the lowest job number from OldJobNumber table minus 1.

Using a query, append the one record in the Seed table to the NewJobNumbers table.

Again, using a query, append all records from the OldJobNumbers table into the NewJobNumbers.

From that point forward your NewJobNumbers table will have an autonumber field that will automatically increment with each new record added.

[wnicole]

This one is a little scary. Don't wAnt to lose info. Let me ask first.
1. Am I changing the names of the table...(currently job number table)
If so this would effect everything connected to that name correct?

[mrojas]

No, you won't lose any information. I would recommend trying it out on a copy of your database.

You would make a copy of your original job numbers table and work with it. Once completed, rename your original table to something else, and rename the new table as the original table.
For example, let's say your original table is named tblOriginalJobNumbers, make a working copy, tblOriginalJobNumbersCopy, and rename tblOriginalJobJumbers to tblOriginalJobNumberMaster. You now have two identical tables with all your original job numbers, but each table with a different name.

Your seed table named tblSeed, your table to be used as the recipient table named tblNewJobNumbers.

Once completed, rename tblOriginalJobNumbersMaster to tblOldJobNumbers and the tblNewJobNumbers to tblOriginalJobNumbers.
Now you'll have three tables with the same information, tblOldJobNumbers, tblOrigianlJobNumbersCopy and tblOriginalJobNumbers.

Verify that all three are identical and delete the tblOriginalJobNumbersCopy.

Once you're comfortable with the results, delete the tblOldJobNumbers table.
You'd be left with only one job numbers table, tblOriginalJobNumbers which has the auto-number field.
Keep in mind that this new table will provide you with a new auto numbered job number, but you won't have control of changing job numbers, or skipping numbers.

As with everything in life, there are advantages and disadvantages to doing this, and there are other ways of accomplishing the same.