Compacting causes problems with Autonumber fields! 2

[StuMunro]

In my Access 97 database I have several tables such as customer/products all linked to a current orders table. When a new order is placed a new record is created in the current record table, this is unique by using a AutoNumber field.

Current Orders table

OrderID CustomerID OrderDate etc.
--------------------------------------------


When an order is complete I move the record to a completed orders table which is identical in structure to the current orders table, except that the OrderId field is no longer an Autonumber, as the existing order number is used. The reason for moving the record is so that the current order table is limited in size to the only the current orders making access speed reasonable.

My problem occurs when the database is compacted as the Autonumber field is reset to the next number after the last Order in the current orders table. If the last completed order had the highest order number then after the compaction this order number will be reused for the next new Order.

When this new Order is completed it will fail to move the record to the completed table as the Order Id already exists in the completed orders table.

I can not use a different Order no in the completed orders, as the user may want to search for a particular order no, in which case I do a union query between the current orders and the completed orders table, so they must be unique between the 2 tables.

Can anyone help with a simple solution as I have only realised this at a late stage!

 

[Aivars]

Hi!

By using of Autonumber proceed lot of problems when you are needed to export/import data as well as in cases like your one. Because I never use autonumber but always use Long or Integer data type fields. For new record update from form I write in the procedure Form_BeforeInsert following codes:

private sub Form_BeforeInsert(cancel as integer)
if dcount("OrderID","MyTable&quot=0 then
me.OrderID=1
else
me.OrderID=dmax("OrderID","MyTable&quot+1
endif
End Sub

Aivars

 

[StuMunro]

I have been shown a solution to the above problem.

When using autonumber fields as in the above set the option to generate a random number rather than an incrementing number as this will not reuse a previously used autonumber.

stu

 

[THoey]

Stu,

That will help for records in your active table, but there is still the chance of duplicating the number of one in your completed table. What Aivars was trying to tell you is the best way. Do Not Use Autonumber for any "usable" value. Just use it as a key. You will have gaps, the possibility for duplication (as is your case), a lot of other problems when you try to use it as data. I suggest you create your Order Number, you can keep the OrderID, but I wouldn't use it... Terry M. Hoey

 

[Spellman]

What we do in my company instead of using Auto numbers is have a control table which stores the next ID number. This is incremented every time we add say an order to the table and then the next order that is added picks up the next order number from the control table. That way you are gauranteed no duplication. It's crude but it works.

 

[K9Boxer]

Just a note on AutoNumbers.... If using Random generation, negative numbers are also generated. FYI