CREATE ID, NOT IDENTITY, FROM CONCATENATED VALUES
Posted on 2012-12-20
Hello there, this is my case:
I have a main table which has an ID (not identity), which is composed by two values: 1 is a value which is read and added in 1, and 2, a constant which is used to determine the location where the record was created. Like this:
A column like I said, contains the Incremented values (1001), which runs depending on the used done on the location prefixed (7, 5), this prefix is the constant used.
I need then, to be able to read the last incremented value, assign it to the field, and concatenate that value with the prefix pro assign this way the ID of the main table.
I need to ask this question because actually I'm a complete novice in VS. Within VS I code in VB.NET, becase I have some experience usin VBA, but can't get it. A copy of my VBA code is this
In my module, this is the code I used to determine the last used Number within the location set:
Function ControlNumber(TableName As String) As Integer
Dim RSCN As DAO.Database
Dim RSUCN As DAO.Recordset
Set RSCN = CurrentDb()
STRCN = "SELECT * FROM " & TableName & " WHERE " & TableName & ".EmpID LIKE '" & PrefixDb & "*' ORDER BY EmpID"
Set RSUCN = RSCN.OpenRecordset(STRCN, dbOpenDynaset, dbSeeChanges, dbOptimistic)
ControlNumber = RSUCN.Fields(1) + 1
Set RSUCN = Nothing
And this is the sub where I concatenate the new ID based on conditions:
Private Sub Command163_Click()
CtlNum = ControlNumber("EMPLOYEE")
If IsNull(Me.EmpID) Then
Me.EmpAutogen = CtlNum
EmpID = PrefixDb & CtlNum
I do really hope you can give me the light I need to continue with my project, which is moving into a web application the current one which run within a LAN.