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

6 Years
Discussion Span
Last Post by kinwang2009

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"

   holdID1 = .Fields("ID")
ENd With


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

   holdID2 = .Fields("EmployeeID")

End With

if holdID1 >  holdID2 then
	TextEmpID.Text = holdID1 + 1
	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:

4 - this record will be deleted

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

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

Edited by PoisonedHeart: n/a

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.