I am working using Visual Basic (12) to work my database. I have one particular table that is used for change requests (you don't need to know what that is, but if you are interested I am happy to tell you what that is). For this table I use an autonumber field. The first record would be 1, with each record being set to increment by 1.
So, when I create a new record, in the VB code I create the record like so:
'Create new change request number
Try
Me.LnkChangeRequestTableAdapter.FillByLastCRNum(Me ._MasterBase3_0ItemMasterDataSet.lnkChangeRequest)
glbintCRNum = _MasterBase3_0ItemMasterDataSet.lnkChangeRequest(0 ).intChangeID
glbintCRNum += 1
Catch ex As Exception
glbintCRNum = 1
End Try
The query being used (FillByLastCRNum), goes like this
SELECT TOP 1 intChangeID
FROM lnkChangeRequest
ORDER BY intChangeID DESC
This works fine, as long as there have never been any records deleted. In normal circumstances, for this table, no record should ever be deleted. But sometimes crap happens whether you want it to or not and a record, or records, or even the entire set of records can be deleted. Additionally, I do not believe this to be a very good approach even under perfect circumstance, since the query merely uses intChangeID from the last created and uses that to provide a value for glbintCRNum.
It occurs to me that perhaps I should not be using an auto number field for this type of activity. However, it is also my thought that this is exactly the type of activity that an auto number field should be used.
So could someone explain to me how to query a table using an auto number field so that it can be worked to ensure continuity should there be missing records (I believe the answer is in the query.)? Or offer a different approach than using an auto numbered field?
If my description is lacking in content or the explanation is not coherent please let me know and I will try to do a better job of explaining.