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.
this is, for your information, against data normalisation. it should be 2 fields ... makes things easier ...
in your case, your (sql) code is completely inefficient due to that design.
anyhow, what IS the exact question?