By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,341 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,341 IT Pros & Developers. It's quick & easy.

Custom Primary Key

P: 1
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!
May 4 '15 #1
Share this Question
Share on Google+
2 Replies


jforbes
Expert 100+
P: 1,107
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function getKey(ByRef sTable As String, ByRef sField As String, ByRef iKeyLen As Integer, ByRef sPrefix As String) As String
  2.      Dim sLastKey As String
  3.      sLastKey = DMax(sField, sTable)
  4.      getKey = sPrefix & Right("00000" & Val((Right(sLastKey, Len(sLastKey) - Len(sPrefix))) + 1), iKeyLen - Len(sPrefix))
  5.  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.
May 4 '15 #2

zmbd
Expert Mod 5K+
P: 5,397
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 :)
May 4 '15 #3

Post your reply

Sign in to post your reply or Sign up for a free account.