Unique Numbering field in Access Form

[emansplz]

I have created a table and form in Access XP to track the disclosuer of patient health information for the new HIPAA compliance regulations, how do I make the auto number option to generate a number that consiste of the date pulled from the system date and a 4 diget number

ex. 0507020001

If you could please help me.

Earl P
ephillips@lrho.org

 

[JeremyNYC]

Earl,

You can't do that with an autonumber fields. Autonumbers will be unique, but they will not be significant--they will not be sequential, no matter what you do. There will be gaps.

If you need a field with data like you describe, you'll have to write a function to generate such numbers.

There are many examples of code for this type of operation. There's one in the Access Developer's Handbook, which you should go buy if you don't have it. Best 50 bux an access developer could spend.

The basic notion is to have a table with a seed value--the next value to be used. You can then append that to some form of the date and shove it in your new record. There are some locking issues to be dealt with in a multi-user application, so it's worth it to check out code written by some of the big dogs.

MichaelRed has a faq on tek-tips that is another version of this. I haven't used his code, but (even though he and I strongly disagree about how useful Access's autonumber fields are), he knows what he's doing, so my guess is his code is pretty dang solid.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.