Automatic incremental of records

N

njogus

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?
 
N

njogus

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?
 
S

scubadiver

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

njogus

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?

scubadiver said:
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.

njogus said:
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?
Click to expand...
Click to expand...
 
Top