How to generate a 7 digit autonumbered code?

[seraphicmortal]

Hi Guys!

I need some help here...

I am still learning vb. I wana generate a 7 digit code (YY-nnnnn) where YY is the current year and nnnnn is an auto-increment number. I am gona use this as a primary key.

Hope someone can help me with my dilemma.

Thanks a lot!

[David.Poundall]

Use Format((Date),"yy") to get the year part, keep the year and your main number in the registry. Presumably you will be making this into a function so check the registry at the start of the function, if the year changes you know you have a rollover so then reset the number. Failing that use the registry number plus one, then stash the new number in the registry.

Problem comes if another PC wants to access the same database. In that case you would be better stashing the year and the number somewhere in the database itself. But even that can get messy if you have multiple people accessing the database.

Best thing to do is let the database do an autonumber for you. Forget the Year thing, add it as a field in the database if you really want to.

HTH

[chemicalNova]

Do you mean something like this?:

Option Explicit
 
Private Sub Command1_Click()
Dim i As Integer
For i = 100 To 600
    Text1.Text = Text1.Text & Year(Now) & "-" & i & vbCrLf
    DoEvents
Next i
End Sub

chem

[seraphicmortal]

I figured I can use this:
uniqueworkno.text=FORMAT(curyear, 'YY')&'-'&FORMAT(workno, '#####')

but i don't know what to do with the increment part....though i also am not sure if the above code will work... =(

[waxz]

do this

workno = workno + 1

and then

uniqueworkno.text=FORMAT(curyear, 'YY')&'-'&FORMAT(workno, '#####')

[seraphicmortal]

no for loop?

[waxz]

from what i understand you want to make a an id per entery

so

when ever u add an entery and need to make an id just obtain the last 5 digits of the last entery and then plus one to it

the for loop will most likely be used when you want to do something with ALL the id's

[seraphicmortal]

oh ok...sowi!

Yes, you're correct! I wana create a unique id everytime I make an entry. =)

Simple eyh but that works for me!

Thanks a lot!

[randem]

seraphicmortal,

You can create a unique id using the API GetTickCount. You can use this as a unique number by appending it to the YY and DD.

[David.Poundall]

You can create a unique id using the API GetTickCount. You can use this as a unique number by appending it to the YY and DD.

Strictly speaking Randem there is a chance that the primary key won't be unique using that.

GetTickCount retrieves the number of milliseconds that have elapsed since Windows was started. Admitedly the chances of ever hitting a record create at exactly the ame time between sessions is vanishingly small .... but sods law could be a factor here.

If the application is only going to be used on one machine by one user then saving a number in the registry and incrementing it when you need to will do the trick.

[szlamany]

Is this for a database, such as ACCESS or MS SQL Server?

We use MS SQL server and have primary keys of FISCAL YEAR+ENTRY # (2006-000001, 2006-000002, and so on) in our accounting systems.

[seraphicmortal]

Szlamany,

Yep, I'm gona use an ACCESS database for this. Can you tell me how you come up with your primary keys pls.....?

[szlamany]

We have a control table that we get the "last value" used for a fiscal year and then increment it so that we mark that value used.

[seraphicmortal]

Huh? Can you show me your SQL code pls? if it's not that much though...

[szlamany]

Ok - here's a post with the concept - it's not ACCESS, but MS SQL server - so it's a STORED PROCEDURE that has a bunch of queries that execute as a single transaction. But you should be able to see the concept and translate it to VB and ACCESS...

http://www.vbforums.com/showpost.php...7&postcount=11

[seraphicmortal]

hey Szlamany,

Is it possible for a field (for example workno) that is autonumbered (for auto-increment purposes) to set as a five digit number?? for example.. the 00001 as the first record, 00002 as the second and so on and so forth????

how do i do this?

[dglienna]

You could probably start with 10000 to get five digits. Otherwise, you would have to format it to get the leading zeros.

[seraphicmortal]

like what I did in post #4?

uniqueworkno.text=FORMAT(curyear, 'YY')&'-'&FORMAT(workno, '#####')

[dglienna]

Actually, if the field is called workno, then this would work.

uniqueworkno.text = FORMAT(curyear, "YY") & "-" & _
                             FORMAT(workno, "#####'")

[seraphicmortal]

ok...i'll give it a go.

Thanks a lot for the help guys! =)

[szlamany]

like what I did in post #4?

Doing that means it won't be auto-generated in the database - but processed on the client side.