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

Custom Primary Key

P: 26
I'm trying to work out how to format a custom primary key using the current year as part of the key. I've copied code which I use 'OnCurrent' for the form. The code I've copied is:

Private Sub Form_Current()
If Me.NewRecord Then
Me!RDNo = Format(Nz(DMax("val([RDNo])", "TblDocumentRequests"), 0) + 1, "000000")
End If

End Sub
What this should do (based on what it does in my other table) is automatically update the primary key plus one of whatever the last number used was. However, I want is the primary key to read as "RD08-001" (with the 08 being the current year and the 001 to be the bit updated automatically).I've tried every which way to update the code to do this but I just can't get it to work. Please help!!
Dec 9 '08 #1
Share this Question
Share on Google+
5 Replies


ADezii
Expert 5K+
P: 8,597
@PotatoChip
This will definitely work as long as you maintain the strict Format that you demonstrated, but there is probably a better solution that another Member will come up with so be patient. Be advised that that does not take into consideration a Year transition like from 08 to 09. The first Record for the New Year would have to be manually entered, or the logic would have to be extended.
Expand|Select|Wrap|Line Numbers
  1. Me![PDNo] = Left$(DLast("[RDNo]", "tblDocumentRequests"), 2) & Format$(Now(), "yy") & _
  2.        "-" & Format$(Val(Right$(DLast("[RDNo]", "tblDocumentRequests"), 3)) + 1, "000")
Dec 10 '08 #2

P: 26
I tried what you suggested and it didn't work. I kept getting error messages and had to delete parts of the code.
Dec 10 '08 #3

ADezii
Expert 5K+
P: 8,597
@PotatoChip
Download the Attachment and have a look-see
Dec 11 '08 #4

P: 26
I don't know what I was doing wrong but I copied and pasted your code into my form and it works perfectly!

Thanks so much!!!
Dec 12 '08 #5

ADezii
Expert 5K+
P: 8,597
@PotatoChip
You are quite welcome.
Dec 12 '08 #6

Post your reply

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