Access Multiple user Auto Number jumping

[Raveen1609]

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

[ArviLaanemets]

Why you can't index it?

You can have only one primary key (a single or multiple field one), but you can additionally have any number of unique indexes (NB! Primary Key and Indexes are not the same!).

Keep the autonumber ID in your table as Primary Key. Add another field (e.g. SequentialNo), and set this field indexed uniguely. The insert query must be something like
INSERT Into YourTable (SequentialNo, Field1, Field2, ...)
SELECT Expr1 AS SequentialNo, Field1, Field2, ...
FROM TourSourceTable ...

NB! As you see, the query doesn't contain the autonumeric ID (you can't force autonumeric values)!

[Bulzie]

Does it matter if the receipt numbers are not sequential? It is still unique. Also is this a single application on one PC or is it say on multiple PCs with data on network so producing receipts from different locations?

[Raveen1609]

Thanks for your response. Yes it matters the receipt number should be sequential to avoid misuse. Same single application, with split backend tables, used in 2 PCs in different locations

[Raveen1609]

For unknown reason, when I save the table after creating a sequnetialID field, and index and set to no duplicates, it is not accepted.

[CJ_London]

and set to no duplicates, it is not accepted.

probably because you already have duplicates