Assign a custom auto increment number

[niktsol1978]

Hello everyone. Here is the scenario of my project:

Each office, where i work, has its own incoming documents, in which a unique number per year, should be assigned , once document info is entered in the database. This unique number will be increased by one for the new incoming document and every new year's eve is reset back to 1. Each office has its unique "autonumbering". The database i am creating will be in multi-user environment. So i thought 3 ways to assign a unique reference number per office.

1) Using DMax function, in which i will search for the maximum reference number at that time in the document's table, +1. But, i've searched that Dmax, might have some issues in multiuser environment (assign two same numbers if users work simultaneously the same process)
2) Making a index with office ID , reference number, and current year (non duplicates) so by using Dmax, if the above problem occurs i will trap the error and loop again the assign new reference number procedure. But, what happens, when multiple users run the same process? Is it possible a deadlock or something?


3) Create a counter table with fields such as <OfficeID>,<ReferenceNumber>,<CurrentYear>. When a new document is entered as incoming document in <OfficeID>, then with a DAO Recordset i could retrieve the <ReferenceNumber> value, at the <CurrentYear>, with a Recordset Pessimistic lock, in case another user does the same process. If an error occurs, then there will be a loop in the whole procedure. I also believe that using this implementation, each new year's eve, i will reset to all <OfficeID> fields the <ReferenceNumber> to 1 and also the <CurrentYear> to new year.

I believe the solution number 3 is more reliable at all. I am glad to read your opinions, as well. Please feel free to suggest any other possible solution(s).

Thank you in advance.

[ranman256]

OfficeID, DocID (autoNum) is all you need for unique.
Ref#, and EntryDate for your use.
entrydate will give you year thus no need to do any 'calculation'.
Having a counter reset to 1 every year is not needed. The timestamp does this for you with no vb needed.

[niktsol1978]

The DocID is not autonumber field. I don't know the document ids that i will receive from other services, and the format of this Docid varies.

How should i manage with timestap to reset the counter? You mean something like validation rule or something?

Thank you in advance

[twgonder]

Did you find a solution? I'm not sure I understand your reference id. Is it the incrementing number or a number already assigned to a document?
The time honored simple solution in the past was id = office:date:milliseconds and test for clash. The foreign document id is just another field in the table. I'm not sure Access can do this schema with queries that can create a new record.