Increase number in table by 1

[PPCO]

Is it possible to add a record into the middle of a table and have a column
with numbers that automatically increase by one after the new entry.
For example:

Test1 1
Test2 2
Test5 3
Test3 3 increase by 1
Test4 4 increase by 1

Thanks!

 

[Jerry Whittle]

Technically no for a few reasons.

1. There isn't a 'middle' of a table. If you add records in a certain order
in a table, there's now guarentee that the next time that you open a table
that the records will be in the same order.

2. Access doesn't have table triggers or any other way to increment numbers
at table level. This includes autonumbers which can't be depended upon to
increment.

However all is not lost. You can sort records from a table using a query,
form, or report.

Also if you are always using a form to imput new records, you can use
something like DMax() + 1 to increment the largest current value by one and
add it to a new record using some VBA code behind the form.

 

[John W. Vinson]

Is it possible to add a record into the middle of a table and have a column
with numbers that automatically increase by one after the new entry.
For example:

Test1 1
Test2 2
Test5 3
Test3 3 increase by 1
Test4 4 increase by 1

Thanks!

Tables don't have a "middle". A Table should be viewed as an unsorted "heap"
of data. If your design depends on the order of records in a table, you're
using spreadsheet logic, not relational thinking!

Your example is quite unclear. Could you explain the structure of your table,
how you're entering data (it should be with a Form, not into a table
directly), and what you want to happen?

 

[PPCO]

What this is for is a delivery route with the deliveries listed in the order
they'll be serviced. What I was hoping was when we start delivering to a new
customer, we could insert that customer in the order they should appear on
the route. I was thinking of ordering the customer sequence with a number.
Perhaps there's a much better way though...
Thanks for all your insight!

 

[John W. Vinson]

What this is for is a delivery route with the deliveries listed in the order
they'll be serviced. What I was hoping was when we start delivering to a new
customer, we could insert that customer in the order they should appear on
the route. I was thinking of ordering the customer sequence with a number.
Perhaps there's a much better way though...
Thanks for all your insight!

You'll need to include the customer's position in the delivery sequence in a
Number field in the customer table (or the route table, or whatever table is
appropriate, I can't see your tables from here!)

This should be an editable field, probably displayed in a continuous form. You
could start with numbers like 10, 20, 30, 40 so you could insert a new
customer at 25, and then the next one at 23; or you could (with a bit more
programming) put some up/down buttons on the subform to "move" the customer up
or down in the delivery order by editing the field value.

But inserting the customer into a particular place in a table will
emphatically NOT work and is the wrong approach!

 

[PPCO]

Thanks for the info! I had actually thought of trying the number sequence
with gaps, but that seems like it has limits as well. How do you do the
up/down arrow in a continuous form? That might work...I've never used a
continuous form: will that guarantee that the records stay in order each time
I open the form to print?

 

[John W. Vinson]

Thanks for the info! I had actually thought of trying the number sequence
with gaps, but that seems like it has limits as well. How do you do the
up/down arrow in a continuous form? That might work...I've never used a
continuous form: will that guarantee that the records stay in order each time
I open the form to print?

There are a LOT of misconceptions in this post... I'd really suggest you read
up on some of the resources posted below.

The up/down arrow will require a Number field in the table to define a sort
order, and some (nontrivial but not very difficult) VBA code and an Update
query to implement it. It's not something that a Microsoft wizard will do for
you.

A continuous form is just a tool, a window to display data in a Query. The
order of records is defined in the Query, and you can control it however you
like.

Forms (and tables, and queries) are not designed for printing. Reports are the
appropriate tool for printing; and a Report has a "Sorting and Grouping"
feature which lets you specify the sort order, based on data in the table, but
independently of any form that might be accessing the same data.

Take a look here, especially the tutorials: you may find that there are a lot
of features of Access that will make your work easier!

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials