by Abhijit Desai (3 Submissions)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 18th July 2002
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Function to generate next primary key (string)
VBcoders
'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
