Custom Primary Key

[RArroyo10]

I am trying to create a custom Primary Key (PK) in access. What I am trying to do is create a key where the first part (prefix) of the key is static and will be 'ABC', 'DEF', and 'GHI' for instance. The second part of the key is numeric and sort of auto-generated. I can't use the auto-numbering feature in Access because the number should be sequential with whatever prefix it has. So I should be able to have ABC001, ABC002, DEF001, and DEF002. Not sure how to go about doing this. I know you can create a PK with two different fields but how could I go about automating the number portion of the PK, and how can I concatenate the result to display in a form?

Any help would be appreciated!

[jforbes]

This post might get you going in the right direction: http://bytes.com/topic/access/answer...mber-data-type

I copied the Code over to make it easier to read this post:

Public Function getKey(ByRef sTable As String, ByRef sField As String, ByRef iKeyLen As Integer, ByRef sPrefix As String) As String
     Dim sLastKey As String
     sLastKey = DMax(sField, sTable)
     getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iKeyLen - Len(sPrefix))
 End Function

I think whether or not it will work right out of the box for you depends on how you want to determine the prefix.

[zmbd]

also use the site search using (w/o quotes :) )
" ms access serial number "
or "ms access sequence number"
You will turn up a few threads that cover this topic... some in great detail :)

Once you get your code/sql together, if you are still having issues, please don't hesitate to post back :)