Sequential Numbering in Access based on another field

[GMP1978]

I am trying to use the expression building on a command button to build a sequential button that looks at the document id field and when you click the sequential number command button it populated the sequential field with what version of the document this is (based on what is in the database already).

Example: We have 3 records in the database so far AR672, AR535, and AR771. Each of these show 01 for sequence number as they are the first of that document number to be used. If I update AR672 with a new version and record that in the database I enter AR672 in the document field and hit sequential number command button.. I would need it to populate 02 as 01 has already been used.

Any help is greatly appreciated.

 

[mdlueck]

Sounds like you would need a query along the lines of:

SELECT TOP 1 [seqnumber]
WHERE [docnumber] = 'AR672'
ORDER BY [seqnumber] DESC;

And increment the vernumber value by 1 to obtain the new version number.

This does not guarantee against two different workstations posting an update at exactly the same time.

 

[GMP1978]

I don't really understand this. I actually created this database 2 years ago and had it working but today an employee opened it up and it was corrupted so I am rebuilding. I had it set up so the Command Button could be clicked and it populated the next sequential number in the form based on the document number and what the last version was. I used VBA, but haven't used it since.

 

[GMP1978]

I did something like Me!Sequence Number = (DCount("[Document Number]"=
I cant remember the rest