I have a form used to update orders, ie: the user enters ,Order No, Qty, Order finished (yes/no). when an order number is entered it also updates an AutoNumber field within the same table used to identify the specific record for later use. The only problem is if a user enters an order number the AutoNumber field updates aswell which it is meant to do ! BUT if the user decides not to carry on with the update for one reason or the other the next time he does decide to update the form the AutoNumber goes to the next Number. My question is: If half way through an update the user decides to cancel and not update is there a way to reset the AutoNumber so the number can be used next time.
If the order number is unique (as it should be !) then why not use the order number field as the primary key and dispence with the autonumber.
I have used this for order info without any problems. If the relationships are defined in the DFB stucture with auto update set, then, if a mistake is made entering a number, it can be changed an all the related tables will automatically update to suit!