Incrementing counter and record deletion

[infinitx]

Hi,

The user wants all of the records in the database to have continuous numbering. I have used AutoNumber in the past but if you delete a record, it produces gaps in the numbering.

I was wondering if there is a way to number the records so they are always numbered continuously (1,2,3,etc.) even if some record is deleted.

I have tried using the following:

Set db = CurrentDb()
Set recordlist = db.Openrecordset("SELECT Max(tblContactHistory.ItemID) AS MaxID FROM tblContactHistory")

ItemID = (recordlist!MaxID + 1)

recordlist.Close
Set recordlist = Nothing

but I still cannot get the records to have continuous numbering after a record is deleted.


Any helped would be appreciated.

Thanks!

 

[dcobau]

I use the following SQL to establish any missing SubID in tblSubmissions:

SELECT T.SubID+1 AS Missing
FROM tblSubmissions AS T LEFT JOIN tblSubmissions AS T1 ON T1.SubID=T.SubID+1
WHERE (((T1.SubID) Is Null));

Note: the SQL is stored in qryMissingSubID

then I attach the following code to btnNew in frmSubmissions:

Dim MissingSubs As Integer
Dim HowManyMissing As Integer

MissingSubs = DLookup("[Missing]", "qryMissingSubID")
HowManyMissing = DCount("[Missing]", "qryMissingSubID")

If HowManyMissing >= 2 Then

Msg = "The system has detected that number " & MissingSubs & " has not been used. Do you want to use it now?"
Style = vbYesNo + vbQuestion + vbDefaultButton2
Title = "IPL Message"
Response = msgbox(Msg, Style, Title)

If Response = vbNo Then

DoCmd.GoToRecord , , acNewRec
Me!SubID = DMax("SubID", "tblSubmissions") + 1

Else

DoCmd.GoToRecord , , acNewRec
Me!SubID = MissingSubs

Hope it helps

Dave