Custom Primary Key

[ClementsK]

I need assistance with creating a custom primary key. I need the key to be C011218001. Broken down, that is C/yy/mm/dd/record sequence number.

Please help! I am at wits end with this one....

Thanks,
Kim

 

[jebry]

Hi Kim!

Assuming your records will entered through a form, you can put this code in the form's Current event procedure:

Dim strSeqNum As String

If IsNull(YourControl) = True Then
strSeqNum = DMax("Right(YourFieldName,3), "YourTable&quot
YourControl = "C" & Format(Date(), "yymmdd&quot & Format(CInt(strSeqNum) + 1, "000&quot
End If

A couple of points:

It would probably be easier to have three fields and in the table definition set the default values to "C", Format(Date(), "yymmdd", the last one can be an autonumber. Then you can format the display by setting the control source of the text boxes to Field1 & Field2 & Format(Field3, "000&quot.

Second, the above definitions will allow for only 1000 records before you will need to start over, unless you plan to restart your sequence number every year or day etc. If that is your intention, then the code above will need to be revised.

hth Jeff Bridgham
bridgham@purdue.edu

 

[ClementsK]

Thank you for your prompt reply. At first glance, that code looks really scary. I assume that I can muddle through it.

You said, "unless you plan to restart your sequence number every year or day etc.". Yes, as the day changes, I want the sequence number to change.

In other words, records entered today would be:
C011218001
C011218002
Tomorrow would be:
C011219001
C011219002

Does that make my predicament worse?

 

[jebry]

Hi again!

Well, that change will make the code a little more complex. Try this:

Dim strSQL As String
Dim intSeqNum As Integer
Dim rst As DAO.Recordset

If IsNull(YourControl) = True Then
strSQL = "Select Top 1 Mid(YourField, 2, 6) As MyDate, Right(YourField, 3) As SeqNum From Your Table Where Mid(YourField, 2, 6) = '" & Format(Date(), "yymmdd&quot & "' Order By Right(YourField, 3) Desc"
Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
If rst.EOF = True And rst.BOF = True Then
intSeqNum = 1
Else
intSeqNum = CInt(rst!SeqNum) + 1
End If
YourControl = "C" & Format(Date(), "yymmdd&quot & Format(intSeqNum, "000&quot
Set rst = Nothing
End If

Again, this code should go in the Form_Current event procedure.

hth
Jeff Bridgham
bridgham@purdue.edu