Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2013
    Posts
    4

    Unanswered: resetting starting number in autonumber field

    I have a table that is used for data entry with a form.
    its a detail listing out the parts that make the whole, so multiple rows. I need each row given a numeric value (1..2..3..etc) now, this table is represent the details of one summary item so the auto numeric field needs to revert back to 1 after entry of the details are finished and the summary item and its details are posted to the final tables...

    is there a command that will reset the autonumber counter back to 1?
    thanks
    Kevin

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Yes there is but you're not supposed to use it that way. An AutoNumber field is supposed to contain a UNIQUE value for the whole table. If you feel adventurous you can use (the constant c_SQL in the following VBA procedure contains the SQL DDL expression):
    Code:
    Sub ResetCounter()
    '
    ' Reset the Autonumber column named SysCounter of the table
    ' named Table1 to an initial value of 1 with an increment of 1.
    '
        Const c_SQL As String = "ALTER TABLE Table1 ALTER COLUMN SysCounter COUNTER(1,1);"
        CurrentDb.Execute c_SQL, dbFailOnError
        
    End Sub
    However, you're heading to big troubles, fast!

    It would be smarter to keep the AutoNumber column as it is and use a query including a COUNT() function with a sub-query to emulate a ROW_COUNT inside a subset. However, it's difficult to provide any example without knowing the name of the table and the names and types of its columns.
    Have a nice day!

  3. #3
    Join Date
    Jan 2013
    Posts
    4
    yes, i understand that the auto number is to represent a unique record, and it will. well, in the temp table that is.

    I have two tables. one is a header, second is the detail.
    its sort of a shopping list for different people.

    Person A is shopping at store A on X date...this holds the header
    Person A buys 1 shirt for D$ - item #1. 2 pairs of shoes for D$ -item #2, etc
    When the detail list is finished, the user then processes the data and the detail temp data is pushed over to a permanent detail table where the item # is no longer a unique id, the combination of two fields from the header table and the item # from the temp detail tbl is the key that cant exist more than once.

    So I need the item # in the detail table to always start at 1 when we begin a new person and date. I have been instructed that the data entry person should not need to enter in the item # in the detail list, that it should be done for them. I agreed, I just didnt know how to reset the auto number so it does.

    i did a work around but wasnt happy with it.
    I will try out your suggestion, i hope it works.
    I will let you know either way.

    thanks

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!

    Please post information about the structures of the tables (name, columns names and data types) if you finally decide to solve the problem using a more canonical way (sub-query to emulate a ROW_COUNT inside a subset).
    Have a nice day!

  5. #5
    Join Date
    Jan 2013
    Posts
    4
    ok, so its not working because...
    the table i want to reset is in a subform in the form that i am running the code from, so the table is still open and the VB runtime error is: "The database engine could not lock table 'tbl_temp_tripticket_lineitem' because it is already in use by another person or process.

    oh, i got an idea....

  6. #6
    Join Date
    Jan 2013
    Posts
    4
    Ok, its not working. i cant seem to release the table for the code to work.
    then i put the code on another form and i am getting a "syntax error in field definition on the line of code:
    CurrentDb.Execute c_SQL, dbFailOnError

    Im not sure what I am going to do.

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    All I can say is : "I told you so" !
    Have a nice day!

Posting Permissions

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