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  

On Tue, 9 Mar 2010 14:14:02 -0800, Jwil <Jwil@discussions.microsoft.com>
wrote:

>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.

-- 

             John W. Vinson [MVP]

> 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.

On Tue, 9 Mar 2010 16:56:01 -0800, Jwil <Jwil@discussions.microsoft.com>
wrote:

>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!
-- 

             John W. Vinson [MVP]

"John W. Vinson" wrote:

> On Tue, 9 Mar 2010 16:56:01 -0800, Jwil <Jwil@discussions.microsoft.com>
> wrote:
> 
> >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!
> -- 
> 
>              John W. Vinson [MVP]
> .
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.

On Tue, 9 Mar 2010 18:50:01 -0800, Jwil <Jwil@discussions.microsoft.com>
wrote:


>
>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. 
-- 

             John W. Vinson [MVP]

"John W. Vinson" wrote:

> On Tue, 9 Mar 2010 18:50:01 -0800, Jwil <Jwil@discussions.microsoft.com>
> wrote:
> 

> 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. 
> -- 
> 
>              John W. Vinson [MVP]
> .
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

"Jwil" <Jwil@discussions.microsoft.com> wrote in message 
news:9698BCB7-D8FE-43E8-AC1F-B3C5B3499075@microsoft.com...
> 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 

jkjkjkj

"Jwil" <Jwil@discussions.microsoft.com> a écrit dans le message de groupe de 
discussion : 9698BCB7-D8FE-43E8-AC1F-B3C5B3499075@microsoft.com...
> 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