Automatic incremental of records

[Guest]

I have a table with several records and arranged serially, i.e 1,2,3 .....25.
When I delete arecod like record number 3 I would like record number 4 to
become record number 3. But in my case Record number 4 still remains serial
number 4 although it is actually the third record. Is there a way it can be
done automatically?

 

[Guest]

As far as I am aware, the answer is no. Anyway, why would you want to?

 

[Guest]

I want to maintain seniority of the people in those records even after I
delete a person the person should replace ther deleted one in that order.
Those serial numbers, how should I assign the people numbers 1,2,3....50 and
the numbers to update themselves automatically?

 

[Guest]

I made the assumption you were asking about a primary key. If you are, then
I wouldn't consider it to be wise to use the primary key for information on
seniority of people especially if you have foreign keys. Then there are data
integrity problems.

Having said "no" there is a solution.

What I have done is create a form with an unbound text box and a command
button that will run two queries

Delete query:

In the criteria write a reference to the text box
[forms]![form name]![field name]

Update query:

In the "update" row write [form name] - 1
In the criteria row write: >[forms]![form name]![field name]

this works.

I will add you may (or should) be able to run these two queries using the
"on delete" event in the form. Then use me.refresh or me.requery.

 

[Joseph Meehan]

I have a table with several records and arranged serially, i.e 1,2,3
.....25. When I delete arecod like record number 3 I would like
record number 4 to become record number 3. But in my case Record
number 4 still remains serial number 4 although it is actually the
third record. Is there a way it can be done automatically?

Tables do not have a sequencial order. It is more of a bucket of data.
You need to provide a method of sorting. Since it appears you want
seniority order, then all you need is a field listing order or date/time of
seniority and sort on that field when you display the data in a query, form
or report.

 

[Guest]

Thanks, I like the idea you've given me. As a novice I've created the Delete
and Update queries. The problem though is about the form; will the form
include fields from a certain table or query? And how can one Command Button
run two queries? How do I make it possible.
What is this 'on delete' event how is it done?

I made the assumption you were asking about a primary key. If you are, then
I wouldn't consider it to be wise to use the primary key for information on
seniority of people especially if you have foreign keys. Then there are data
integrity problems.

Having said "no" there is a solution.

What I have done is create a form with an unbound text box and a command
button that will run two queries

Delete query:

In the criteria write a reference to the text box
[forms]![form name]![field name]

Update query:

In the "update" row write [form name] - 1
In the criteria row write: >[forms]![form name]![field name]

this works.

I will add you may (or should) be able to run these two queries using the
"on delete" event in the form. Then use me.refresh or me.requery.

I want to maintain seniority of the people in those records even after I
delete a person the person should replace ther deleted one in that order.
Those serial numbers, how should I assign the people numbers 1,2,3....50 and
the numbers to update themselves automatically?