Hello all,

Im just wondering if someone can help me with a quick question.

At the moment in a table i am using, there is an autonumber field to display
the records "reference number" however due to the nature of the data on this
table, the records quite often get deleted by the using imputting the data,
halfway though creating the record, thus big gaps in the "reference
numbers"/autonumbers.

Im just wondering if there is a better way to make a field which
automaticaly creates a unique "reference number" but without leaving a gap in
the sequence if a record is cancelled before fully completed.

A further thought, it may help that the only way a record can be
cancelled/deleted is if it is not fully completed. so for example no one can
delete a record from 6 months ago (removed all ways of the user deleting a
record) , they can only cancel one they are creating.

Thanks in advance

In Access, the Autonmber datatype is usually used as a surrogate primary
key. That being the case, it's main value is to the database - not to the
user. Therefore, it should not be displayed to the user - at all. My rule is
that if the user can't do anything with it - don't show it to them.

Since there's nothing you can do about the gaps forming in the Autonumber
datatype, use it only as a unique numbering system for the database. To show
a sequential numbering scheme to the user, create your own.

Assuming a numeric numbering scheme, to find out the next number in
sequence, you can use either of the following:

SELECT Max(Nz(myNumber, 0)) + 1 AS NextNum FROM myTable
....or...
Nz(DMax("myNumber", "myTable"), 0) + 1

But even then, you can run into concurency issues; this is when two or more
users try to create records with the same number at the same time.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


"GiBB" <(E-Mail Removed)> wrote in message
news:809BD47B-2100-43F4-86DA-(E-Mail Removed)...
> Hello all,
>
> Im just wondering if someone can help me with a quick question.
>
> At the moment in a table i am using, there is an autonumber field to
> display
> the records "reference number" however due to the nature of the data on
> this
> table, the records quite often get deleted by the using imputting the
> data,
> halfway though creating the record, thus big gaps in the "reference
> numbers"/autonumbers.
>
> Im just wondering if there is a better way to make a field which
> automaticaly creates a unique "reference number" but without leaving a gap
> in
> the sequence if a record is cancelled before fully completed.
>
> A further thought, it may help that the only way a record can be
> cancelled/deleted is if it is not fully completed. so for example no one
> can
> delete a record from 6 months ago (removed all ways of the user deleting a
> record) , they can only cancel one they are creating.
>
> Thanks in advance

Thats a fair point and thanks for your advice.

due to the ammount of users trying to use this database, i think it will be
best to leave the reference numbers as a standard auto number, but i'll try
and drill it into the users not to cancel records.

thanks again

"Graham R Seach" wrote:

> In Access, the Autonmber datatype is usually used as a surrogate primary
> key. That being the case, it's main value is to the database - not to the
> user. Therefore, it should not be displayed to the user - at all. My rule is
> that if the user can't do anything with it - don't show it to them.
>
> Since there's nothing you can do about the gaps forming in the Autonumber
> datatype, use it only as a unique numbering system for the database. To show
> a sequential numbering scheme to the user, create your own.
>
> Assuming a numeric numbering scheme, to find out the next number in
> sequence, you can use either of the following:
>
> SELECT Max(Nz(myNumber, 0)) + 1 AS NextNum FROM myTable
> ...or...
> Nz(DMax("myNumber", "myTable"), 0) + 1
>
> But even then, you can run into concurency issues; this is when two or more
> users try to create records with the same number at the same time.
>
> Regards,
> Graham R Seach
> Microsoft Access MVP
> Sydney, Australia
>
>
> "GiBB" <(E-Mail Removed)> wrote in message
> news:809BD47B-2100-43F4-86DA-(E-Mail Removed)...
> > Hello all,
> >
> > Im just wondering if someone can help me with a quick question.
> >
> > At the moment in a table i am using, there is an autonumber field to
> > display
> > the records "reference number" however due to the nature of the data on
> > this
> > table, the records quite often get deleted by the using imputting the
> > data,
> > halfway though creating the record, thus big gaps in the "reference
> > numbers"/autonumbers.
> >
> > Im just wondering if there is a better way to make a field which
> > automaticaly creates a unique "reference number" but without leaving a gap
> > in
> > the sequence if a record is cancelled before fully completed.
> >
> > A further thought, it may help that the only way a record can be
> > cancelled/deleted is if it is not fully completed. so for example no one
> > can
> > delete a record from 6 months ago (removed all ways of the user deleting a
> > record) , they can only cancel one they are creating.
> >
> > Thanks in advance
>

To emphasise what Graham posted - the users should not see an autonumber
field. In my experience "trying to drill it into users" is doomed to
inglorious and predictable failure.


"GiBB" <(E-Mail Removed)> wrote in message
news:F5C624BD-E9D5-4036-B97D-(E-Mail Removed)...
> Thats a fair point and thanks for your advice.
>
> due to the ammount of users trying to use this database, i think it will
> be
> best to leave the reference numbers as a standard auto number, but i'll
> try
> and drill it into the users not to cancel records.
>
> thanks again
>
> "Graham R Seach" wrote:
>
>> In Access, the Autonmber datatype is usually used as a surrogate primary
>> key. That being the case, it's main value is to the database - not to the
>> user. Therefore, it should not be displayed to the user - at all. My rule
>> is
>> that if the user can't do anything with it - don't show it to them.
>>
>> Since there's nothing you can do about the gaps forming in the Autonumber
>> datatype, use it only as a unique numbering system for the database. To
>> show
>> a sequential numbering scheme to the user, create your own.
>>
>> Assuming a numeric numbering scheme, to find out the next number in
>> sequence, you can use either of the following:
>>
>> SELECT Max(Nz(myNumber, 0)) + 1 AS NextNum FROM myTable
>> ...or...
>> Nz(DMax("myNumber", "myTable"), 0) + 1
>>
>> But even then, you can run into concurency issues; this is when two or
>> more
>> users try to create records with the same number at the same time.
>>
>> Regards,
>> Graham R Seach
>> Microsoft Access MVP
>> Sydney, Australia
>>
>>
>> "GiBB" <(E-Mail Removed)> wrote in message
>> news:809BD47B-2100-43F4-86DA-(E-Mail Removed)...
>> > Hello all,
>> >
>> > Im just wondering if someone can help me with a quick question.
>> >
>> > At the moment in a table i am using, there is an autonumber field to
>> > display
>> > the records "reference number" however due to the nature of the data on
>> > this
>> > table, the records quite often get deleted by the using imputting the
>> > data,
>> > halfway though creating the record, thus big gaps in the "reference
>> > numbers"/autonumbers.
>> >
>> > Im just wondering if there is a better way to make a field which
>> > automaticaly creates a unique "reference number" but without leaving a
>> > gap
>> > in
>> > the sequence if a record is cancelled before fully completed.
>> >
>> > A further thought, it may help that the only way a record can be
>> > cancelled/deleted is if it is not fully completed. so for example no
>> > one
>> > can
>> > delete a record from 6 months ago (removed all ways of the user
>> > deleting a
>> > record) , they can only cancel one they are creating.
>> >
>> > Thanks in advance
>>

Virtually every table does require a unique identifier of some kind
(preferably a number) so you can't renumber rows once records are deleted.

The only real solution is to prevent the form from allowing deletions :-)


"GiBB" wrote:

> Thats a fair point and thanks for your advice.
>
> due to the ammount of users trying to use this database, i think it will be
> best to leave the reference numbers as a standard auto number, but i'll try
> and drill it into the users not to cancel records.
>
> thanks again
>
> "Graham R Seach" wrote:
>
> > In Access, the Autonmber datatype is usually used as a surrogate primary
> > key. That being the case, it's main value is to the database - not to the
> > user. Therefore, it should not be displayed to the user - at all. My rule is
> > that if the user can't do anything with it - don't show it to them.
> >
> > Since there's nothing you can do about the gaps forming in the Autonumber
> > datatype, use it only as a unique numbering system for the database. To show
> > a sequential numbering scheme to the user, create your own.
> >
> > Assuming a numeric numbering scheme, to find out the next number in
> > sequence, you can use either of the following:
> >
> > SELECT Max(Nz(myNumber, 0)) + 1 AS NextNum FROM myTable
> > ...or...
> > Nz(DMax("myNumber", "myTable"), 0) + 1
> >
> > But even then, you can run into concurency issues; this is when two or more
> > users try to create records with the same number at the same time.
> >
> > Regards,
> > Graham R Seach
> > Microsoft Access MVP
> > Sydney, Australia
> >
> >
> > "GiBB" <(E-Mail Removed)> wrote in message
> > news:809BD47B-2100-43F4-86DA-(E-Mail Removed)...
> > > Hello all,
> > >
> > > Im just wondering if someone can help me with a quick question.
> > >
> > > At the moment in a table i am using, there is an autonumber field to
> > > display
> > > the records "reference number" however due to the nature of the data on
> > > this
> > > table, the records quite often get deleted by the using imputting the
> > > data,
> > > halfway though creating the record, thus big gaps in the "reference
> > > numbers"/autonumbers.
> > >
> > > Im just wondering if there is a better way to make a field which
> > > automaticaly creates a unique "reference number" but without leaving a gap
> > > in
> > > the sequence if a record is cancelled before fully completed.
> > >
> > > A further thought, it may help that the only way a record can be
> > > cancelled/deleted is if it is not fully completed. so for example no one
> > > can
> > > delete a record from 6 months ago (removed all ways of the user deleting a
> > > record) , they can only cancel one they are creating.
> > >
> > > Thanks in advance
> >