How to Reset AutoNumber

[shinyo21]

Anyone know how to reset the autonumber for access ? Cox It keep running the number and I just seem can't make it back.

Also if there is some records which mistaken keyin I deleted it keep go for next new number. It would be a waste for that number.

 

[RuralGuy]

Here's a link that answers one of your questions. Set AutoNumbers to start from ...

Understand that the autonumber should *never* be seen by the user and as such *you* should only care that it is unique (which Access guarantees). We have lots of numbers so wasting a few should not be a problem. I suspect you are using the AutoNumber field for a purpose for which it was never intended.

 

[shinyo21]

Thanks for ur info, but that is only set it to number 1. Do u have any way to set it back to specify number we want it ? The code that is regarding one ? Am I need to use VB to append it ?

 

[selenau837]

Below is a Function that is my database to manually incremint the autonumber.

My autonumbers are in a table that is incremented and placed in the appropriatte table.

You will need an auto number table for each table you have. IE, if you have 4 tables that you want to use this function for, you will need 4 auto number tables.

HTH.

PS, I didn't write the code, it came with my database I took over.

Public Function IncrementAutoNumber(TableName As String) As Long

    Dim rst As New ADODB.Recordset
        
    With rst
        .ActiveConnection = CurrentProject.Connection
        .LockType = adLockPessimistic
        .CursorType = adOpenKeyset
        .Open "Select * From " & TableName
        .Fields("intAutoNumber").value = .Fields("intAutoNumber").value + 1
        .update
        IncrementAutoNumber = .Fields("intAutoNumber").value
        .Close
    End With
    
    Set rst = Nothing

End Function

 

[RuralGuy]

The link I supplied shows you how to set the AutoNumber to anything you want.

 

[selenau837]

The link I supplied shows you how to set the AutoNumber to anything you want.

Yes, the reason my database now has this function is becuase it kept corrupting and the autonumber, was the reason. So it was removed and we now use this.

 

[shinyo21]

Sorry ... I still dun understand, In the microsoft access where can I include or get this code to be. There is no coding can be edit or see.

 

[meanster99]

Click this link for another way
http://forums.devarticles.com/microsoft-access-development-49/how-to-make-autonumber-to-start-with-value-11658.html

or this one:

http://www.mvps.org/access/tables/tbl0005.htm

 

[shinyo21]

Actually it really can add any specify number there as I wanted but ...
example I append 15 number to the autonumber there but after taht I keyin the next record, the continues number is not start from 16. It start from what it remember number. Actually is there anyway I know where access store this autonumber in the database ?