No reason to think bad of you at all; it’s a perfectly reasonable question.
Firstly the new record should be added via a form, which is where data
should be entered anyway, never directly into the datasheet view of the
table. Secondly use two columns in the table, ClassCode of text data type,
and DrawingNumber of (long integer) number data type. Its much easier to
concatenate two values together, than to split them. You should make the two
columns the composite primary key of the table, or otherwise index them
uniquely (in combination, that is, not individually). Having two columns also
allows you easily to look up the last number for the class code in question.
This done by putting code in the AfterUpdate event procedure of the ClassCode
control on the form. The code looks up the last number in the table (called
Drawings in the example below) for that code and adds 1, using the DMax
function to get the last (highest) number. So the code goes like this:
Dim varLastNumber as Variant
Dim strCriteria As String
strCriteria = "[ClassCode] = """ & Me.[ClassCode] & """"
varLastNumber = DMax("[DrawingNumber]", "[Drawings]", strCriteria)
Me.[DrawingNumber] = Nz(varLastNumber,0)+1
The Nz function is used to return a zero if the Dmax function returns a
Null, which would be the case for the first drawing of a class code.
To format the DrawingNumber to 5 digits, with leading zeros where necessary,
set its Format property to:
00000
If you want to combine the class code and drawing number, in a report say,
you can do so by setting the ControlSource property of an unbound text box to:
=[ClassCode] & "-" & Format([Drawing Number],"00000")
Or in a query you can put the following in the 'field' row of a blank column
in query design view:
FullDrawingNumber:[ClassCode] & "-" & Format([Drawing Number],"00000")
This will return a column named FullDrawingNumber in the query, to which you
can bind a text box control on a form or report.
One caveat: in a multi-user environment on a network, if two or more users
try to add a new record for the same class code simultaneously the first one
to save the record will succeed, but the subsequent ones will get an error
because of the key violation. If this could be a problem the error can be
handled in the form's Error event procedure.
As the class codes seem to be in upper case you can make sure that this is
the case even if the user enters them in lower or mixed case by putting the
following code in the ClassCode control's KeyPress event procedure:
Dim strCharacter As String
' Convert ANSI value to character string.
strCharacter = Chr(KeyAscii)
' Convert character to upper case, then to ANSI value.
KeyAscii = Asc(UCase(strCharacter))
If you are unfamiliar with entering code in event procedures this is how its
done:
Select the control in form design view and open its properties sheet if its
not already open. Then select the relevant event property in the properties
sheet. Click on the 'build' button; that's the one on the right with 3 dots.
Select 'Code Builder' in the dialogue, and click OK. The VBA window will
open at the event procedure with the first and last lines already in place.
Enter the lines of code between these two existing lines.
Ken Sheridan
Stafford, England
tennmo said:
I am very much a beginner with Access so don't think to bad of me when you
read my question. My boss wants me to create a drawing number database where
the number includes a class code data field with a seq. auto generated number
for each class code. Here are some examples: BRKT-10000, BRKT-10001,
FAST-10000, FAST-10001.
How do I do this? Might be very easy but when you don't know anything, very
hard.
Thanks in advance for any help you can give.
