How to create a custom auto-incremented number

[RodgerC]

Anyone with any ideas on how to create a custom auto-incremented number in Access to be used as a primary key? I want to have a number that starts with the last 2 digits of the current year then a 3 digit auto incremented number. ie. 01-001, 01-002, etc.

 

[StewartGW]

Create a function that gets the max value of the primary key using a SQL statement as follows:

Set rs = cn.Execute("SELECT MAX(pk_key) + 1 FROM TableName&quot

If Not rs.EOF Then
strPK_KEY = Right(CStr(Year(Date()),2) & "-001"
Else
strPK_KEY = rs(0)
End If

 

[JerryDennison]

RogerC, if I were you I would NOT use this field as your primary key. Use a regular autonumber field as the PK and this field only for end user consumption.

 

[RodgerC]

Thanks for the hint Jerry, can you tell me more about why you say not to use this field as primary key?

 

[JerryDennison]

One good reason is it's a text field. These take more space and are slower to process than numeric fields. Another good reason is that primary keys should not be thought of as something that has meaning. They are a means for Access to address or uniquely identify each record. They have not meaning.