• Home
 · 
• Databases/Data Access/DAO/ADO
 · 
• Autogeneration of code at runtime for database table field

Autogeneration of code at runtime for database table field

Autogeneration of code at runtime for database table field

Rate Autogeneration of code at runtime for database table field

• 1
• 2
• 3
• 4
• 5

(1(1 Vote))

'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