Auto numbering an issue list

[Guest]

I'm managing an issue list in Access. One table, one form, several reports.
Each issue needs to have a unique number, preferably in the same sequence as
when added to the database. Users will need to see and refer to the issue
numbers in the form and the reports.

I want to have the form default to a new, never-before-used number whenever
a new issue (record) is added. Currently I'm using the AutoNumber data type,
but -- as is well-noted in the discussion groups -- AutoNumber has many
drawbacks. Is there another way to auto-fill the issue number field with a
unique number (in sequence) without using the AutoNumber feature?

I've browsed through the discussion groups and haven't found anything that
addresses this specific issue (or maybe I'm just not getting some of the
answers). All suggestions are welcome, but would prefer something toward the
novice side.

Thanks!

 

[tina]

you could add code to the form's BeforeUpdate event, as

If Me.NewRecord Then
Me!UniqueNumberFieldName = DMax("UniqueNumberFieldName",
"MyTableName") + 1
End If

the "x = x" expression should all be on one line.
if you have multiple users entering new records at the same time, you could
conceivably have one user's data entry trigger the BeforeUpdate event and
pull the highest number in the table at the same time as another user,
resulting in the same "new" highest number being calculated. if you set the
field's Index property in the table to No Duplicates, a error message will
(hopefully) be triggered for only one of the records, which you can trap and
handle.

hth

 

[Guest]

Tina,

Thanks for the suggestion. I tried something similar, based on an idea I
read in another post. I used the DMax expression in the Default Value
property on the unique number field in the form (attempts to use it in the
table resulted in an error message). That seemed to do the trick.

Brian

 

[tina]

one thing to keep in mind is that a default value is generated in the field
*as soon as the user begins entering data in any field in the current
record.* with multiple users, there is a much greater chance that one user
will begin a new record, and another user will then begin a new record
*before the first user has saved their new record.* in other words, a much
greater possibility of two concurrent users generating the same "new"
highest number. using the form's BeforeUpdate event to generate the number
will not eliminate that possibility altogether, but will probably reduce it
considerably.

hth