AutoNumber

[vertue]

When I delete an entry I would like the number to be used again. ie
If I delet the entry number 17 I would like the next new entry to be given that number instead of the next one which could (lets say) be number 154. Is there a way of doing this automatiacally so that all numbers up to the new one are used.

TIA

 

[WayneRyan]

vertue,

That's not what Autonumbers are for. Access provides them to
give each record a unique key. You should not care what they
are, most forms won't display them and the users don't care
about them.

If you want a consecutive numbering system, that can be done
but you'll have to write some code. If you want to "fill in"
the holes (from deletions), then that will be even more code.

Wayne

 

[vertue]

Wayne

Thanks for your promt reply. I was going to use the Autonumber as CustomerID numbers in my make believe database. I guess I will have to code them.

One more question. If I delete a record are all the bytes from that record gone from the database or is the space still there but un-used? I hope you know what I mean.

Trev

 

[WayneRyan]

vertue,

As you work with your database, it will grow as Access claims
disk space to run queries, make recordsets, and other things.
Every once in a while, you have to:

Tools --> Database Utilities --> Compace database

Access will then copy the objects in your database to db1.mdb,
then delete your currentdb, then rename db1.mdb to your database.

Also, if you want to look at maintaining your own record numbers
you can search on this forum, or use Access Help, for DLookUp,
DMax and DCount functions.

Wayne

 

[vertue]

Thanks Wayne

Thanks

 

[samehkh]

This code helps solving the serial number problem in a multi user environment, where several users are attempting to take the same number.
It depends on keeping the number in a separate table and then opening it one at a time with the "deny read" permisson.
One User will be allowed in while others will be locked out until he is done.




Public Function GetInvoiceNumber As Long


On Error GoTo Error_Handler

Dim LockCount As Integer, wait As Long, waitx As Long
Dim wrk As Workspace, mydb As DataBase, numTable As Recordset

DoCmd.Hourglass True

'Open the table called Autonumber in deny read mode.
Set wrk = DBEngine.Workspaces(0)
Set mydb = wrk.OpenDatabase("your database here")
Set numTable = mydb.OpenRecordset("AutoNumber", dbOpenTable, dbDenyRead)
LockCount = 0

'Read value and increment.
numTable.MoveFirst
numTable.Edit
GetInvoiceNumber = numTable!InvoiceNo
numTable!InvoiceNo = numTable!InvoiceNo + 1
numTable.Update
numTable.Close
mydb.Close

GetInvoiceNumber_Exit:
DoCmd.Hourglass False
Exit Function

Error_Handler:

Select Case Err
'list of errors that might occur due to locking issues
Case 3006, 3008, 3009, 3027, 3045, 3046, 3158, 3164, 3186, _
3187, 3188, 3189, 3197, 3202, 3211, 3212, 3218, 3260, 3261, 3262
LockCount = LockCount + 1
If LockCount > 10 Then
GetInvoiceNumber = -1
Resume GetInvoiceNumber_Exit
Else
Pause (100000) ' Pause is a wait loop defined somewhere else
Resume
End If

Case Else
'another kind of error
MsgBox "Error" & Err.number & Err.Description, vbCritical + vbOKOnly
GetInvoiceNumber = -1
Resume GetInvoiceNumber_Exit
End Select
End Function

 

[Pat Hartman]

Reusing customerID's is a really bad idea. Won't there be confusion with any paper records if a new customer has the ID of an old customer?

 

[vertue]

Re Using

If I start to make an entry and then decide I did not want it the data base moves to the next ID. So I would want to go back and use the previous ID. ie if I started to enter on #16 then decided I did not want that entry the next entry would be #17 and therefore #16 was not used. This goes back to a question I asked earlier - how to stop the data base adding entries automatically without me pushing a button to add the entry.

 

[vertue]

Explain

You are all so good here at answering questions, that I should explain why I ask questions that seem not to matter.

I am learning Access (as you know) and as I do something I come across a "what if" question. It is not really important whether the question is relavent, as I know most things can be done more than one way. I just feel that access is controling me, instead of the otherway around. The only way I can have control of the database or at least feel I have complete control is to answer these seemingly irrelavent questions. Sometimes these questions answer or lead to more knowledge or, put me onto another problem. Nonetheless, it all helps, so thanks all.

In the case of the question we are on now. It does not really matter if the ID should or should not be used again. It is more important that I have the knowledge to control the database that way if I wished.

Trev

 

[WayneRyan]

Trev,

One of Access's strengths is that you can very rapidly develop
a software application. Once you nail down your table definitions
it is relatively easy to develop your forms and reports.

Access does many thing to control your access to your app's
data. The majority of these are good things. You just learn
how Access "thinks" and know when to deviate from its ground
rules.

If you want "total control" do a search here for "unbound forms"
and you will see how much more work that entails when building
an application.

Just some thoughts ...

Wayne

 

[vertue]

Wayne

Thanks Wayne. You have now put me on the right road.

 

[Pat Hartman]

Jet assigns the next autonumber as soon as the first character is typed into the form. It does not actually add a record if you cancel the update. It does not however reuse the already assigned autonumber. This is because Access is intended to be used in a multi-user environment and there is no practical way to ensure uniqueness in autonumbers unless they are committed as soon as they are assigned. Once an autonumber has been bypassed, Jet would have no way of knowing why it was bypassed and whether the number could be reassigned. You are certainly free to reproduce this process yourself but it won't be easy. You have no way of knowing why any particular id is missing. Is it missing because someone started a new record and cancelled it? Is it missing because someone deleted a record? Is it missing because someone started to run an append query and cancelled it? And, if you don't know why an id is missing it is not safe to reuse it.

You are all so good here at answering questions, that I should explain why I ask questions that seem not to matter.

- we ask why you are doing something that is generally considered poor practice so that we can keep you from making serious mistakes if possible. Your original question exibited a clear lack of understanding of what autonumbers are.

If I delete a record are all the bytes from that record gone from the database or is the space still there but un-used

- The space occupied by deleted items of any kind is not reclaimed until the database is compacted. Access will continue to acquire new space causing the db to grow even if there is technically free space within the db.

samehkh's solution does not solve your problem.

 

[samehkh]

Sometimes business rules dont allow serial numbers to be dropped, like in the case I posted (Invoice numbers)