Compact & Repair and Autonumber issue

[Roncc]

Hello all,
I have a database that is used to track orders. As part of the system I use autonumber fields to create unique order numbers and receipt numbers. I getting some "not responding" messages in the database and would like to run a Compact and Repair to clean it up. The problem is that when I run the compact and repair I know it will reset the autonumber fields to 0 and I will end up with duplicate order and receipt numbers in my table. In the past I have manually fixed this kind of problem by creating a dummy table with the appropriate number of records which I can append (and then delete) to the autonumber source table which will reset it to the current number. Is there a simpler way to do this, to tell the autonumber system that I want the next number to be a specific number after it has been reset in a compact and repair event?

Thanks for the help.

Roncc

[kd2017]

The problem is that when I run the compact and repair I know it will reset the autonumber fields to 0 and I will end up with duplicate order and receipt numbers in my table.

This is not the case. While it will reset the counter it won't change the values in your records or cause duplicates.

Make a backup copy of the DB and give it a try.

[orange]

The only time that compact and repair will reset autonumbers to 0 is when you delete all records in that table/tables.. The table(s) must have all records deleted for it/them to have its autonumbers set to 0. After the C/R, the autonumber will start at 1.

[Roncc]

I must not have made myself clear on the issue, I apologize. The way the program works is as follows: I have a table called NewNumberTbl that has a field called Id which is an autonumber field and a text field called NewRecord. I also have a table called NewNumberTemplateTbl which is a single field called NewRecord. I have a third table called OrderTbl containing all the fields needed for ordering, including a number field called OrderNumber. When I want to place a new order I append the single record in the table NewNumberTemplateTbl to the table NewNumberTbl which sets the autonumber in the Id field to the next number. I then append the single record in the table NewNumberTbl to the table OrderTbl where the Id number is placed in the field OrderNumber so now I have a new order record with a unique order number. After I am finished with my data entry for the new order, I close the table and use a delete query to remove the single record in the table NewNumberTbl. When I run a Compact and Repair I am not worried about the numbers being reset in the table OrderTbl, I know they will not be affected however, the autonumber in the table NewNumberTbl will be reset so when I create a new order the numbering system will start over and I will get duplication in the OrderTbl.

[Micron]

Your problem is your mis-use of the auto number feature. I'm on my phone so I can't grab the links I use to point people to what auto numbers are and are not. I'm surprised that no one has taken that view yet. Suggest you research them or perhaps others will provide the usual go-to links. Not only that they are not to be used as meaningful data but neither should they be used to arrive at meaningful data.

[davegri]

The usual approach when the next order number is needed is to lookup the max existing order number in OrderTbl and add one to it.

[Micron]

As long as the generated number is not part of a composite field or index, that simple approach can work, otherwise the other part(s) of the index are also required. In a shared db the timing of the number generation becomes important.

[orange]

It is often helpful to include/attach a jpg of your tables and relationships.
Here is a link to a free, generic Customer, Order and Invoicing data model.

I don't understand the need for

NewNumberTemplateTbl which is a single field called NewRecord

but would be interested in knowing how you use this.
With Access most use autonumber PK in each table. As has been mentioned you can use a field (datatype number), then use Dmax(that field value) + 1 for the next record in that table.

Here is the link to the Autonumber info that Micron mentioned.


Good luck.

[Roncc]

I didn’t bother to explain the whole order program because I didn’t want waste people’s time. I simply wanted to see if anyone knew of a programmatic way to correct the reset of the autonumber system after a Compact and Repair event. To go into a bit more detail: the reason I use a multiple step system is that I may have several items (records in the table OrderTbl) that I want to have the same order number. For example; I may want to order paper, pencils, and pens from Staples. When I do this I create 3 new records in the table OrderTbl, one record for each item. I create a unique order number for these new records by appending the single record in the table NewNumberTemplateTbl to the (empty) table NewNumberTbl which creates a unique number in it’s (the single record in the table NewNumberTbl) Id field. I then use an update query to place that Id number in the OrderNumber field for each of the new records. The new records in the table OrderTbl have alogic field which defaults to True when the record is created, the new OrderNumber only populates those records in the table OrderTbl if this logic field is true,and once the OrderNumber has been populated I run an update query which sets all these logic fields in the table OrderTbl to false. When I am done I delete the single record in the table NewNumberTbl and I am ready to use it for the next order number.

[Roncc]

I don't think I'm misusing the autonumber feature as I am using it to create a unique number for each iteration. I don't care if they are sequential, I only care that they are unique. I posted some additional details in another response. Thank you for taking the time to assist though, I really appreciate it.

[Roncc]

I figured out how to use the Dmax function suggested by davegri and Orange. I think I'll redesign things to work with that.
Thanks for all the suggestions and the time you all spent working on my issue. I really, really appreciate it.

Roncc

[ArviLaanemets]

To go into a bit more detail: the reason I use a multiple step system is that I may have several items (records in the table OrderTbl) that I want to have the same order number. For example; I may want to order paper, pencils, and pens from Staples. When I do this I create 3 new records in the table OrderTbl, one record for each item. I create a unique order number for these new records by appending the single record in the table NewNumberTemplateTbl to the (empty) table NewNumberTbl which creates a unique number in it’s (the single record in the table NewNumberTbl) Id field. I then use an update query to place that Id number in the OrderNumber field for each of the new records. The new records in the table OrderTbl have alogic field which defaults to True when the record is created, the new OrderNumber only populates those records in the table OrderTbl if this logic field is true,and once the OrderNumber has been populated I run an update query which sets all these logic fields in the table OrderTbl to false. When I am done I delete the single record in the table NewNumberTbl and I am ready to use it for the next order number.

This approach is faulty!

You need a table tblOrders with autonumeric OrderID, with fields unique for order, like OrderDate, OrderNumber, ClientID, etc.
And you need a table tblOrderDetails with autonumeric OrderDetailID, and Long Integer foreign key OrderID, where the value of OrderID from tblOrders is stored to link every order row to order. In this table you'll have fields unique for particular order row, like ItemID, Quantity, Price, etc.
For every order, there'll be only one row of data in tblOrders. For every row in tblOrders , there'll be one or several rows with same OrderID in tblOrderDetails.