Primary Key Generated Case To Case?

[Faraz A. Qureshi]

I have a field in a table Consisting entries choice in a dropdown like:
Class A
....
Class C
....
Class AA
....

I want the Primary Key of the said table to be autogenerated as follows:

Pickup the last character(s) from the field "Class" i.e. A, C, AA & the
number of their relative records, i.e. if 100, 900, 8755 records exists,
respectively the key generated to be and updated as to:
A-0001 to A-0100
C-0001 to C-0480
AA-0001 to AA-8755

Any suitable formula/code for the said key field shall be highly obliged.

 

[Tom van Stiphout]

On Wed, 3 Feb 2010 21:53:01 -0800, Faraz A. Qureshi

That seems like a REALLY BAD IDEA. Primary keys should typically be
small, hidden, and not have any meaning. The Autonumber is a perfect
example.
Then if you wish you can have another field with the values you want.
I would probably do this in VBA although I have a sense this can also
be done in SQL if you're hard-core.

update myTable set myField = myFunction(Class)
So you're passing the class values into the new function myFunction
which will do the calculation and return your desired result.

(in a standard module)
public function myFunction(byval strClass as string) as string
dim strResult as string
strResult = Mid$(strClass, 7) & "-0001 to " & Mid$(strClass, 7) & "-"
dim recCount as long
recCount = DCount("*","myTable", "Class='" & strClass & "'"
strResult = strResult & format$("0000", recCount)
myFunction = strResult
end function

Of course you realize that there are all kinds of problems with this
approach, right?
* The record count may change over time
* There may be more than 9999 records for a class

-Tom.
Microsoft Access MVP

 

[Daryl S]

Faraz -

I would not recommend this as the primary key. Let an autonumber do that.
You can create this field, and make it a unique key if you like. On the form
that the user is entering the data, on the BeforeUpdate event, you can build
this record as follows:

Me.NewAltKey = Right(Me.Class,len(Me.Class)-6) & "-" &
Format(DCount("[YourPrimaryKey]", "YourTableName", "[Class] = '" & Me.Class &
"'"),"0000")

Note that if records can be deleted out of this table, then this method will
fail because it is using a count. You can also set up a recordset and find
the max NewAltKey for the class, and parse it and increment it to make that
work. It is more involved, but it would solve that problem.

 

[Faraz A. Qureshi]

XClent Tom!