Help Please, AutoNumber Field

[madcats]

I have an autonumber field that somehow got reset back to one. Is there anyway to reset this number, I need it to be back around 1900.
This is a big problem because I use the auto number as an order number and also use that number to link the options selected to that order. Now that it has reset to one, it is picking up options from the original order number one.
Any suggestions?

Addtl Info. This is in an Order Table. When the order is ready for manufacturing it is assigned a serial number and the order is deleted from the order table and put into a production table.

 

[ghudson]

The AutoNumber field type should never be used like you are using it. If the number must be sequentially unique then you need to create your own "autonumber" function. Search around the forum for there are a few options I have seen before that will show you how to create your own autonumber and there are ways [previously posted] that you can reset your autonumber back to 1900.

FYI: Compacting an empty table will reset the built-in table field type autonumber back to 1.

 

[jsanders]

Copy the table and paste the structure to a new one.
In the new table change the autonumber field to a number field.
Add a record in the new table and insert 1900 in the new number field
Paste the new table back to the original

 

[jsanders]

Hey G,
Why not use the autonumber as an order number?
Been doing it for years.

 

[RV]

Hey G,
Why not use the autonumber as an order number?
Been doing it for years.

Autonumbers have got one and one purpose only, to uniquely identify a record.
Autonumbering comes with gaps.
As for any document sequences, it's required by fiscal and / or accounting legislation and rules that any document numbering is both sequential and gapless.
Hence you should always implement your own sequential numbering.

I guess you haven't been audited for years

RV

 

[madcats]

Thanks for your replies. The odd thing is that the table was not compacted or was it empty. The user said that they just recieved some sort of error and they exited the form. Very puzzling. I am really needing the autonumber for it's uniqueness not for any sequential reason. This is an internal company procedure to get the order ready for production.

 

[ghudson]

You will not have to worry about the autonumber table field type resetting itself if you use a custom function to create each new number. I always set the format of my autonumber to "random" since the number is meaningless except to "uniquely identify a record" as RV correctly mentioned.

 

[jsanders]

The only time I have ever seen a “gap” in auto-numbering is when a record is deleted. Even if you create your own, it will still be a missing record.
So how does that solve the problem of non-sequential numbering

 

[RV]

The only time I have ever seen a “gap” in auto-numbering is when a record is deleted

How 'bout a user entering data using a form, then figures for whatever reason to, for instance, navigate out of the current row ending up entering no new record at all.
Well that is how the user experiences it, as the autonumber will have been issued.
That most definitely will create gaps..
Meaning, you can't re-use the spoiled autonumber and I can tell you from experience, that could cause rather nasty auditing issues.

Even if you create your own, it will still be a missing record. So how does that solve the problem of non-sequential numbering

No it won't as, in contrary to autonumbers, you can "control" issuing self-defined sequential gapless numbering.

Real databases, such as ORACLE, always use defined gapless sequential numbering or standard functionality to set up such as per the reasons mentioned in my previous reply.
And never ever a ROW_ID...

Now opinions differ but my personal opinion is, if you do not want to take any changes, always define your own sequences for all data that will or could be subjected to accounting and / or fiscal rules and / or auditing.

RV