Primary Key Generated Case To Case?

  • Thread starter Thread starter Faraz A. Qureshi
  • Start date Start date
F

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.
 
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
 
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.
 
Back
Top