incremented unique id

[smile4evr]

i hv a vb6 form connected to the ms acess... it has multiple tabs so if a user wants to make any one of the two then a unique key should be followed... preferably the key should be followed by the system year as this could be used for many years... but this key should be incremented only when one tab with this id is submitted... the tabs should not repeat the request id...and the details of the two tabs are stored in different database so we cant acess from one tab...
im totally confused... please help...

[jireh]

I'm also confused with your explanation... can you please rephrase you explanation... sorry!

[smile4evr]

well... its smething like i hv three sstabs... one for veg another for non-veg...nw when a person selects veg/non-veg and submits the form a unique id should be generated...
but when submitted the data is written in different databases so hw can i know the last value and increment??

plz do let me know if im clear enough now or not!

-thnx a lot for da response!

[Teme64]

Not sure if this is even close with what you want:

Dim strSQL As String
Dim oConn As ADODB.Connection
Dim Rs As RecordSet
oConn = CreateObject("ADODB.Connection")
oConn.Open("<conn string>")
strSQL = "INSERT INTO <Table Name> (<fields>) VALUES (<field values); SELECT @@IDENTITY AS 'Identity' "
Rs = oConn.Execute(strSQL)
Print(Rs(0))

What it does is that you use normal SQL Insert statement to add new row. Your table has Identity/AutoNumber field and that value for this new row is returned. This works with JET 4.0.

If this was what you did mean, then you can read more from http://support.microsoft.com/default.aspx/kb/232144

[smile4evr]

hey thankx a lot for the help... but i guess this is not wat i want... wat is jet 4?? sorry but im not sure if im using that...
... thnx... do let me know if u know anyting more!

[Teme64]

Ok. But you are using MS Access?

a unique id should be generated

AutoNumber field is a unique ID. If you have an AutoNumber field in the table, that should work i.e. you get an unique ID for each inserted row. And forget Jet if your not familiar with it :)

hw can i know the last value and increment??

AutoNumber increments itself, you don't have to do that. The code above returns this last, incremented value for you.
If this does not work in your case, then clarify what kind of unique ID you're looking for. And perhaps show a snippet of your code how you're currently doing it.

[smile4evr]

hey actually i dont want to use auto number which in database because then a lot of them get wasted and errors occur if i delete records... that is when i make test runs and later delete those records then it will create a big problem... so without using auto number i just want to make this unique id in the front end- VB 6...
thnx a lott....

[Teme64]

Well, then there are few option left.

One data type that is unique is timestamp. And there are two ways to implement it.
First is to let Access handle it: create a field named "Timestamp", set type to "Date/Time" and from the field properties "Default Value" to Now().

Second is to handle it in VB6 code: create a field named "Timestamp", now you can set it's type to "Text" and every time you add a new row to Access table set CStr(Now()) value to "Timestamp" field.

Does this help anything?