by Chittaranjan Dhurat (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB 6.0
Difficulty: Unknown Difficulty
Originally Published: Thu 23rd October 2003
Date Added: Mon 8th February 2021
Rating: 
(1 Votes)
Autogeneration of code at runtime for database table field
VBcoders
'Augument List:
'1)lengthofcode : length of code (or field)
'2)OldRes : recordset name use in this form
'3)codetype : "number" or "charnumber"
'4)firstletter : first letter in character or "", compulsary for codetype "charnumber"
'type of code
'1) number
'ex : 1,2,3,......
'2) charNumber
'ex : A001,A002,A003
'variable declaration
Dim lastCode As Variant
Dim newCode As Variant
Dim res As Object
Set res = OldRes
Dim typeofCode As String
'selecting code type
Select Case codetype
Case "number" ' code for type 1 number
'requirements of type 1 number
'1) recordset name as object
'2) name of autogenerated field
If res.BOF = False And res.EOF = False Then
'Move to the last record of the recordset
res.MoveLast
'save the autogenerated field data
lastCode = res(fieldName)
newCode = lastCode + 1
Else
newCode = 1 'first record
End If
' then return the new code
codeGeneration = newCode
Case "charnumber" ' code for type 2 charnumber
'requirements
'1) recordset name as object
'2) name of autogenerated field
'3) first letter of code
If Not res.BOF And Not res.EOF Then
'Move to the last record of the recordset
res.MoveLast
'save the autogenerated field data
lastCode = res(fieldName)
Dim lastNumber As Integer
Dim newNumber As Integer
lastNumber = Val(Mid(lastCode, 2))
newNumber = lastNumber + 1
newCode = firstLetter & String((lengthofCode - Len(Str(newNumber))), "0") & newNumber
Else
newCode = firstLetter & String(Len(Str(lengthofCode - 2)), "0") & "1"
End If
' returning the new code
codeGeneration = newCode
End Select
End Function
