Not the same autonumber problem

[madcats]

I have an access database that I have used autonumber to assign order numbers for several years. I am now wanting to split the database and it is causing a problem with the order numbers. My situation is different than other posts because I delete orders from the table when I assign serial numbers, but I still do not want to use that order number.

Example:
Enter order number 1000
Enter order number 1001
Assign a serial number to Order 1001 and delete that record.
Enter a new order, need order number to be 1002

Because I delete 1001 but still do not want to use 1001, I can't use any max number function that I've seen in some other posts.

Could I make a table to somehow keep track of the next order number. I am not very knowledgeable about access so please bear with me.

 

[mikebaldam]

Dont Follow You Sorry.....

I'm not sure if I follow you...?

Thats what the autonumber field does....

Its will not re-use the same number...

How about having an OrderActive Yes/No Check Box and then you can Deactivate the orders rather than delete them...?

Mike

 

[madcats]

Mike,
Thanks for the reply. The autonumber has been working fine, but now I want to split the database and the linked table doesn't seem to pick up the autonumber correctly, it went back to one when I tried to add new order.

I have thought about making the order inactive, but there reasons that it would not work very well in this case.

 

[RV]

You should never use autonumbering to generate order numbers or similar document numbers.
Autonumbering has one and one purpose only, being to uniquely identify a record.

You should create an user-defined sequence.
Search the forum and you'll find various threads that will provide you with a solution.

RV

 

[The_Doc_Man]

RV is absolutely right. An autonumber has a very limited purpose in life. You are exceeding that purpose by making the autonumber have external meaning. Literally, if you want unique numbers that can go away and then split the DB in a way to break the uniqueness, your numbers that went away by themselves will come back by themselves.

Why? Inherent design flaw. Numbers can GO AWAY yet (because you don't want them duplicated) they STILL HAVE MEANING. This cannot happen in Access (or any other database for that matter) because... if the number still has meaning, it has to have a repository for that meaning - even if the meaning is "this number has been used already."

This is a deep logical concept having to do with proving a negative. There can be two reasons why a number isn't in your DB. (1) It was NEVER in your DB or (2) It was REMOVED from your DB. Without some tangible record of used numbers, you can NEVER decide between the choices infallibly. Access can't do it either because when you split the DB and the numbers starting duplicating, it was because Access could no longer trust its (absent) record of what had or had not been used before.

So how do you make this work? Well, ... how about a table of programmatically generated record numbers that point to the actual records based on their autonumber key. So you join the programmed record number to its record via a JOIN on the autonumber key from the real table. Now, when you retire a real record, blank the FOREIGN KEY field in the table of programmed record numbers. Make the JOIN an INNER join so the absent records won't show up. BUT when you go to enter a new record, you'll have the table of actual record numbers to prevent you from assigning duplicates.

The basic principle at work here is that a database cannot tell you anything you didn't tell it ... OR anything that you told it to forget. So the trick is to learn to tell the database to remember that you told it to forget something. Have I lost you yet? 'cause I think I'm losing myself.