Generate auto OR # in vb6 & MS Access

[micdansan]

Hey guys,

Anyone help to generate ID reference, for ex.
R-00000001
R-00000002
R-00000003
...... and so on.

[Doogle]

I think you're going to have to use an AutoNumber column in Access and then 'build' the 'front end' after the SELECT.

For example if the Autonumber Column name was 'ID' and it was in a RecordSet named RS

strID = "R-" & Left$("00000000",8 - Len(CStr(RS![ID]))) & CStr(RS![ID])

and to unblock to the original numeric: (assuming it's a Long Type):

lngID = CLng(Mid$(strID, 3))

[micdansan]

Found this code in the internet, I like the return ID format in this code but does'nt work in vb6, I get error compile error sub or function not defined.. highlighted on the IsDbNull

'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

[techgnome]

I'd use an autonumber, then create an expression field that formats it the way you want it to look.

-tg