How to insert a new record in a form and keep the order how i want it.

[ruthcali]

i am using Access97.

There must be an easier way to do this!

I have a form with records that need to be kept in order. for example, i have:
record 1: BA48
record 2: CZ48
record 3: AG48
record 4: ----
record 5: BQ48
record 6: YR48

Now, let's say i need to enter a record after CZ48. How can i do this? if i hit New Record, then my new record gets entered as record 7.

That won't work because the order is very important. It is showing what system is connected to what. (So, YR48 is connected to BQ48, then there is a break, then AG48 is connected to CZ48 and CZ48 is connected to BA48).

one way of accomplishing adding a new record after CZ48 is to add a number field called Record to the table and manually enter a number in that field. then, to add a record after CZ48, i would change record 6 to 7, record 5 to 6, record 4 to 5, record 3 to 4, keep record 2 the same and record 1 the same. Then, I would enter a new record and call it record 3.

Doing all that would result in my new record being after CZ48.

But there must be an easier way to do this! What if i have 50 records and i want to add a new record after record 2!! I can't ask the user to change record 50 to 51, 49 to 50, etc.

help.

thanks,

 

[Guest_imported]

since order is important I suggest you use a parent id field then fill your forms using recursion. See MS KB article id Q132242.
Or you could use a number scheme like a5 a10 a15 b5 b10 b15 but this would create problems later on if the db gets too large