Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2012
    Posts
    22

    Unanswered: Changing where Autonumber begins

    Hi guys,

    I have a table where the auto number needs to begin from 1197 (The must current up to date record) and not 1 is there any way I can change this?
    Also in my tests I have been creating records in tables to make sure they work, however when I create a record now it will start from 19 or 20. Is there any way to reset the Autonumber counter?

    Also, I have a form where it shows the current invoices and I was wondering if there was a way to automatically add days to a date, I have the date of the invoice and then a chase date which is x amount of days from the invoice date and the x is credit days. So is there a way to add a number of days to a date based on another field Edit: This bit was much simpler than I had thought it was just: Date + Number = New Date
    Last edited by Kisageru; 10-23-12 at 05:16.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    There is a way to reset the autonumber seed, but I can't remember what it is and where I saw it. IIRC it can be done under properties of the table via VBA. But as the autonumber value should have no significance outside the system I wasn't especially interested in remebering that. Autonumber is a means of quickly and easily making each row unique, that number has no other meaning, it should not be used for purchase orders, GRN's reciept no's especially where there is another meaning imposed ont he number (eg that numbers should be contiguous). if you need numbers in a specific sequence with no gaps then you will have to roll your own numbering mechanism
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    AutoNumbers exist to provide unique numbers, not serial numbers. If you absolutely must have a controlled serial number for a field, write a function that has easy-to-maintain parameters and set the field type to Long Integer instead.
    (MS SQL provides the means for controlling the start point of auto numbers and for resetting them, but I would still prefer to go down the custom function approach there, too.)

    Check the help file for the DateAdd function with respect to adjusting a date by a number of days.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Sep 2012
    Posts
    22
    Okay thanks guys, I thought it would be easier having it generate the Invoice Number for me but it's only four digits I can do with typing in

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can re-seed a autonumber column using:
    Code:
    Dim strSQL As String
        
        strSQL = "ALTER TABLE <TableName> ALTER COLUMN <ColumnName> COUNTER(<Start>,<Increment>);"    
        CurrentDb.Execute strSQL, dbFailOnError
    Where:
    - <TableName> is the name of the table.
    - <ColumnName> is the name of the column of type Autonumber.
    - <Start> is an Integer that represents the starting value for the counter.
    - <Increment> is an Integer that represents the value that will be added to the counter for each row.

    The warning provided by weejas is very important. You do not re-seed an AutoNumber column in the normal course of a database use.
    Have a nice day!

  6. #6
    Join Date
    Oct 2006
    Posts
    110
    Another option would be to use a number sequence. When you create an invoice it will pull a number from a table, apply it to the record, then update the table. This you would be able to control what number you want to start with, IMO.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    If the invoice number is stored into a table, you can easily compute the new number in sequence with:
    Code:
    lngNewNumber = Nz(DMax("InvoiceNumber", "Tbl_Invoices"), 0) + 1
    Where:
    - lngNewNumber is a Long numeric variable that receives the new invoice number.
    - InvoiceNumber is the name of the column where the invoice numbers are stored.
    - Tbl_Invoices is the name of the table that stores the invoices data.

    The Nz() function is used to cope with an empty table, as Null would be returned by the DMax() function in such a case. That way, 1 (one) will be returned if the table is empty.
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if its a multi user DB, then you probably ought to be seeking a row lock whilst you are getting the new ID AND release it after you've written the new ID
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •