Find next open key field

[bodhran1]

Hi all,

I have a numerical field in my table that has skipped a few numbers.

That field has been changed to my key field now (a long story)

Can I use VB to search the primary key field line by line and if the next record isn't one number higher than the last, it will add that number?

My table has the following number 605100, 605101, 605102 & 605104

I want VB to read these, figure out that 605103 isn't used and then add that number as a new record.

Thanks !!

Dave

 

[WayneRyan]

Dave:


' *********************************
Dim dbs As Database
Dim rst As Recordset
Dim sql As String
Dim LastNum As Long
Dim ThisNum As Long
Dim LoopCtr As Long

Set dbs = CurrentDb
sql = "Select * From YourTable Order By TheNum"
Set rst = dbs.OpenRecordset(sql)
rst.MoveFirst
LastNum = rst!TheNum
rst.MoveNext
While Not rst.EOF And Not rst.BOF
ThisNum = rst!TheNum
If (ThisNum - LastNum) > 1 Then
For LoopCtr = LastNum + 1 To ThisNum - 1
rst.AddNew
rst!TheNum = LoopCtr
' Add any other fields
rst.Update
Next LoopCtr
End If
LastNum = ThisNum
rst.MoveNext
Wend
' *********************************

I haven't tried it, but make a copy of your
db and give it a go.

Wayne