automatically re-number records in a query

[jtabb]

I am using an Access table to create a "Table of Contents" for AutoCad drawings. I want to control the sequence in which the drawings are listed in the "Table of Contents" by simplying assigning a serial number to each record, e.g., 001 through 999. When I insert a new drawing in the middle of the sequence, I want to automatically re-number each record, so that I can sort on the "serial number" and have the drawing appear in the desired location on the "Table of Contents" list of drawings.

How do I automatically re-number my serial number column after inserting a record?

 

[PaulBricker]

I would assume that you need to use VBA to go thru the recordset and reassign the values but there are going to be caveats.
1. Do you have consecutive values from 001 to N for the records in the table, or do you have gaps in the numbering?

2. How do you insert a record into the middle of a table? Do you give it whatever number you want even if that number is assigned to another record, and then look to renumber everything above that?

A few more details, and we should be able to work out something.

Paul

 

[jtabb]

Reply to Paul Bricker:
Thanks for your response.
1. The serial numbers are consecutive, w/o gaps.
2. My manual method of inserting a record is to add an alpha character to the number, e.g., serial number: 101A. I then sort the column, and manually begin re-numbering from 101A on. 101A becomes 102, and every record past that is incremented by 1.

 

[earthandfire]

Obviously, from what you say the data type for your serial number is Text. Would it be possible to change it to Integer? That would make a solution much easier and you wouldn't need to worry about VBA, just a couple of queries, which you could create in the queries pane.

 

[jtabb]

Changing to an integer is not a problem.

 

[ProgramError]

What about adding an additional field with the sequence of number incremented in steps of 10. that way the serial number will always be the same for the coresponding autocad drawing and you could move the position of the drawing within the table of contents by changing number in the sequence number field.
for example record 50 could be changed to 11 and it would appear between records 10 and 20. Then It's just a case of updating the sequence field again wth gaps of 10.

I recall we use to have this problem in the days when we use to have line numbers when programming....

Ian M

Program Error
Programmers do it one finger at a time!

 

[earthandfire]

Great.

Once you've done that refer to thread702-1032542 the queries that you need were placed there about half an hour ago by hkaing79, so there's not much point in me duplicating them.