Restart Auto-Numbering

[Hey Lucy]

My ID field is an auto-assigned number. Is there any way to restart the auto-numbering at number 1?

 

[mdlueck]

Empty the table, compact the database, that usually resets the Auto-Number capability.

Instructions how to clean up an Access DB may be found here:

NT Command Script and Documented Steps to Decompile / Compact / Compile an Access DB
http://www.access-programmers.co.uk...to_Decompile_/_Compact_/_Compile_an_Access_DB

 

[John Big Booty]

The numbers generated by the AutoNumber function should be relied upon to produce nothing other than a unique Key., and are considered by many to be not fit for Human consumption.

If you require a sequential number, then you should consider implementing your own numbering system based on the DMax() function + 1

The following Code in the Form's On Current Event Should do the trick;

If Me.YourCounterField = 0 Or IsNull(Me.YourCounterField) Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR="Red"]X[/COLOR][/B]) + 1
End If

Note; Replace X with your seed number (the number you wish your series to start at).

 

[Solo712]

The numbers generated by the AutoNumber function should be relied upon to produce nothing other than a unique Key., and are considered by many to be not fit for Human consumption.

If Me.YourCounterField = 0 Or IsNull(Me.YourCounterField) Then
        Me.YourCounterField = Nz(DMax("YourCounterField", "YourTableName"), [B][COLOR=red]X[/COLOR][/B]) + 1
End If

Note; Replace X with your seed number (the number you wish your series to start at).

Thanks for saying that, John. I am puzzled by the Access engineers not dealing with the glaring weaknesses of the AutoNumber facility. In a number of my applications the tables are filtered and I need to combine the autonumber with an ID of another field. (i.e. there would be multiple seeds based on the ID field. Can't do it with Autonumber. When archiving portion of the table, the autonumber needs to be reseeded. Why would not there be a simple VBA function for that ?
Why does one have to compact/decompile the database, empty and re-import the table to do something as trivial as that ? And then of course ther are the mysterious seeding errors. I say, make it the eleventh commandment for develpment in Access : STAY AWAY FROM AUTONUMBERS !

Here is a sample of a general VBA routine (can be used on any table to generate sequence number for a field) which optionally compresses the sequence set and reuses the numbers of deleted records.

'     NextNumber
'     -------------------------------------------------------------------
'     A function to replace AutoNumber sequencing. Sample call:
'     Me!AutoNumberedField = NextNumber ("tblMyTable","AutoNumberedField")
'     where AutoNumberedField is Number/Long
'
'     Optional Boolean argument for NextNumber determines whether the records will
'     be compacted before issuing a new ordinal.
'     So, NextNumber ("tblMyTable","AutoNumberedField", True) will cause the availabe low
'     numbers from deletes to be reissued to existing records before returning the
'     next available number. The original sequence remains unaffected.
'
Public Function NextNumber(tTable As String, fField As String, Optional sSwitch As Boolean) As Long
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset, SQLstr As String
  Dim i As Integer
  Set dbs = CurrentDb
  SQLstr = "SELECT " & fField & " FROM " & tTable & " ORDER BY " & fField
  On Error GoTo NextNumber_Error
  Set rst = dbs.OpenRecordset(SQLstr, dbOpenDynaset)
 
  i = 0
  If Not rst.EOF Then
    ' if no optional switch present or its set to FALSE
    ' return the value of last record
    If IsMissing(sSwitch) Or Not sSwitch Then
      rst.MoveLast
      i = rst.Fields(fField)
    Else
      rst.MoveFirst
      Do While Not rst.EOF
        i = i + 1
        If rst.Fields(fField) <> i Then
          rst.Edit
          rst.Fields(fField) = i  ' the final value of i = number of recs.
          rst.Update
        End If
        rst.MoveNext
      Loop
    End If
  End If
 
  NextNumber = i + 1
 
ExitNextNumber:
  rst.Close
  dbs.Close
  Set rst = Nothing
  Set dbs = Nothing
  Exit Function
 
NextNumber_Error:
  MsgBox "NextNumber Error: " & Err.Number & " " & Err.Description
  NextNumber = 0
  Resume ExitNextNumber
End Function

Someone - I believe it was Galaxiom - wondered why the DMax function was not good enough for me. Two reasons: 1) it does not compact. The compacting function allows a reseeding routine to be run. You may know there are only 430 records but if the Autonumber seed is at 1480 you will have helluva time reseeding to, say 500. Essentially you have to run a check on the field value in each record. After you have compacted, it is easy to write a routine which reseeds to any number, lower or higher than the existing start number. 2) this routine can be easily upgraded (add an extra argument to the function, and a WHERE clause to the SQL string ) to allow multiple seeds/sequences in the field, to serve as a part of a compound unique key in databases which are heavily filtered, eg an accounting system with several general ledgers or a service desk for several customers. Finally, the code is reusable. It can be placed into a module with public procedures in any database and called to be used on any table.

Best,
Jiri

 

[jdraw]

That's an interesting function. Do you use it? The autonumber approach is used in other DBMS also eg Oracle Sequence.
see http://sqlzoo.net/howto/source/z.dir/tip000001/access

How do you deal with foreign keys if you compact and reseed Ids with your function?

 

[Solo712]

That's an interesting function. Do you use it? The autonumber approach is used in other DBMS also eg Oracle Sequence.
see http://sqlzoo.net/howto/source/z.dir/tip000001/access

How do you deal with foreign keys if you compact and reseed Ids with your function?

I don't compact ID's if they are used as foreign keys in referencing relationships. So I have not run into any issues on that score. Naturally, if the IDs were to be used as FKs, the renumbering would be a little more involved process.

But for the moment, I use compacting as a tool in managing bulkier, subform-type of data, typically transactions. Most often I compact the IDs after archiving or purging older data from tables.

Best,
Jiri

 

[Galaxiom]

I am puzzled by the Access engineers not dealing with the glaring weaknesses of the AutoNumber facility.

Because it is not a weakness when used as intended.

I say, make it the eleventh commandment for develpment in Access : STAY AWAY FROM AUTONUMBERS !

I am not a fan of Autonumbers and prefer to write the key even if I don't really need a sequence. I have seen random seed corruptions more than once and it brings data entry to a dead halt which is really bad if the database is important.

I prefer to store the next number in a table. When the number is to be applied to the record a recordset is opened with a lock to prevent other users reading or writing until it is read and updated by the first user. A loop retries ten times with a short wait if the record is locked when it tries.

The number is applied to the record in the Form's BeforeUpdate so no gaps exist in the sequence unless records are deleted.

If a record is deleted then there is no point reusing the number because it clealy has no special meaning and any unique number would be fine.

 

[Solo712]

If a record is deleted then there is no point reusing the number because it clealy has no special meaning and any unique number would be fine.

It may have no special meaning or it may have a special meaning. You can create an auto key and build code into it: first three ID denote a specific client account, next two two digits his bank account, and the next five are a transaction sequence. If you keep all transactions (as you should), eventually you will run out of numbers. You will need to archive and renumber. If you archive by specific periods presenting an ordered, complete set of records, the auditors will not bug you.

Best,
Jiri

 

[Galaxiom]

If the number has any meaning then you wouldn't want to change it on existing records or there would be no point having the number at all.

A transaction number should never change. How would it be traceable if it changed? Five years later an auditor contacts you and asks about a specific invoice number. You are going to tell them that number has been changed so you can't confirm it?

If you are going to run out of numbers then the choice of code is inappropriate. Using so many of the available digits to duplicate information in the record is where you started going wrong.

 

[The_Doc_Man]

I recall a lengthy exchange between myself and the user Banana some years ago on the topic of natural vs. synthetic keys. In general, I have to say that your phobia appears to be caused by asking an autonumber to do something it was not intended to do.

If your PK value has any natural meaning, it is not an autonumber and cannot be generated by the Access autonumber mechanism. Autonumbers can be set to increment or random and they work equally well either way. The whole (and really, the ONLY) point of autonumbers is that they are going to be unique. Not sequentially contiguous. Not monotonically increasing. Just unique. The only thing for which I ever use an autonumber is a transaction ID number that is sent to a consumer after-the-fact. I.e. like a bank transaction number - except mine tend to be shorter.

If you have an a priori pattern of meaning for your PKs, they cannot be autonumbers. Can't be said much plainer than that.

 

[Solo712]

A transaction number should never change. How would it be traceable if it changed?

Seems to me like you want to wax contentious, Galaxiom.

Five years later an auditor contacts you and asks about a specific invoice number. You are going to tell them that number has been changed so you can't confirm it?

I am not talking about invoice numbers or cheque numbers but key subsets denoting transaction sequence. There is no issue of numbered document matching here. It is purely a question of internal order.

If you are going to run out of numbers then the choice of code is inappropriate. Using so many of the available digits to duplicate information in the record is where you started going wrong.

You are talking through your hat, Galaxiom. There is no danger of duplicating information in the table. Record 20130126 does not 'duplicate information' in record 20130001 just because the first four digits denote the year of the transaction. You just don't know what you are talking about. If there is no realistic chance of having more than 99999 current transactions in a table why would it be wrong to fix length of the subkey ?
Because you don't understand the difference between current and historical data ?

Jiri

 

[Galaxiom]

There is no danger of duplicating information in the table. Record 20130126 does not 'duplicate information' in record 20130001 just because the first four digits denote the year of the transaction.

Please read more closely.

Using so many of the available digits to duplicate information in the record is where you started going wrong.

You are denormalizing the record itself by duplicating what is stored in the date field as part of a misguided decision to repeat the information in the keys.

You should already have a date, clientID and Account as fields in the table. What is the point of repeating this in the key?

If there is no realistic chance of having more than 99999 current transactions in a table why would it be wrong to fix length of the subkey ?

You are resorting to clumsy workarounds, shifting records out of the table and reassigning the keys to overcome a design fault that causes you to run out of keys. As I said before, you went wrong when you decided to make the key mean something to a human.

I do hope you use transactions to ensure this multistep process doesn't blow up in your face when one of the queries falls over.

 

[Galaxiom]

why would it be wrong to fix length of the subkey ?

There you reveal your profound misunderstanding of database design.

Normalization dictates that each value holds information that means one thing and one thing only. The concept of a "subkey" does not belong in a properly designed database.

 

[gemma-the-husky]

lucy and solo

you must appreciate the guidance you are getting. a number that is intended as a PK in one table, and consequently an FK in another table is just a number with no special significance - which is why an autonumber is perfectly acceptable. most of the time, users do not even need to see the number. There is ABSOLUTELY NO design problem with the behaviour of autonumbers which leads to gaps.

your system should be able to handle such a setup with no problems. If you find you can't then you have a design issue of some sort, or a misunderstanding of how some aspects of RDBS systems work. The point is that the PK of a table, and therefore the FK in another related table does not HAVE to be meaningful.

If you need a "sequence" or special value, then personally, I would tend to create and store that as an extra field and key in the first table, rather than use it as the PK/FK value.

Some might not bother with the extra field, but it's a matter of taste.

Having a rigid format such as yyyy-xxxxx might limit you to 99999 values per annum. As long as you appreciate this and can live with it, there is no issue. Alternatively, you could split the "special sequence" into 2, and store the year, and sequence value as distinct fields, which makes more sense to be honest.

splitting the "special sequence" into 2, and having a separate year, and sequence value - could still be treated as a 2-field PK, and used as a 2-field FK - but in many cases a 2-field key is more complicated to use and manipulate. Instead, a long integer numeric key gives you an efficient key with 2billion values, and in worth the extra overhead of the extra key.

 

[gemma-the-husky]

Seems to me like you want to wax contentious, Galaxiom.



I am not talking about invoice numbers or cheque numbers but key subsets denoting transaction sequence. There is no issue of numbered document matching here. It is purely a question of internal order.



You are talking through your hat, Galaxiom. There is no danger of duplicating information in the table. Record 20130126 does not 'duplicate information' in record 20130001 just because the first four digits denote the year of the transaction. You just don't know what you are talking about. If there is no realistic chance of having more than 99999 current transactions in a table why would it be wrong to fix length of the subkey ?
Because you don't understand the difference between current and historical data ?

Jiri

sorry. I have to say that Galaxiom is not talking out of his hat.

the very design of your recordid is of itself denormalised. you do not need to store a recordid consisting of a year and recordnumber - you could store those as two separate fields.

now you may choose to regard this as a discrete recordid, as you do - but this of itself does not mandate all the complications you have discsussed about deleting records, and resequencing keys.

all those procedures demonstrate is a non-optimal application design.

and there is 100% absolutely no need to worry about an autonumber sequence that gives you a "next record id" of 1501 as opposed to say 481. The number is completely unimportant.

A dcount or a dsum of a domain will inform you of the number of records or their total. The record ids are just immaterial.

 

[RainLover]

Gemma

I would suggest that you give this Solo712 a wide birth.

He has nothing to contribute to a constructive debate. He lacks experience and the ability to grasp even the most basic of concepts.

I know it is not the common attitude of this Forum to ban users, but if he is going to continue to insult the good people here I will be asking for his termination.

We are all here for various reasons. None of them is to be treated in a degrading manner.