Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2013
    Posts
    5

    Unanswered: Creating duplicate values and overwriting existing

    First of all, Hi to everyone, my first post here as so far I could find solution to every problem without specifically asking...so far. Now, to the problem:

    I have a fairly large database with couple of tables and forms. On of the tables is a standard table containing specific info on let say customers - ID, name, organization,... Since it is a start of a new year customer info should be reviewed and appropriate changes made. The problem is, I have removed indexing and allowed duplicate values for all fields in the table but now everytime I edit a customer info and save, acces will create duplicate value (it will create the new one but retain the old one). If I set customer ID as a primary key access won't even allow editing of old records - to be more precise, I can edit it but can't save changes (the changes you requested to the table error). I'm stuck, can anyone please offer some advice??

    Thanks and hny to everyone!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    go through the exisiting data and kill the duplicates
    use a select query that counts rows that are grouped by the same customer ID, delete duplicates such that only one row is left

    then re set the primary key
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2013
    Posts
    5
    I've thought about that. But the problem is, if I set a customer ID as a primary key, I can't edit existing entries any more. To be more exact, I can edit app. fields in formso but access won't let me save them (as this would create duplicate items, which are not allowed). Access is cerating duplicates instead of overwriting.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why can you not edit (and save) existing entities.
    its something you have done with in your forms
    for a relational db to work there MUST be a mechanism which uniquely identifies a row. whatever that mechanism is its enforced as a primary KEY

    from what you have outline so far the natural candidate for this primary key is the customer id. so use it

    if you have been tinkering around with things then its quite possible you have duplicate values so before you can redefine the primary key you MUST delete all duplicates, then re apply the primary key.

    as to why Access is creating new records instead of saving edits, then look back at what you have done. its something you have fecked around with. its not a normal Access behaviour
    if the worst comes to the worst then create a new form, buit ONLY after you have deleted the duplicates AND re imposed the primary key
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2013
    Posts
    5
    You are probably right...it seems that form is the reason that access won't overwrite entries and it is instead creating duplicates. What is even more strange, this happens in 1 out of 20 cases... I really don't understand why and how and I'm an access noob so I'm not sure if I'll be able to recreate form so this will work...

    If I erase duplicates, reassign primary key access won't let me save anything other than new entries...go figure

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    as said before
    its something you have done with your form
    its the form is bolluxed and you don't know what you did, and or you don't know what code is behind the form then I'd ditch it and create a new form.

    one thing to bear in mind is that Access forms are quite a tricky piece of development code, there is quite a lot happening behind the scenes and sometimes those behind the scenes activities can be significantly compromised by retrospective changes in table design.

    you could try do a compact and repair (on a backup and see if that clears the fault (it could be a corruption within the DB)
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2013
    Posts
    5
    Hm...will try this, thanks! The problem is, I can't even properly test it, since this problem occurs only sometimes - it is not easily repeatable and thus its very hard to test if doing something actually helped solving it...


    Oh and thanks for your help!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    is it a specific machine specific user
    I always find it useful to store stuff like the computer and the user who created the row, or last edited the row
    ..to get those use the API calls, google:- dev ashish api

    it may not be a form problem it could be a user problem

    irresepective of that enforce a primary key
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jan 2013
    Posts
    5
    Hm... the database is split - front end is on local machines while back end is on server. The problem occurs occasionaly on each and every machine (3), no matter who is working with the database, it happens to regular users with access and to network administrators...

Posting Permissions

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