please help me to do with my system.
i want my TextEmpID.Text is auto generated evrytime i will add another employee name.
Field = "EmployeeID"
i'm using ms access as ADODB


thanks in advance

if you just want to auto increment the field values then you could:

1.) Open the table
2.) sort the records in ascending order.
3.) move to the last record
4.) get the id field, then add + 1....

This is for simple incrementation., but if you really want your app to behave like access' incrementation, then you could do this trick...

1.) Do the following steps above.
2.) When a record is deleted, store first it's id in a separate table, then when you will auto increment the values, you will have to open this table and see what is the greatest id number, after sorting it out, compare that id to the original table

3.) Open the table for deleted id's
4.) sort the table, move to last record, store the field value in a variable or what ever you want.
5.) Open the table for the original id's
6.) sort the table, move to last record, store the field value in a variable or what ever you want.
7.) compare the two fields, if one of them is greater than then get that value and add + 1, then show it in the textbox....

Dim rsEmployeeTable as New Adodb.Recordset
Dim rsDeletedIDs    as new Adodb.Recordset

Dim holdId1 as Integer
Dim holdID2 as Integer


With rsDeletedIDs
   If .State = adStateOpen then .Close
   .Open "Select * from tblDeletedID"
   If .BOF AND .EOF = True then
	holdID1 = 0
	Goto jumphere
   ENd if
   .Sort = "ID ASC"
   .MoveLast

   holdID1 = .Fields("ID")
   
ENd With

jumphere:

With rsEmployeeTable
   If .State = adStateOpen then .Close
   .Open "Select * from tblEmployee"
   .Sort = "EmpID ASC"
   .MoveLast

   holdID2 = .Fields("EmployeeID")

End With


if holdID1 >  holdID2 then
	TextEmpID.Text = holdID1 + 1
Else
	TextEmpID.Text = holdID1 + 2
ENd if

This computer does not have VB6 installed, so I just visualize it in with notepad. :P hope there are no errors....

NOTE: Due to data integrity and security, the auto increment features of DBMS are these types, wherein, whenever a number have already generated, and its deleted it will never be generated again: example:

FieldID
1
2
3
4 - this record will be deleted

the next time you add a new record the value is now.

FieldID
1
2
3
5 - see this, it skipped the number four which has been already entered from the previous transaction.

Edited 6 Years Ago by PoisonedHeart: n/a

This article has been dead for over six months. Start a new discussion instead.