INCREMENTAL NBR VS. AUTONUMBER WHEN APPENDING

T

Tank

When appending batches of records into a master table, I want ID numbers
automatically created to start with the next available record number after
the highest ID number in the Master Table that is receiving the appending
records. I prefer an incremental numbering system rather than the Autonumber
system.

Is there code to create an incremental number system directly into the
Master Table or into the appending query (not into a data entry form) when I
append batches of records.

I am familiar with expressions to create incremental ID numbers used in data
entry forms such as an Event Procedure (On Current) or a default value in the
ID field control on the form itself.

I frequently append batches of records into a table having a key field ID
autonumber. I leave off the Key ID number, of course, from the appending
batch, so that, when appended into the master table, the autonumber of the
master table triggers consecutive new ID numbers for these newly appended
batched records.

Often, however, when appending archived records from an “Archive Tableâ€
(old, inactive records that might be 10 years without activity but now
re-activated using their original record ID number) into the master table, I
include in the appending process the original key ID number and append all
fields of the record including the key ID field. The appending process works
smoothly without any glitch.

However, frequently, the Autonumber of the master table will “fall backwardâ€
when I need to create a new record in the master table, giving me not the
highest next number, but rather some number that perhaps follows the last
number of the archive records I earlier appended successfully into the master
table. Eventually, of course, when creating a new record, warning messages
will advise me that I can’t save the newly created record (without
explanation) which I believe is the result of a conflict with the Autonumber
trying to use an existing number value in the master table.

Workaround solutions can be accomplished by visually checking the master
table for the last highest number, then manually numbering the batched
records with the next number above the master table’s Autonumber ID and then
proceed with the appending.

However, I would prefer to use an incremental numbering system for both (a)
the data entry forms and also for (b) appending batches directly into the
master table. Is there a procedure or expression I can use in the table
itself or the appending query similar to the examples that follow below?

-----------------------------------------------------------------------------------------
For reference, my two options in the data entry form are either (1) or (2).


(1) Event Procedure (On Current):

If Me.NewRecord Then
Me.PersonID = DMax("[PersonID]", "tblPersons") + 1
End If


(2) Default value in PersonID control on data entry form:

=Dmax(“[PersonID]â€,â€tblPersonsâ€)+1
 
M

Marshall Barton

Tank said:
When appending batches of records into a master table, I want ID numbers
automatically created to start with the next available record number after
the highest ID number in the Master Table that is receiving the appending
records. I prefer an incremental numbering system rather than the Autonumber
system.

Is there code to create an incremental number system directly into the
Master Table or into the appending query (not into a data entry form) when I
append batches of records.

I am familiar with expressions to create incremental ID numbers used in data
entry forms such as an Event Procedure (On Current) or a default value in the
ID field control on the form itself.

I frequently append batches of records into a table having a key field ID
autonumber. I leave off the Key ID number, of course, from the appending
batch, so that, when appended into the master table, the autonumber of the
master table triggers consecutive new ID numbers for these newly appended
batched records.

Often, however, when appending archived records from an “Archive Table”
(old, inactive records that might be 10 years without activity but now
re-activated using their original record ID number) into the master table, I
include in the appending process the original key ID number and append all
fields of the record including the key ID field. The appending process works
smoothly without any glitch.

However, frequently, the Autonumber of the master table will “fall backward”
when I need to create a new record in the master table, giving me not the
highest next number, but rather some number that perhaps follows the last
number of the archive records I earlier appended successfully into the master
table. Eventually, of course, when creating a new record, warning messages
will advise me that I can’t save the newly created record (without
explanation) which I believe is the result of a conflict with the Autonumber
trying to use an existing number value in the master table.

Workaround solutions can be accomplished by visually checking the master
table for the last highest number, then manually numbering the batched
records with the next number above the master table’s Autonumber ID and then
proceed with the appending.

However, I would prefer to use an incremental numbering system for both (a)
the data entry forms and also for (b) appending batches directly into the
master table. Is there a procedure or expression I can use in the table
itself or the appending query similar to the examples that follow below?

-----------------------------------------------------------------------------------------
For reference, my two options in the data entry form are either (1) or (2).


(1) Event Procedure (On Current):

If Me.NewRecord Then
Me.PersonID = DMax("[PersonID]", "tblPersons") + 1
End If


(2) Default value in PersonID control on data entry form:

=Dmax(“[PersonID]”,”tblPersons”)+1

-----------------------------------------------------------------------------------------


I think you are barking up the wrong tree. A surrogate
primary key has no purpose othen than being a unique
identifier. If you feel that you absolutely must have a
(semi) sequential number, it should not be the primary key
and an additional mechanism needs be used to assign it.
 
T

Tank

Thanks for your thoughts and I appreciate your interest. I look forward to
the issues I raised being addressed perhaps by persons who may have similar
experiences.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top