Using an autonumber as a Record ID

[Guest]

Currently I am using the autonumber feature in access to generate a unique
record ID everytime a new record is added to the database. I would also like
to be able to search records based on this number. However I notice that as I
delete records it leaves gaps, which as I understand will not be re-used in
order to prevent child records from accidently becoming associated with
incorrect parent records. I'm just wondering if there is a better way to
generate record ID's? Also I'm new to access and would like to know if there
could be any future issues arrising from my using the autonumber feature for
the Record ID?

Thanks!

 

[Mark A. Sam]

Hello,

I have been using Access since it came on the market in the early 90's. I
use Autonumbers for unique record ID's and think it is the best way to go.
If you are talking about assigning numbers for use, like Invoice Numbers,
Just assign them in a number or text field with a No Duplicates index. Then
if you delete a number you can easily reassign it. You can also reassign an
autonumber using code, you just cannot enter it manually.

God Bless,

Mark A. Sam

 

[Guest]

My autonumber / record ID is used for only one table. So when a record from
that table is deleted along with the autonumber, I should be able to re-use
that number. In order to limit the gaps in my autonumber sequence, is there a
way to have access re-use these deleted numbers before going to a new number?

And thank you for your help.

 

[Mark A. Sam]

Justin,

Yes, but only through programming. Access itself won't do it. I can't
understand why you are deleting a record in the first place if you want to
use the the autonumber. Why not keep the record intact and clear or reassign
the field values?

God Bless,

Mark

 

[Guest]

The autonumber is used as the record ID for a tblEmployeeProjectDetails. Each
record is pulled from two other tables, tblEmployeeInfo and tblProjectInfo.
If an employee is deleted from tblEmployeeInfo (Terminated, Retires, etc.) it
cascades down to tblEmployeeProjectDetails and deletes any records related to
the "old" employee. The problem is that any single employee can be on many
different projects and when we delete the employee, it creates a gap for
every record that was associated with the employee. I would like to re-use
the "missing" numbers to refill the gaps as we create new records.

 

[Mikal via AccessMonster.com]

Justin,
You shouldn't rely on your primary key to be meaningful to humans. If you
need truly sequential numbers, you should assign them in a separate field as
Mark suggested above. If you search this forum for information about auto-
number fields, you will find a lot of examples illustrative of why this is so.
Basically, the primary key is there to relate data in one table to data in
another table. To rely on it for anything else is probably a mistake.
HTH
Mike

The autonumber is used as the record ID for a tblEmployeeProjectDetails.

I would like to re-use
the "missing" numbers to refill the gaps as we create new records.

Justin,

[quoted text clipped - 56 lines]

--
Mike Pippins

"We have met the enemy and he is us." -- Pogo Possum

Message posted via AccessMonster.com

 

[Mark A. Sam]

Justin,

Create a new form with a Command button and a textbox. Name the button
Update and the textbox NewId. Place this code in the Click event of the
button. Open the form and type a number into the textbox and click the
button. This will add a new record to the table, tblEmployeeInfo, and
assign the number to the ID field that is in the textbox.

You need the DAO object library referenced so if you don't know it is,
compile the project. If it compiles you are ok, if not then while your
module is open, click on the tools menu and select references. Look for
Microsoft DAO 3.x Object library and select it. Close the references dialog
and reopen it. Click the up arrow until it moves as far up the list as it
can. It needs to be above the Active X objects library.

Private Sub Update_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblEmployeeInfo", dbOpenDynaset)

If IsNumeric([NewID]) Then
rst.AddNew
rst![ID] = [NewID]
rst.Update
rst.Close
Set rst = Nothing

MsgBox "Record Added"

End If

End Sub

 

[Guest]

I think that gets me on the right track. Thanks to all for the helpful info.

Justin,

Create a new form with a Command button and a textbox. Name the button
Update and the textbox NewId. Place this code in the Click event of the
button. Open the form and type a number into the textbox and click the
button. This will add a new record to the table, tblEmployeeInfo, and
assign the number to the ID field that is in the textbox.

You need the DAO object library referenced so if you don't know it is,
compile the project. If it compiles you are ok, if not then while your
module is open, click on the tools menu and select references. Look for
Microsoft DAO 3.x Object library and select it. Close the references dialog
and reopen it. Click the up arrow until it moves as far up the list as it
can. It needs to be above the Active X objects library.

Private Sub Update_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblEmployeeInfo", dbOpenDynaset)

If IsNumeric([NewID]) Then
rst.AddNew
rst![ID] = [NewID]
rst.Update
rst.Close
Set rst = Nothing

MsgBox "Record Added"

End If

End Sub

The autonumber is used as the record ID for a tblEmployeeProjectDetails.
Each
record is pulled from two other tables, tblEmployeeInfo and
tblProjectInfo.
If an employee is deleted from tblEmployeeInfo (Terminated, Retires, etc.)
it
cascades down to tblEmployeeProjectDetails and deletes any records related
to
the "old" employee. The problem is that any single employee can be on many
different projects and when we delete the employee, it creates a gap for
every record that was associated with the employee. I would like to re-use
the "missing" numbers to refill the gaps as we create new records.

 

[Mark A. Sam]

Make this adjustment Justin, to make sure the recordset gets closed
everytime. I rushed to write that and placed the statements to close the
recordset in the wrong place.


Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblEmployeeInfo", dbOpenDynaset)

If IsNumeric([NewID]) Then
rst.AddNew
rst![ID] = [NewID]
rst.Update
MsgBox "Record Added"
End If

rst.Close
Set rst = Nothing

I think that gets me on the right track. Thanks to all for the helpful
info.

Justin,

Create a new form with a Command button and a textbox. Name the button
Update and the textbox NewId. Place this code in the Click event of the
button. Open the form and type a number into the textbox and click the
button. This will add a new record to the table, tblEmployeeInfo, and
assign the number to the ID field that is in the textbox.

You need the DAO object library referenced so if you don't know it is,
compile the project. If it compiles you are ok, if not then while your
module is open, click on the tools menu and select references. Look for
Microsoft DAO 3.x Object library and select it. Close the references
dialog
and reopen it. Click the up arrow until it moves as far up the list as
it
can. It needs to be above the Active X objects library.

Private Sub Update_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblEmployeeInfo", dbOpenDynaset)

If IsNumeric([NewID]) Then
rst.AddNew
rst![ID] = [NewID]
rst.Update
rst.Close
Set rst = Nothing

MsgBox "Record Added"

End If

End Sub

The autonumber is used as the record ID for a
tblEmployeeProjectDetails.
Each
record is pulled from two other tables, tblEmployeeInfo and
tblProjectInfo.
If an employee is deleted from tblEmployeeInfo (Terminated, Retires,
etc.)
it
cascades down to tblEmployeeProjectDetails and deletes any records
related
to
the "old" employee. The problem is that any single employee can be on
many
different projects and when we delete the employee, it creates a gap
for
every record that was associated with the employee. I would like to
re-use
the "missing" numbers to refill the gaps as we create new records.


:

Justin,

Yes, but only through programming. Access itself won't do it. I can't
understand why you are deleting a record in the first place if you
want
to
use the the autonumber. Why not keep the record intact and clear or
reassign
the field values?

God Bless,

Mark

My autonumber / record ID is used for only one table. So when a
record
from
that table is deleted along with the autonumber, I should be able to
re-use
that number. In order to limit the gaps in my autonumber sequence,
is
there a
way to have access re-use these deleted numbers before going to a
new
number?

And thank you for your help.

:

Hello,

I have been using Access since it came on the market in the early
90's.
I
use Autonumbers for unique record ID's and think it is the best way
to
go.
If you are talking about assigning numbers for use, like Invoice
Numbers,
Just assign them in a number or text field with a No Duplicates
index.
Then
if you delete a number you can easily reassign it. You can also
reassign
an
autonumber using code, you just cannot enter it manually.

God Bless,

Mark A. Sam



message
Currently I am using the autonumber feature in access to generate
a
unique
record ID everytime a new record is added to the database. I
would
also
like
to be able to search records based on this number. However I
notice
that
as I
delete records it leaves gaps, which as I understand will not be
re-used
in
order to prevent child records from accidently becoming
associated
with
incorrect parent records. I'm just wondering if there is a better
way
to
generate record ID's? Also I'm new to access and would like to
know
if
there
could be any future issues arrising from my using the autonumber
feature
for
the Record ID?

Thanks!

 

[MikeB]

http://www.mvps.org/access/tencommandments.htm

Look at #7... In fact review all of them. It is kind of the Readers Digest of
common sense to experienced developers approach to Access in particular and
databases in general.

 

[Guest]

Hey that's a great website! Looks like I was breaking one of the ten. I think
I will spend a little time going through that website, before I continue with
my db. Thanks again!

 

[Mark A. Sam]

7 Thou shalt not use Autonumber if the field is meant to have meaning for
thy users.

I don't agree with this one unless I am not understandnig the meaning. I
don't see anything wrong with using an autonumber for an invoice number or
order number or something similar, unless it is the client's preference to
use a special format. In fact I think it is the better way to go.

 

[Marshall Barton]

Mark, a serious problem with exposing autonumber values is
that humans seem to have a compulsion to assign meaning to
just about anything they can see. E.g. Auditors can get
really bent out of shape when there is a gap in a sequence
of invoice numbers, while others will surely think a larger
number means the item is later in time than a smaller
number. When they find that their ideas about these numbers
is not valid, they invariably launch an effort to get the
numbers changed to fit their preconceptions. Don't forget
that autonumbers can go negative, might be random and with
Replication are GUIDs.

Autonumbers are, at the heart of it, just an address so
other tables can use the value as a pointer. Any other use
is just asking for trouble.

 

[Mark A. Sam]

Marshall,

I understand what you are saying and I guess it depends on the usage and the
clients needs It hasn't been my experience that users pay attention to the
number like you described. If I have license and there is no need to
construct a number, I prefer an autonumber, just because I find it easier to
implement. Definately as a pointer, it is the way to go.

God Bless,

Mark

Mark, a serious problem with exposing autonumber values is
that humans seem to have a compulsion to assign meaning to
just about anything they can see. E.g. Auditors can get
really bent out of shape when there is a gap in a sequence
of invoice numbers, while others will surely think a larger
number means the item is later in time than a smaller
number. When they find that their ideas about these numbers
is not valid, they invariably launch an effort to get the
numbers changed to fit their preconceptions. Don't forget
that autonumbers can go negative, might be random and with
Replication are GUIDs.

Autonumbers are, at the heart of it, just an address so
other tables can use the value as a pointer. Any other use
is just asking for trouble.
--
Marsh
MVP [MS Access]

7 Thou shalt not use Autonumber if the field is meant to have meaning for
thy users.

I don't agree with this one unless I am not understandnig the meaning. I
don't see anything wrong with using an autonumber for an invoice number or
order number or something similar, unless it is the client's preference to
use a special format. In fact I think it is the better way to go.

 

[MikeB]

No one is telling you which method to employ.

It was just the consensus of developers, with scores of years experience, that
for values that are exposed to the real world, it is best to develop your own
construct, independent of AutoNumber.

Marshall,

I understand what you are saying and I guess it depends on the usage and the
clients needs It hasn't been my experience that users pay attention to the
number like you described. If I have license and there is no need to
construct a number, I prefer an autonumber, just because I find it easier to
implement. Definately as a pointer, it is the way to go.

God Bless,

Mark

Mark, a serious problem with exposing autonumber values is
that humans seem to have a compulsion to assign meaning to
just about anything they can see. E.g. Auditors can get
really bent out of shape when there is a gap in a sequence
of invoice numbers, while others will surely think a larger
number means the item is later in time than a smaller
number. When they find that their ideas about these numbers
is not valid, they invariably launch an effort to get the
numbers changed to fit their preconceptions. Don't forget
that autonumbers can go negative, might be random and with
Replication are GUIDs.

Autonumbers are, at the heart of it, just an address so
other tables can use the value as a pointer. Any other use
is just asking for trouble.
--
Marsh
MVP [MS Access]

7 Thou shalt not use Autonumber if the field is meant to have meaning for
thy users.

I don't agree with this one unless I am not understandnig the meaning. I
don't see anything wrong with using an autonumber for an invoice number or
order number or something similar, unless it is the client's preference to
use a special format. In fact I think it is the better way to go.


"MikeB" <m.byerleyATVerizonDottieNettie> wrote

http://www.mvps.org/access/tencommandments.htm

Look at #7... In fact review all of them. It is kind of the Readers
Digest of common sense to experienced developers approach to Access in
particular and databases in general.


Currently I am using the autonumber feature in access to generate a
unique
record ID everytime a new record is added to the database. I would also
like
to be able to search records based on this number. However I notice that
as I
delete records it leaves gaps, which as I understand will not be re-used
in
order to prevent child records from accidently becoming associated with
incorrect parent records. I'm just wondering if there is a better way to
generate record ID's? Also I'm new to access and would like to know if
there
could be any future issues arrising from my using the autonumber feature
for
the Record ID?

 

[Guest]

Interesting thoughts, and informative.

I have a split db, and not sure on how to handle back end updates where
autonumber keys are used. I have a tendency to do changes to the development
version, but then need to update the operational one.

Initial thoughts are to import all data into the new structure, but then all
autonumbers will be regenerated, corrupting relationships between tables.

Possible more correct is to run alter table code to update esisting
structure, but this strikes me as very laborsome.

Can you offer thoughts in this regard?

Thanks in advance.


swas

Make this adjustment Justin, to make sure the recordset gets closed
everytime. I rushed to write that and placed the statements to close the
recordset in the wrong place.


Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblEmployeeInfo", dbOpenDynaset)

If IsNumeric([NewID]) Then
rst.AddNew
rst![ID] = [NewID]
rst.Update
MsgBox "Record Added"
End If

rst.Close
Set rst = Nothing

I think that gets me on the right track. Thanks to all for the helpful
info.

Justin,

Create a new form with a Command button and a textbox. Name the button
Update and the textbox NewId. Place this code in the Click event of the
button. Open the form and type a number into the textbox and click the
button. This will add a new record to the table, tblEmployeeInfo, and
assign the number to the ID field that is in the textbox.

You need the DAO object library referenced so if you don't know it is,
compile the project. If it compiles you are ok, if not then while your
module is open, click on the tools menu and select references. Look for
Microsoft DAO 3.x Object library and select it. Close the references
dialog
and reopen it. Click the up arrow until it moves as far up the list as
it
can. It needs to be above the Active X objects library.

Private Sub Update_Click()

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("tblEmployeeInfo", dbOpenDynaset)

If IsNumeric([NewID]) Then
rst.AddNew
rst![ID] = [NewID]
rst.Update
rst.Close
Set rst = Nothing

MsgBox "Record Added"

End If

End Sub






The autonumber is used as the record ID for a
tblEmployeeProjectDetails.
Each
record is pulled from two other tables, tblEmployeeInfo and
tblProjectInfo.
If an employee is deleted from tblEmployeeInfo (Terminated, Retires,
etc.)
it
cascades down to tblEmployeeProjectDetails and deletes any records
related
to
the "old" employee. The problem is that any single employee can be on
many
different projects and when we delete the employee, it creates a gap
for
every record that was associated with the employee. I would like to
re-use
the "missing" numbers to refill the gaps as we create new records.


:

Justin,

Yes, but only through programming. Access itself won't do it. I can't
understand why you are deleting a record in the first place if you
want
to
use the the autonumber. Why not keep the record intact and clear or
reassign
the field values?

God Bless,

Mark

My autonumber / record ID is used for only one table. So when a
record
from
that table is deleted along with the autonumber, I should be able to
re-use
that number. In order to limit the gaps in my autonumber sequence,
is
there a
way to have access re-use these deleted numbers before going to a
new
number?

And thank you for your help.

:

Hello,

I have been using Access since it came on the market in the early
90's.
I
use Autonumbers for unique record ID's and think it is the best way
to
go.
If you are talking about assigning numbers for use, like Invoice
Numbers,
Just assign them in a number or text field with a No Duplicates
index.
Then
if you delete a number you can easily reassign it. You can also
reassign
an
autonumber using code, you just cannot enter it manually.

God Bless,

Mark A. Sam



message
Currently I am using the autonumber feature in access to generate
a
unique
record ID everytime a new record is added to the database. I
would
also
like
to be able to search records based on this number. However I
notice
that
as I
delete records it leaves gaps, which as I understand will not be
re-used
in
order to prevent child records from accidently becoming
associated
with
incorrect parent records. I'm just wondering if there is a better
way
to
generate record ID's? Also I'm new to access and would like to
know
if
there
could be any future issues arrising from my using the autonumber
feature
for
the Record ID?

Thanks!

 

[Rick Brandt]

Interesting thoughts, and informative.

I have a split db, and not sure on how to handle back end updates
where autonumber keys are used. I have a tendency to do changes to
the development version, but then need to update the operational one.

Initial thoughts are to import all data into the new structure, but
then all autonumbers will be regenerated, corrupting relationships
between tables.

Possible more correct is to run alter table code to update esisting
structure, but this strikes me as very laborsome.

Can you offer thoughts in this regard?

If you use append queries to copy the data to new tables the AutoNumbers are not
regenerated.

 

[Guest]

Thanks for the response Rick, and others.

So is appending all records into a new database backend structure the better
alternative to alter table commands? Or is this dependant on the number of
modifications required?

I definately need to review use of autonumber fields...

swas

 

[Rick Brandt]

Thanks for the response Rick, and others.

So is appending all records into a new database backend structure the
better alternative to alter table commands? Or is this dependant on
the number of modifications required?

The latter is the criteria I use. If the changes to the tables can be
accurately described as "tweaks", then I use code to apply those changes to the
existing back end. If there are "significant" changes then I usually distribute
a new empty back end and import all of the existing data into it.