Custom Primary Key

[PotatoChip]

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])", "TblDocumentReq uests"), 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!!

[ADezii]

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])", "TblDocumentReq uests"), 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!!

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.

Me![PDNo] = Left$(DLast("[RDNo]", "tblDocumentRequests"), 2) & Format$(Now(), "yy") & _
       "-" & Format$(Val(Right$(DLast("[RDNo]", "tblDocumentRequests"), 3)) + 1, "000")

[PotatoChip]

I tried what you suggested and it didn't work. I kept getting error messages and had to delete parts of the code.

[ADezii]

I tried what you suggested and it didn't work. I kept getting error messages and had to delete parts of the code.

Download the Attachment and have a look-see

[PotatoChip]

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!!!

[ADezii]

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!!!

You are quite welcome.