Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2004
    Posts
    4

    Unanswered: Autonumber assigns duplicated number..

    Hi, I am really having trouble with client database.
    The table-A has autonumber field, and all of sudden, autonumber
    won't function anymore. It gives the duplicated value, and show the
    error below when I try to type something in that table.

    "The changes you requested to the table were not successful because they would create dupliated values in the index, primary key, or relationship.

    -------------------------------------
    Total records in the TableA has 1017 records.
    ID fields type = autonumber
    Some reason, it starts from 761 (which is already being in use)

    Windows XP
    Access 2003
    ----------------------------------------

    I did "compact and repair database", but won't fix it.
    Can anybody know how to fix autonumber or conrol ?

    Things is, this field "ID" is used as client ID number and cannot reassign it.
    And I do not want to change field type from AutoNumber to Number(longint)
    It will need change the whole logic.

    please help me out.

    Thank you very much

    Yoshi

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    You mentioned this is a "client" database. Are you running a split db?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Oct 2004
    Posts
    4
    i am sorry. The DB(nicrosoft Access) is located at my customer's server.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Autonumber

    You can try (via VBA), opening the recordset and creating a new record immediately, and then open the form based on that autonumber field which was newly created. This way the autonumber field is created quickly and the user is in an "edit" mode on the form (you'll need to put in some values in the required fields using this method).

    I'm guessing that the user having the form open and the autonumber field being created on the form at some point might be causing some problem with other users on the same form.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Oct 2004
    Posts
    4
    >I'm guessing that the user having the form open and the autonumber field >being created on the form at some point might be causing some problem >with other users on the same form.[/QUOTE]

    Thank you for replying. Your guess is right. It looks like the form directoly
    attaches to that table. And I can imagine easily if more than one users
    open the form at the same time.

    Also I was able to fix AutoNumber Problems by
    1) runnning make-table-query (create new table)
    (at this point the new table had the same prolblem.
    2) deleting all data in the new table.
    3) Appending all data into new table from broken-table

    This way, i was able to copy Autonumbers again.
    AND Autonumber starts functioning.

    Thank you

    Yoshi

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Autonumber

    That seems like the long way to do it (doing a make-table). Personally, on the click event of the button that opens the form, I'd add in some vba code to create the record (after the rs.update, set a variant (MyNewID = rs.IDField) to the autonumber field), close the recordset (rs.close, set rs = nothing), then open the form with a docmd.openform MyForm,, "Where ID = " & MyNewID & "".
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Aug 2012
    Posts
    1

    Great to meet all of you here on www.dbforums.com.

    I have been a lurker here at dBforums - Database Support Community for two years, admiring all the amazing information on this website. I chose not to join because I was still along the way of educating myself in this area of expertise. I have now decided that despite the fact that I'm not exactly as effective as I'd like to be, my level of excitement and near-obsession has pushed me to be a subscriber. Good to meet everyone and if you guys want to join my new forum im at House Cleaning Henderson Nv thanks again for your warm welcomes!

Posting Permissions

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