G
Guest
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
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