Auto Generation of Numbers

  • Thread starter Thread starter Clareman
  • Start date Start date
C

Clareman

Issue numbers are automatically generated in the 'Live' issues table. Once
issues are resolved they are archived into the 'Closed' issues table.

We have reached a particular number (for example 10660) and for some reason
the numbers now being generated are for the range (9900) and these numbers
have previously been used and archived to the closed table.

Can anyone suggest why this would suddenly start happening and advise how we
can continue with the 10660 range of numbers?

Thank you.
 
1. Moving records from one table to another depending on their status is
usually a very bad idea. In fact you might be finding out why. It's much
better to have a Yes/No field named something like IssueClosed. Then do
queries based on that field to return only the Yes or No data as needed.

2. What autogenerates these numbers? Is the field an autonumber datatype or
is there some fancy code that creates a new sequence number when a record is
added? In a similar vein, what moves the records from one table to another.

3. If it's an autonumber field, there can be a few reasons for this to
happen. An autonumber is only guarenteed to be unique within that table IF it
is used as the primary key field. There's two points to consider. First that
it needs to be the primary key and, second, it's only unique within that
table. See my comment #1 above. As you are moving records from one table to
another.....

4. There's also a known bug with autonumbers and some versions of Access.
However applying all the service patches and services releases fixes that bug.
 
Thanks for your response.

We appear to have reached the number 10642 amd for some reason it has
decided to auto generate from 9900 range?

On the 'Live' issues table the data type is Auto Number and it is the
primary key. It is indexed to be Yes (No Duplicates) and New Values set to
Increment. On the 'Closed' issues table Indexed is set to No.

Do you know if there is a setting where we can continue generating the
number from the 10642 range or if the installation of Office 2007 would
resolve this problem?

Thanks for your help.
 
Sorry I meant to mention that we have a field for 'Closed Issues' Yes or No
of which we query on but we have auto archive in place which moved the issues
from the Live to Closed table. Not sure how this was setup but we can select
a button and messages appear to auto archive.

Your help is appreciated.
 
With an autonumber set up as the primary key, the only thing for (nearly)
sure it that Access will not duplicate the number within the same table. It
doesn't not guarentee that the number will be sequential or not reused if the
previous number was deleted. Access can basically chose any number that it
wants for a new record including a negative number!

Now if you find the same number in the table more than once, there is a
known bug that an update can fix. However if it just choses a lower number or
skips a few, and that is a problem for the database, you probably shouldn't
be using an autonumber.

Upgrading to Access 2007 will not 'fix' the problem.

What you can do is to do a DMax of a number field and add one to it when
creating a new record. You'll need to create all records using a table. Also
you'll only be able to create one new record at a time so append queries are
out of the question. There is also a problem with having more than one person
adding records at the same time.

If the lack of sequential autonumbers is just an irritant and not a real
problem, I'd just live with it.

BTW: setting New Values set to Increment is, as you found out, bogus. It
really doesn't. Now if you set them to Random, it will mix the numbers up
better.
 
Moving from the Live to Closed tables is probably made the problem come to
light. If you move or delete records from a table then do a Compact and
Repair, Access forgets what numbers have been used in that table. Therefore
it can start using the 9900 range again. I'm assuming that the 9900 range was
moved to the Closed table in the past.

Still keeping all the records in one table, a very good idea, is not 100%
positive to keep the autonumbers in order.

As far as autonumbers are concerned, there isn't a problem unless Access
tries or succedes in putting the same number in the field more than once in
the same table when creating a new record.
 
Thanks again for your response. The 9990 range has previously been archived
so the only problem we will have is duplicate issues in the Closed table.

Thats why it would be ideal to continue from the 10600 range - do you know
how to set numbers to start auto generating from this range? You mentioned
service patches and service releases would resolve - do you think upgrading
to Office 2007 (we are on 2003) would help with this problem?

Many thanks.
 
We have carried out the compact by going to Tools, Options, General and
Compact On Close and this has resolved the problem. The numbers are now
continuing from the higher range of numbers.

Thanks for your help.
 
Back
Top