I want to control and automate my number sequence in Access 2010

[izamac]

I do not want to use AutoNumber to generate my Stock Control Number. I need to somehow automatically generate a number so that when I enter data and then make an error it does not mess up the numbering system like AutoNumber does.
I am a novice who is will to try to do what it takes to get this going properly. I cant find anything online relating to Access 2010

[ranman256]

AutoNumber CANT mess up. Tho if you want some specific number, please advise.
what format?

[ItsMe]

Are you looking for an alternative to AutoNumber? The hard part is making sure you do not edit an existing record's sequential number. You have to find a place for your code where it will fire whenever a new record is created. Maybe something like this.

If IsNull(Me.ID.Value) And Me.NewRecord = True Then
    Dim lngUnique As Long
    
    lngUnique = DMax("ID", "Table1")
    lngUnique = lngUnique + 1
    
    Me.ID.Value = lngUnique
    
        If Me.Dirty Then
            Me.Dirty = False
        End If
End If

[rpeare]

I think you want a hybrid of itsme's suggestion and an autonumber.

Autonumber can not be overwritten or messed up, that's what you want to avoid any possibility of having.

So if you had a table where, on the third record you had entered something that was wrong you would have something like

tblTest
PK  PartNumber  PartDesc
1   1           Part Number 1
2   2           Part Number 2
4   3           Part Number 3

This way you are maintaining the integrity of a PK (something that you know will not be changed over time).