Renumbering or inserting

[Jwil]

Hi,

I need help renumbering my records. I'm new to Access so I'll explain what
I'm doing and if there is a better way please let me know.

From a form the user can either choose to create a full set of tasks or a
reduced set of tasks. These tasks show up on another form called "traveler".
I made two tables. One table has the full tasks (80 records) and the other
has the reduced tasks (30 records) But they aren't really records per se
they're more like 5 word instructions that tell someone what to do. Anyway,
when they click the full button I run an append query that appends the full
tasks table to another table that is the subform of the traveler form. and
the same if they hit the reduced button. I also add the current record ID
which ties those tasks to the record they were creating. These tasks however
aren't set in stone but just a starting point. The user needs to be able to
add and delete the tasks and they need to stay in a specific order.

How could I add a task (record) in the middle of the table? I'm not using
an autonumber for the full or reduced tasks table. And the tasks aren't
unique but repeated several times. So I was thinking that I could maybe pop
up a texbox that lets them input what they want to add and then it would ask
what record number and then I could just renumber everything after that
number and then sort. To the user it would look like they inserted a task.

Thank you very much

 

[John W. Vinson]

Hi,

I need help renumbering my records. I'm new to Access so I'll explain what
I'm doing and if there is a better way please let me know.

From a form the user can either choose to create a full set of tasks or a
reduced set of tasks. These tasks show up on another form called "traveler".
I made two tables. One table has the full tasks (80 records) and the other
has the reduced tasks (30 records) But they aren't really records per se
they're more like 5 word instructions that tell someone what to do. Anyway,
when they click the full button I run an append query that appends the full
tasks table to another table that is the subform of the traveler form. and
the same if they hit the reduced button. I also add the current record ID
which ties those tasks to the record they were creating. These tasks however
aren't set in stone but just a starting point. The user needs to be able to
add and delete the tasks and they need to stay in a specific order.

How could I add a task (record) in the middle of the table? I'm not using
an autonumber for the full or reduced tasks table. And the tasks aren't
unique but repeated several times. So I was thinking that I could maybe pop
up a texbox that lets them input what they want to add and then it would ask
what record number and then I could just renumber everything after that
number and then sort. To the user it would look like they inserted a task.

Thank you very much

Tables *have no order*. They should be viewed as an unordered bucket of
records.

If you want to present the tasks in some particular order, then you must - no
option! - include a field in the table to specify that order, and use a query
sorting by that field to present the records to the user. There simply is no
"record number" concept in the table.

If you want flexibility in specifying a sort order, you can use either a Long
Integer ID field with big gaps - start with 16, 32, 48, 64, 80 as the records,
and insert records 24, then 20, then 18, then 17 to "fill in the cracks"; or
for nearly unlimited flexibility, use a Double, which will let you insert
trillions of records between 1.0 and 2.0.

 

[Jwil]

Tables *have no order*. They should be viewed as an unordered bucket of

records.

If you want to present the tasks in some particular order, then you must - no
option! - include a field in the table to specify that order, and use a query
sorting by that field to present the records to the user. There simply is no
"record number" concept in the table.

If you want flexibility in specifying a sort order, you can use either a Long
Integer ID field with big gaps - start with 16, 32, 48, 64, 80 as the records,
and insert records 24, then 20, then 18, then 17 to "fill in the cracks"; or
for nearly unlimited flexibility, use a Double, which will let you insert
trillions of records between 1.0 and 2.0.

John,

I'm not using an autonumber but I am using a field to number the tasks.
these are the numbers I want to re-order. There isn't code to just take a 5
and make it a 6 and a 6 and make it a 7 and so on...? I'm not talking about
records but the ID field I created.

 

[John W. Vinson]

I'm not using an autonumber but I am using a field to number the tasks.
these are the numbers I want to re-order. There isn't code to just take a 5
and make it a 6 and a 6 and make it a 7 and so on...? I'm not talking about
records but the ID field I created.

It can be done but it would be inefficient and pretty complicated. I certainly
would not do it on the primary key of a table if it's involved in any
relationships!

I'm not up to writing an update query to do this off the top of my head; I'll
think about it and post back. If anyone has code in their pocket to do so,
please jump in!

 

[Jwil]

It can be done but it would be inefficient and pretty complicated. I certainly
would not do it on the primary key of a table if it's involved in any
relationships!

I'm not up to writing an update query to do this off the top of my head; I'll
think about it and post back. If anyone has code in their pocket to do so,
please jump in!

John,

Thank you. Should I go about this a different way?
I can also copy all the records below the "insert" (not my ID field just the
tasks field). I can then paste them one record down and then delete the
repeated task. Then I have a "blank" space for someone to input a new task.
The ID field stayed the same so everything is in the right order. Could that
maybe be automated? The table that has the tasks aren't in any
relationships. Their only purpose is to house the tasks so that I can append
them to another table on the click of a button.

 

[John W. Vinson]

Thank you. Should I go about this a different way?
I can also copy all the records below the "insert" (not my ID field just the
tasks field). I can then paste them one record down and then delete the
repeated task. Then I have a "blank" space for someone to input a new task.
The ID field stayed the same so everything is in the right order. Could that
maybe be automated? The table that has the tasks aren't in any
relationships. Their only purpose is to house the tasks so that I can append
them to another table on the click of a button.

If there is a specific desired order to the tasks - and ESPECIALLY if that
order varies from user to user - then you must have a table with fields for
the TaskID and a number field which can be used to specify the desired order.
You should emphatically NOT count on putting records into a table defining
that order. It *might*, just often enough to fool you into thinking that it
will work - but it won't, not reliably.

 

[Jwil]

If there is a specific desired order to the tasks - and ESPECIALLY if that
order varies from user to user - then you must have a table with fields for
the TaskID and a number field which can be used to specify the desired order.
You should emphatically NOT count on putting records into a table defining
that order. It *might*, just often enough to fool you into thinking that it
will work - but it won't, not reliably.

Ok. Is there a completely different way I can go about this? I'm not at
all married to any of my ideas.

Thank you

 

[De Jager]

Hi,

I need help renumbering my records. I'm new to Access so I'll explain
what
I'm doing and if there is a better way please let me know.

From a form the user can either choose to create a full set of tasks or a
reduced set of tasks. These tasks show up on another form called
"traveler".
I made two tables. One table has the full tasks (80 records) and the other
has the reduced tasks (30 records) But they aren't really records per se
they're more like 5 word instructions that tell someone what to do.
Anyway,
when they click the full button I run an append query that appends the
full
tasks table to another table that is the subform of the traveler form. and
the same if they hit the reduced button. I also add the current record ID
which ties those tasks to the record they were creating. These tasks
however
aren't set in stone but just a starting point. The user needs to be able
to
add and delete the tasks and they need to stay in a specific order.

How could I add a task (record) in the middle of the table? I'm not using
an autonumber for the full or reduced tasks table. And the tasks aren't
unique but repeated several times. So I was thinking that I could maybe
pop
up a texbox that lets them input what they want to add and then it would
ask
what record number and then I could just renumber everything after that
number and then sort. To the user it would look like they inserted a
task.

Thank you very much

 

[joelgeraldine]

jkjkjkj