I have a receipt generating access application. I have used auto number for my receipts, but the number jumping. So I created a SequentialID number field and used following code to enter before insert event. But since I am not able to index the same with no duplicates, the users got duplicate receipt numbers. How do I solve this issue.
Private Sub Form_BeforeInsert(Cancel As Integer)
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim newID As Long
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT MAX(SequentialID) AS MaxID FROM [Seva Details Query];")
If Not (rs.EOF And rs.BOF) Then
newID = rs("MaxID") + 1
Else
newID = 1
End If
Me.SequentialID = newID
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub