If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Autonumbers
When I set up my Customer database 3 yrs ago the record
number corresponded to the Customer ID which was set to autonumber. At some point and I am not sure what, the record number no longer tracks the Customer ID, in fact it seems to be floating. The customer ID is still set to autonumber but every time I load the database a particular customer record will have a different record number. For example when I go the end of the records which is number 1666, customer ID 415 appears, and not customer ID 1666. I may have deleated some records but probably less than 10. I find this irritating because I can no longer find a record simply by entering the ID number in the record field, I can't even get close to the number - I have to search in the customer ID field. Is there any way I can correct this? |
#2
|
|||
|
|||
Autonumbers
1. The main (only?) purpose of an AutoNumber Field in to provide uniqueness
to each Record in the Table. The AutoNumber Field WILL develop gaps since when Records are deleted, the AutoNumber values are not recovered. Even when you abandon the entry of a new Record, the AutoNumber value allocated to this incomplete / abandoned new Record is not recovered. Thus, the AutoNumber Field value will NOT track the Record Number (my guess is that you meant the number X in "Record X of XX Records" in the Navigation Bar). 2. The current Record Number X is simply an ordinal position of the Record in the Recordset of the Datasheet / Form. If you have a Sorting order using Field(s) other than the AutoNumber Field, the order of the Records (i.e. the Record Numbers of Records) in the Recordset will almost certainly be different from the AutoNumber Field values. 3. Note that Records in a Table are stored unordered and the database engine will retrieve Records the most efficient way it thinks. If you want to sort Records in particular order, you need to explicitly set the Sort order. Access seems to sort Records according to the PrimaryKey Field (probably your AutoNumber Field is the PK Field) if no Sort order is specified. However, this is not guaranteed. In large Tables in SQL Server Back-End, I often see that Records are retrieved in no particular order if no Sort order is specified. It may not be easy to fix this, especially if you have "related" Records. You will also need to think of using something else rather than AutoNumber Field. -- HTH Van T. Dinh MVP (Access) "David" wrote in message ... When I set up my Customer database 3 yrs ago the record number corresponded to the Customer ID which was set to autonumber. At some point and I am not sure what, the record number no longer tracks the Customer ID, in fact it seems to be floating. The customer ID is still set to autonumber but every time I load the database a particular customer record will have a different record number. For example when I go the end of the records which is number 1666, customer ID 415 appears, and not customer ID 1666. I may have deleated some records but probably less than 10. I find this irritating because I can no longer find a record simply by entering the ID number in the record field, I can't even get close to the number - I have to search in the customer ID field. Is there any way I can correct this? |
#3
|
|||
|
|||
Autonumbers
David
To follow on what Van has already offered, it seems possible that you are using a table view of your data. To get a "sorted" view, create a query and use a form, based on that query. Besides, forms offer a rich event environment, where tables simply don't provide that kind of control. -- Good luck Jeff Boyce Access MVP |
Thread Tools | |
Display Modes | |
|
|