Appending New Data in Between Two Records

[VBVA]

I have never used Access before, but I have moderate experience with VBA and here is what I want to do:

I wish to insert new data (location, construction project title, job number, etc) in between two existing records in a table or query. I am having trouble running an SQL or ID # counter to archive new info. I can add the record to the end of the db, but I can't get it to insert in between two existing records while re-autonumbering the database. Anybody have any simple code I can reference for this problem?

 

[MarkMcD]

Sounds like you need to have an extra field on your table called something like "intOrder". Populate this field yourself and then sort by it. If you populate it with increments of at least 2 you then have room to insert another record.

If there is potential to insert more than record, you will have to write a routine to loop through all records (sorted by intOrder) and reset the intOrder with your increment.

Then, any time you read this table, order by the intOrder field and you will get it in YOUR order and not the physical database order.

Hope this makes sense.
Cheers,
Mark.