So, when I now list the members, the membership numbers appear as 1, 2, 3, 5, 6, 7, 4, 8, 9. while the member numbers continue to appear in their correct order (ie 1 through to 9).
In this situation, how can I change the record numbers - without changing the member numbers?
Or should I just change the member numbers?How do I change record numbers in an Access database?
I assume the record number bares some kind relevance to your program. Because normally you would not drive your logic by the record number but rather by the unique key.. in your case membership number.
First I would sort membership number in ascending sequence. Close and reopen the table and if this is the primary key it should sort itself out.
Hope it helpsHow do I change record numbers in an Access database?
Why on earth use a different member number field? The whole idea of an auto-number field is to be the key field of the table. You are now better to re-write the system to use the key field in stead. Then remove the unneeded field. It is better never to delete an entry from any database, for complex use it can leave serious data inconsistencies. You should have a boolean or integer field to use as empty or 0 for valid entries, and on or number 1 for an obsolete entry. This way it is always available to link to other tables without breaking any queries or code, even if the member is no longer valid. Your queries can read only entries with the or empty value.
