• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Function to generate next primary key (string)

Function to generate next primary key (string)

Function to generate next primary key (string)

Rate Function to generate next primary key (string)

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'Supply current key as parameter to function and it will return next primary 'key. The left part of key supplied may be a string of characters. The right 'part will be numbers. No intermediate charachters are allowed between 'numbers. For consistancy the make use of 0's to obtain fixed length 'strings
'For ex. autogen("Cust0001") will return "Cust0002"
'autogen("item009") will return "item010"

Public Function AutoGen(PKey As String) As String
  Dim LenId As Integer, LenZeros As Integer, Num As Long
  Dim Result As String, Cnt As Integer, Chara As String
  On Error GoTo ErrHandle
  For Cnt = 1 To Len(PKey)
    Chara = Mid(PKey, Cnt, 1)
    If Chara >= "0" And Chara <= "9" Then Exit For
    LenId = LenId + 1
    Result = Result + Chara
  Next Cnt
  Num = Mid(PKey, LenId + 1)
  Num = Num + 1
  Chara = Num
  LenZeros = Len(PKey) - Len(Chara) - Len(Result)
  For Cnt = LenId + 1 To LenId + LenZeros
    Result = Result + "0"
  Next Cnt
  Result = Result & Num
  AutoGen = Result
  Exit Function
ErrHandle:
  MsgBox "Invalid parameter supplied", vbCritical
End Function