On Thu, 22 Apr 2010 14:08:03 -0700, April Klein <April
Hi -
I would like to create my own auto number. It should include:
project acronym+date+####
Example: 65947WHB-CAE_042210-0001
I have a table set up that I can pull the project acronym from, the date
would be the date entered into the database, the number would generate
automatically. What is the best way to do this?
Thanks for your help.
April
This is a Very Bad Idea. It's called an "Intelligent Key" in the jargon, and
that's unfortunately not a compliment!
A field should be "atomic", containing one and only one indivisible piece of
information. A project acronym is one atomic piece of information; a date is a
different piece; a sequential number is yet a third. These should be stored in
three separate fields in your table; they can be concatenated together for
display purposes if you wish.
An Access Autonumber will not serve your turn - it's a meaningless unique
number, not even guaranteed to be sequential.
I'd suggest having three fields: a Text ProjectID field, a Date/Time DateAdded
field, and a Number... Long Integer SeqNo field in your table. Use a Form to
add data to the table. On the form you would put a combo box named
cboProjectID based on the table of project acronyms bound to the ProjectID;
set the Default Value of the DateAdded field to =Date(); and put the following
VBA code in the AfterUpdate event of the combo box:
Private Sub cboProjectID_AfterUpdate()
If Not IsNull(Me!cboProjectID) Then
Me!SeqNo = NZ(DMax("[SeqNo]", "[yourtablename]", _
"[ProjectID] = '" & Me!cboProjectID & "'")) + 1
End If
End Sub
