Insert a record above existing record

[rkmaitra]

Hello,
I have a table which has an 'Autonumber' field called 'Recno'. The table is sorted according to this field and I need to maintain this order for the calculated fields in a related form to work.
I have discovered that I have missed adding a record when entering the data and would need to add it in its correct position (Recno). Is there a simple macro for this?
The reason I need a macro is that this may recur when entering data at a later stage and I need a method to to insert a record into a particular record number when needed.
Thank you,
Rudra

 

[spikepl]

No there isn't. An autonumber is just a key generator, and you cannot rely on it being in any particular order, or even being positive.

If you want to have something for human consumption, you need to do it yourself. Search for custom autonumber in this forum

Update:

Besides, relying on SEQUENCE of records in a table to do something is a bit foolhardy unless you yourself create something on which the data can be sorted in a query so it always gets sorted in the same fashion.

 

[boblarson]

Autonumbers should not be used for this type of thing. They are not guaranteed to run in any particular order (if set to increment they USUALLY do, but they may not) and they can be negative or random. So, you need to use your own field if you want to maintain the order. Look up the DMAX + 1 method (there are a lot of posts here about it).

And no, there is no simple macro for this. You will need code and if you need to reorder any you will need either code or at least one update query (if not a couple) to accomplish the re-ordering.