Creating a 'Counter'

[Guest]

Scenario:
The database is part of a replicated set. The autonumber field is very long
and can be a negative number. All orders/estimates are identified by a
unique number. Sequential numbers are the current and preferred option.

Question:
How can I create a counter that will generate a new number each time either
a new record is created or saved.

There is not an issue of different replicated sets creating duplicate
numbers as each set is in a different departments and each require their own
counters.

 

[John Vinson]

Question:
How can I create a counter that will generate a new number each time either
a new record is created or saved.

Force all data entry to be done using a Form; and use the form's
BeforeInsert event (for created) or BeforeUpdate event (saved) to
create this number. For instance,

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSeqNo = NZ(DMax("[SeqNo]", "[Tablename]", "[DeptID] = " _
& Me!DeptID)) + 1
DoCmd.RunCommand acCmdSaveRecord
End Sub

to look up the largest SeqNo for this department, add one (using NZ to
return zero if this is the very first SeqNo for this department), and
then immediately save the record to prevent another user from
inadvertantly assigning the same number.

John W. Vinson[MVP]

 

[Rick Brandt]

Scenario:
The database is part of a replicated set. The autonumber field is
very long and can be a negative number. All orders/estimates are
identified by a unique number. Sequential numbers are the current
and preferred option.

Question:
How can I create a counter that will generate a new number each time
either a new record is created or saved.

There is not an issue of different replicated sets creating duplicate
numbers as each set is in a different departments and each require
their own counters.

DMax() + 1 used in the BeforeUpdate event of the form will work with moderate
concurrency. For higher concurrency you use a seed table. This table has a
single row twith a single field containing the next number to assign. When a
new record is saved you run code that creates an editable RecordSet against the
seed table and imposes a lock on it. You read the value from the RecordSet,
assign it to the record, increment the value in the seed table and then release
the lock.