code for autonumber (1 Viewer)

[Purdue22]

I want to have an autonumber field but when I delete a record I want the number to remain the same and not increment when I replace the record. So if I had a record 955 and I deleted this record then replaced the record with new info it would not skip to 956. Can anyone supply code to ahcieve this.

Thanks

 

[D-Fresh]

You won't be able to use Autonumber here, but you probably could just use a number field and search through the table for an open number... This is a dirty way to do it, but it should serve your purpose...

Public Function GetOpenNumber() As Long
Dim MyDB As Database
Dim MyRecs As Recordset
Dim MySQL As String
Dim TempID As Long

MySQL = "SELECT * FROM [tblRecords] ORDER BY tblRecords.ID"

Set MyDB = CurrentDb
Set MyRecs = MyDB.OpenRecordset(MySQL)

TempID = 0

While Not MyRecs.EOF

If MyRecs("ID") <> (TempID + 1) Then
GetOpenNumber = TempID + 1
Exit Function
Else
TempID = MyRecs("ID")
MyRecs.MoveNext
End If

Wend
GetOpenNumber = TempID + 1
End Function

Call this function to find out an open number in the table, or if there are no gaps, then it will return the last number in the table + 1. Then just set the ID field to the value this function returns, and you're all set. Hope this helps...

Doug