Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2004
    Posts
    6

    Smile Unanswered: Restart AutoNumber based on records in a field

    Can anyone tell me how or if there is code somewhere to number records in a field, but restart the number when a new value is found.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by vrand964
    Can anyone tell me how or if there is code somewhere to number records in a field, but restart the number when a new value is found.
    You can't restart/reset an autonumber column except for a compact & repair on an empty table with an autonumber column. The other way is to copy the structure to another tablename and delete the old table and rename the new one ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Mar 2004
    Posts
    6
    Is there a way I can number items 1 to 25 and restart the count each time the value in the field changes and/or count hits 25.

    Thanks

  4. #4
    Join Date
    Apr 2004
    Posts
    130
    Have you considered pre-populating the table with the number sequence you want (in a non-autonumber field) and appending another 25 (blank) rows (or more) when you reach a certain count? Not pretty, but it would work.
    Winston

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Quote Originally Posted by vrand964
    Is there a way I can number items 1 to 25 and restart the count each time the value in the field changes and/or count hits 25.

    Thanks
    You could put a unbound text box on your form with the data source being

    =Count([Whicheverfieldname]) We will call the box MyCounter

    Pick a field that is guaraneteed to always have an entry.

    Now add a field to your table to take your count number, let's call it NumberField.

    Now place a macro or code on the Got Focus event for a field that will always get an entry for a new record and the macro or code sets the value of NumberField to be [MyCounter]+1

    You now out a couple of conditions on your macro or code.

    I will call the field that has the macor of code on Got Focus Surname

    The maco or code only runs if the Surname is null.

    If you want to one step further you can three things

    A copy of your table and delete all the copies records

    Am appned query that will append all the records from your table to the copy of your table and a delete query that will delete all the records in your main table.

    You can run these two queries from a macro or code (make doubly sure the append query runs first!!!). You can place a button or label on your form which you make invisible but you have a macro or code, sat to run On Current when the MyCount box =25. The macro or code to run the two queries can be OnClick of the label or button.

    To the macro or code that runs the queries you also add to close and reopen your form.

    Have fun

    Mike

Posting Permissions

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