Automatic incremental of records

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

njogus - 17 Jul 2007 07:40 GMT

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?

Reply to this Message

scubadiver - 17 Jul 2007 08:14 GMT

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

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

Reply to this Message

njogus - 17 Jul 2007 10:52 GMT

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?

> As far as I am aware, the answer is no. Anyway, why would you want to?
>
[quoted text clipped - 3 lines]
> > number 4 although it is actually the third record. Is there a way it can be
> > done automatically?

Reply to this Message

scubadiver - 17 Jul 2007 11:24 GMT

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.
[quoted text clipped - 8 lines]
> > > number 4 although it is actually the third record. Is there a way it can be
> > > done automatically?

Reply to this Message

njogus - 19 Jul 2007 07:34 GMT

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
[quoted text clipped - 33 lines]
> > > > number 4 although it is actually the third record. Is there a way it can be
> > > > done automatically?

Reply to this Message

Joseph Meehan - 17 Jul 2007 12:51 GMT

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

Signature

Joseph Meehan

Dia 's Muire duit

Reply to this Message