troubleshooting Question

CREATE ID, NOT IDENTITY, FROM CONCATENATED VALUES

Avatar of LUISOFO
LUISOFO asked on
Web Development SoftwareVisual Basic.NETMicrosoft SQL Server 2008
12 Comments5 Solutions310 ViewsLast Modified:
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:

71001     51001
71002     51002
.              .
.              .
7nnnn     5nnnn

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
Dim STRCN

Set RSCN = CurrentDb()
STRCN = "SELECT * FROM " & TableName & " WHERE " & TableName & ".EmpID LIKE '" & PrefixDb & "*' ORDER BY EmpID"
Set RSUCN = RSCN.OpenRecordset(STRCN, dbOpenDynaset, dbSeeChanges, dbOptimistic)
With RSUCN
.MoveLast
ControlNumber = RSUCN.Fields(1) + 1

End With
RSUCN.Close
Set RSUCN = Nothing

End Function
______________________________________________________

And this is the sub where I concatenate the new ID based on conditions:

_______________________________________________________


Private Sub Command163_Click()
Dim CtlNum
CtlNum = ControlNumber("EMPLOYEE")
If IsNull(Me.EmpID) Then
Me.EmpAutogen = CtlNum
EmpID = PrefixDb & CtlNum
DoCmd.RunCommand acCmdSaveRecord

End If

End Sub
________________________________________________________

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.
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 5 Answers and 12 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 5 Answers and 12 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros