AutoGenerate Number In VB Itself

[Guest_imported]

Hi,
I have a field, SerialNumber in my table(Access). How do I autogenerate the serial number, say 0001 for the first time and increment it by 1 (ie.) 0002 for the next and so on. I am getting a datatype error when the serial number is set to autonumber in the table. So I have made it as a text field. I need to autogenerate using code. Please lemme how to do it.
Thanks

 

[DevonTaig]

If you have a text field that is really to be treated as a sequential number, there are a couple of possible solutions you might try. First, add another numeric field to the table and autoincrement it...through code, set the text field equal to the numeric field with the correct number of zeros pre-pended to the string...For example, if the serial number needs to be 5 digits, and the autoincrement field is 123, then your code would produced "00123". Of course that doesn't create a very normalized database. Since this is a Microsoft Access database, another option might be to store the last serial number value in the registry and then add one to it the next time you generate it. See SaveSettings, and GetSettings in the VB documentation (there are also several WinAPI functions that read/write to the registry). Be aware, however, if you persist info in the registry under the CurrentUser hive, you will generate duplicate serial numbers in your table for different users who log in...If that is a concern for you, it is better to use the LocalMachine hive.

 

[Guest_imported]

Hi,
Thanks for replying. I don't think I need to tamper with the registry. Actually I have worked on SQl server also and there I had a procedure which increments the number. I used the code having Param and so on. I would check in the code itself if the previous value was <1000 then append 00(Number generated after calling the sql procedure.) Is there any way to write a procedure in Access. What would be the pocedure.
Thanks

Do see my other posts.

 

[Guest_imported]

Any Help,

 

[sunaj]

Hi,

I'm not sure exactly what you are after, but here is my input for what its worth.

Usually any formatting is done be the program that pulls data out of the database (e.g. ASP page, VB program). It is easy to format a number in these languages.
Notice that a number fields with the index set to 'yes' in sql server automatically increases the values, but there is no guarentee that the number are sequential (try deleting some records and then add new ones).
You can force numbers into a fields that has index set to 'yes' in SQL server. Use 'IDENTITY INSERT ON'.
If you insist on formating the number in SQL server I would write a small stored prodcedure to pull data out of the table. You can use RIGHT('0000000' + CONVERT(varchar(8), @int), 8) to format you number in Transact SQL.

Good Luck



Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[cassandra]

Here is a snippet of my code where I generate a serail number using the Date as part of the number.

Dim leftside As String
Dim rightside As String
Dim tmprecno As String
Dim tmprecno2 As String

Sub nxtrec()


mstrcriteria = &quot;&quot;
mrs.MoveFirst
leftside = Month(Date) & Day(Date) & Mid(Year(Date), 3, 4)
mrs.MoveFirst
Do Until mrs.EOF
ritside = mrs.RecordCount
mrs.MoveNext
DoEvents
Loop

tmprecno = leftside & &quot;-&quot; & rightside

newrit = Val(rightside) + 1

If newrit < 10 Then
rightside = &quot;0000&quot; & newrit
ElseIf newrit >= 10 And newrit <= 99 Then
rightside = &quot;000&quot; & newrit
ElseIf newrit >= 100 And newrit <= 999 Then
rightside = &quot;00&quot; & newrit
ElseIf newrit >= 1000 And newrit <= 9999 Then
rightside = &quot;0&quot; & newrit
End If

tmprecno2 = lefftside & &quot;-&quot; & righttside
txtrecno = tmprecno2


End Sub

Cassandra