5 Replies - 3227 Views - Last Post: 30 August 2011 - 02:58 AM

#1 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

creating Auto Number Generation

Posted 15 July 2007 - 04:57 AM

Description: to run this code the connection must support sql Queries and the suto number Field must be an integer data typecreating Auto Number generation in any connection
public function Generate_No( Table_Name as String, Auto_Field_Name as String) as integer
dim con as new connection
dim rs as new Recordset

' write the codding for connection
'you can use any connection
  rs.open("Select Max(" & Auto_Field_Name & ") from " & Table_Name & ""),con
' now it will gives the Biggest number
if not rs.eof then
     Generate_No=val(RS(0)) + 1
else
     Generate_No=1
end if



Is This A Good Question/Topic? 0
  • +

Replies To: creating Auto Number Generation

#2 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: creating Auto Number Generation

Posted 15 July 2007 - 04:57 AM

Description: to run this code the connection must support sql Queries and the suto number Field must be an integer data typecreating Auto Number generation in any connection
public function Generate_No( Table_Name as String, Auto_Field_Name as String) as integer
dim con as new connection
dim rs as new Recordset

' write the codding for connection
'you can use any connection
  rs.open("Select Max(" & Auto_Field_Name & ") from " & Table_Name & ""),con
' now it will gives the Biggest number
if not rs.eof then
     Generate_No=val(RS(0)) + 1
else
     Generate_No=1
end if
End Function

Was This Post Helpful? 0
  • +
  • -

#3 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: creating Auto Number Generation

Posted 15 July 2007 - 04:57 AM

Description: to run this code the connection must support sql Queries and the suto number Field must be an integer data typecreating Auto Number generation in any connection
public function Generate_No( Table_Name as String, Auto_Field_Name as String) as integer
dim con as new connection
dim rs as new Recordset

' write the codding for connection
'you can use any connection
  rs.open("Select Max(" & Auto_Field_Name & ") from " & Table_Name & ""),con
' now it will gives the Biggest number
if not rs.eof then
     Generate_No=val(RS(0)) + 1
else
     Generate_No=1
end if
End Function



'This function is For id Field like 'S00001','S00002','S00003','E00001','E00002','E00003'..
Public Function Generate_No1(Table_Name As String, Auto_Field_Name As String, CategoryKey As String, Optional IdLength As Integer = 5) As String
Dim con As New Connection
Dim rs As New Recordset
Dim MAx_No As Integer

' write the codding for connection
'you can use any connection

 rs.Open ("Select Max(" & Auto_Field_Name & ") from " & Table_Name & " Where " & Auto_Field_Name & " like '" & CategoryKey & "*'"), con
' in the above Segment change the wildcard depends on the Database ; For Access *, For Sqlserver %, and so on
' Where condition Filter the Records
' Since it is formated it will gives the Biggest value
' Note : S002 is biger than S0019 so it will return s003 
' so when you prepare Id Field this key give at least 6 characters
If Not rs.EOF Then
     MAx_No = Val(Right(rs(0), Len(rs(0)) - Len(CategoryKey))) + 1
     Generate_No = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0"))
Else
     MAx_No = 1
     Generate_No = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0"))
End If
End Function


Was This Post Helpful? 0
  • +
  • -

#4 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: creating Auto Number Generation

Posted 15 July 2007 - 04:57 AM

Description: to run this code the connection must support sql Queries and the suto number Field must be an integer data typecreating Auto Number generation in any connection
public function Generate_No( Table_Name as String, Auto_Field_Name as String) as integer
dim con as new connection
dim rs as new Recordset

' write the codding for connection
'you can use any connection
  rs.open("Select Max(" & Auto_Field_Name & ") from " & Table_Name & ""),con
' now it will gives the Biggest number
if not rs.eof then
     Generate_No=val(RS(0)) + 1
else
     Generate_No=1
end if
End Function



'This function is For id Field like 'S00001','S00002','S00003','E00001','E00002','E00003'..
Public Function Generate_No1(Table_Name As String, Auto_Field_Name As String, CategoryKey As String, Optional IdLength As Integer = 5) As String
Dim con As New Connection
Dim rs As New Recordset
Dim MAx_No As Integer

' write the codding for connection
'you can use any connection

 rs.Open ("Select Max(" & Auto_Field_Name & ") from " & Table_Name & " Where " & Auto_Field_Name & " like '" & CategoryKey & "*'"), con
' in the above Segment change the wildcard depends on the Database ; For Access *, For Sqlserver %, and so on
' Where condition Filter the Records
' Since it is formated it will gives the Biggest value
' Note : S0002 is smaller than s0019 but S002 is bigger than S0019 so it will return s003 
' so when you prepare Id Field give at least 6 characters for the keyfield
If Not rs.EOF Then
     MAx_No = Val(Right(rs(0), Len(rs(0)) - Len(CategoryKey))) + 1
     Generate_No1 = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0"))
Else
     MAx_No = 1
     Generate_No1 = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0"))
End If
End Function


Was This Post Helpful? 0
  • +
  • -

#5 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: creating Auto Number Generation

Posted 15 July 2007 - 04:57 AM

Description: to run this code the connection must support sql Queries and the suto number Field must be an integer data type Execute like this Dim MAxno as Integer MAxno =Generate_No("Customers", "customerId") Dim StrMaxNo as String MAxno =Generate_No("Employers", "EmployeeId","E",5) Will generate The following sequence 'E00001','E00002','E00003','E00004' MAxno =Generate_No("Employers", "EmployeeId","S",5) Will generate The following sequence 'S00001','S00002','S00003','S00004' MAxno =Generate_No("Employers", "EmployeeId","S",4) Will generate The following sequence 'S0001','S0002','S0003','S0004' creating Auto Number generation in any connection
public function Generate_No( Table_Name as String, Auto_Field_Name as String) as integer
dim con as new connection
dim rs as new Recordset

' write the codding for connection
'you can use any connection
  rs.open("Select Max(" & Auto_Field_Name & ") from " & Table_Name & ""),con
' now it will gives the Biggest number
if not rs.eof then
     Generate_No=val(RS(0) & "") + 1
else
     Generate_No=1
end if
End Function



'This function is For id Field like 'S00001','S00002','S00003','E00001','E00002','E00003'..
Public Function Generate_No1(Table_Name As String, Auto_Field_Name As String, CategoryKey As String, Optional IdLength As Integer = 5) As String
Dim con As New Connection
Dim rs As New Recordset
Dim MAx_No As Integer

' write the codding for connection
'you can use any connection

 rs.Open ("Select Max(" & Auto_Field_Name & ") from " & Table_Name & " Where " & Auto_Field_Name & " like '" & CategoryKey & "*'"), con
' in the above Segment change the wildcard depends on the Database ; For Access *, For Sqlserver %, and so on
' Where condition Filter the Records
' Since it is formated it will gives the Biggest value
' Note : S0002 is smaller than s0019 but S002 is bigger than S0019 so it will return s003 
' so when you prepare Id Field give at least 6 characters for the keyfield
If Not rs.EOF Then
     If Not IsDbNull(rs(0)) then
          MAx_No = Val(Right(rs(0), Len(rs(0)) - Len(CategoryKey))) + 1
     Else
          MAx_No = 1
     End if
     Generate_No1 = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0"))
Else
     MAx_No = 1
     Generate_No1 = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0"))
End If
End Function

Was This Post Helpful? 0
  • +
  • -

#6 memon1987   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 10-December 08

Re: creating Auto Number Generation

Posted 10 December 2008 - 12:17 PM

ok but how can i know i that how many box in programm, tables and cammand button ? pls tell me
Was This Post Helpful? 0
  • +
  • -

#7 nmviradiya   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 0
  • Joined: 25-January 11

Re: creating Auto Number Generation

Posted 28 February 2011 - 06:33 AM

Sometime we want to generate s001,s002,s003 this type of number at this time which code is use full
Was This Post Helpful? 0
  • +
  • -

#8 newaudrey   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 162
  • Joined: 08-July 10

Re: creating Auto Number Generation

Posted 29 April 2011 - 11:47 PM

please answer my question how can i call the function above? pls help
Was This Post Helpful? 0
  • +
  • -

#9 thava   User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,607
  • Joined: 17-April 07

Re: creating Auto Number Generation

Posted 04 May 2011 - 02:05 AM

dim NewId as string newid = Generate_No1("Students","StudentID","S",7) msgbox (NewId) Note you need to open the connection object
Was This Post Helpful? 0
  • +
  • -

#10 newaudrey   User is offline

  • D.I.C Head

Reputation: -3
  • View blog
  • Posts: 162
  • Joined: 08-July 10

Re: creating Auto Number Generation

Posted 30 August 2011 - 02:58 AM

how can i use this number sir? i added this code Public Function Generate_No(Table_Name As String, Auto_Field_Name As String) As Integer Dim con As New Connection Dim rs As New Recordset ' write the codding for connection 'you can use any connection rs.Open ("Select Max(" & Auto_Field_Name & ") from " & Table_Name & ""), con ' now it will gives the Biggest number If Not rs.EOF Then Generate_No = Val(rs(0)) + 1 Else Generate_No = 1 End If End Function 'This function is For id Field like 'S00001','S00002','S00003','E00001','E00002','E00003'.. Public Function Generate_No1(Table_Name As String, Auto_Field_Name As String, CategoryKey As String, Optional IdLength As Integer = 5) As String Dim con As New Connection Dim rs As New Recordset Dim MAx_No As Integer ' write the codding for connection 'you can use any connection rs.Open ("Select Max(" & Auto_Field_Name & ") from " & Table_Name & " Where " & Auto_Field_Name & " like '" & CategoryKey & "*'"), con ' in the above Segment change the wildcard depends on the Database ; For Access *, For Sqlserver %, and so on ' Where condition Filter the Records ' Since it is formated it will gives the Biggest value ' Note : S002 is biger than S0019 so it will return s003 ' so when you prepare Id Field this key give at least 6 characters If Not rs.EOF Then MAx_No = Val(Right(rs(0), Len(rs(0)) - Len(CategoryKey))) + 1 Generate_No = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0")) Else MAx_No = 1 Generate_No = CategoryKey + Format(MAx_No, String(IdLength - Len(CategoryKey), "0")) End If End Function Private Sub Command1_Click() Dim NewId As String NewId = Generate_No1("Students", "StudentID", "S", 7) MsgBox (NewId) End Sub wen i click the button i got this error compile error: function call on left-hand side of assignment must return variant or object please hekp me. im just a newbie
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1