AutoNumber and Record Number are Out of Sync

[Guest_imported]

I created a table with my first field utilizing the AutoNumber feature and then created a form to input information. We input orders into the form and use the autonumber as our order ID. We have to frequently go back into the same record in the form to update order information and we type in the record number at the bottom to bring up the record because it matches the order ID. One of my employees went into the data table yesterday and deleted a record, which caused the autonumber and record number to get out of sync for every record past the one that she deleted. How can I correct this without blowing up my database? I'm trying to sync back up the record number and autonumber. Thank you!

 

[GoDawgs]

I have not found a way to do this unfortunately, I ran into a similar problem when i had to change a field from autonumber to number and then back again, everything got out of sync. In your case it sounds like you have duplicate information though, I would get rid of one of the two columns and just stick with one order number. Hope that helps...

Kevin

 

[Craig0201]

But the question is why bother???

There is no need for the IDs to be synchronised with record numbers....

Craig

 

[JoanneM]

Hello there

Before you do anything else make sure you have a backup.
Then these steps will get you out of the problem

1 Get every one out of the database except you

2 Note If your original table has a primary key, you must
temporarily remove the primary key before running the
append query. Also, if your original table contains
fields that have the Required property set to Yes, the
Indexed property set to Yes (No Duplicates), or field
and/or record ValidationRule property settings that
prevent Null entries in fields, you must temporarily
disable these settings


3 put the following piece of code into a module and run it
& give the same name as the AutoNumber field in the
table whose value you want to change to the field created
I used OrderID

sub MkTmpTbl4AutoInc()
dim strsql as string
strsql = strsql = "CREATE TABLE AutoincTmp (OrderID long );"
DoCmd.RunSQL strsql
end sub

This creates your temporary table

4 In Datasheet view of your temporary table enter a value
in the Number field that is 1 less than the starting
value you want for the AutoNumber field. For example, if
you want the AutoNumber field to start at 100, enter 99
in the Number field.

5 Create and run an append query to append the temporary
table to the table whose AutoNumber value you want to
change.

6 Delete the temporary table.


7 Delete the record added by the append query.


8 If you had to disable property settings in step 2, return
them to their original settings.



Good luck

regards

Jo

 

[jebry]

Hi!

I agree with Craig, except he didn't say it strongly enough. If you are using the autonumber for anything that the user needs to see, use, type in etc., then you should not be using autonumber. Autonumber is designed to be a way to uniquely identify records which do not have a natural, easy to use primary key. It should always be internal to the database and manipulated only in code, never manually. Access does not respect the autonumber and will change it in various circumstances. Access will maintain its internal consistancy, but any external records(things in your files or papers sent to customers) will no longer match up to the program. Things like an order number need to be produced in a way that will keep them stable.

hth
Jeff Bridgham
bridgham@purdue.edu

 

[gusbrunston]

Hi.
My favorite way to increment order numbers is to put this in the Default row of the Data page of the property sheet for the control:

=DMax("[OrderNumber]","tblCustomers")+1

It finds the the highest used order number, adds 1, and assigns that number to the new order. The control can be set to Enabled=No, so the number can't be changed. If a record is deleted, the other order numbers are not changed. Gus Brunston An old PICKer, using Access2000. I manage

http://www.rentdex.com.

Suggestions welcome. padregus@attbi.com

 

[Laois]

I think you all missed the point here. KDJUST is trying to keep the record numbers displayed in the navigation bar at the bottom of the form with the Autonumber.

KDJUST there is no connection between the record numbers and the Autonumber and you cannot keep them synced. The record number is not connected to the record it is just the position of the record in the Recordset and this can change if you resort the form or you delete a record.

Synced
AutoNumber Record Number
1 1
2 2
3 3
4 4
5 5

Now lets sort by AutoNumber Descending
Not synced any more
AutoNumber Record Number
5 1
4 2
3 3
2 4
1 5

Now lets delete record with Autonumber 3
Not synced any more
AutoNumber Record Number
1 1
2 2
4 3
5 4

Forget about the record number it means nothing. Use a combo box to look up orders on your form.

Good Luck

Laois