IF that field ISN'T used as primary key for relationship:
1) sort the records by your actual "sequential number" field
2) put the cursor into the field of the first record
3) do a Records >> Replace Field Contents... >> Replace with serial numbers checking the box "Update serial numbers..."
Note that this command isn't undoable, so do it on a copy of the DB.
Thanks for your response, raybaudi. Unfortunately, the number field IS used in relationships to other tables.
I think I may have found a solution, however. I added a new text field to my existing table, and used Replace Field Contents so that it reads "continue" (I chose the word arbitrarily) for every existing record. I also set it so that the same word would be auto-entered into any new records.
Then I created another table with just two fields:
1. A number field that follows the same pattern as my existing table.
2. A text field that uses the number field to "look up" the contents of my "continue" field from the first table.
I linked the two tables, and then I started creating records in the new table. Eventually I intend to have approximately the same number of records in both tables. I only have a couple of hundred so far, but it seems to be working: I performed a Find (in the new table), setting it to Omit all the records that have "continue" in the text field. All that's left are the numbers that were "skipped" in the original table.
I'll be the first to admit that it's an inelegant solution, but in some ways it's better than what I first envisioned. At least this way I can continue to track new skips, and also remove the old skips as they are used for new records. Much better than a static list that I would have to keep track of by hand. But I would still welcome any new ideas!
I would be very very careful here. In my book, the minor savings from using all your labels is not worth the risk to you data integrity due to trying to assign new records to past "skipped" ID numbers.
What I would do is use two fields to keep the primary key value separate, hidden and thus can have as many "gaps" as possible. I'd then use a second field as the source of values for the labels you will print. You can then use Replace Field Contents with the serial number field to assign a gap free sequence to this new field for use in your labels and you can then experiment with design options to avoid future "gaps" without any risk to your relationships.
I have faced this in the past.
1) Created another table (Table2) with all the serial numbers.
2) Create a calculated field in each table such as zz_FlagX and set the calculation to equal "X".
3) Link the two tables using zz_FlagX.
4) Create a layout using the new table with the serial number and the linked field Table2::zz_FlagX.
The missing serial numbers will show up as empty fields.