trouble creating duplicate record due to key 1

[frantik]

Hi! - Help!!!

I have a relational database, I would like to include a "Copy Record" button to enable the user to create a duplicate record.

The duplicate record through the wizard will not work because there is a key field.

Also there are related tables including a 1-M rel, that data needs copying from. (all displayed via subforms on the main form)

Is there an easy way to go about this? And can someone explain it to me please!

Thanks

 

[frantik]

Rick you probably think I'm quite mad! - its not an autonumber field - but it is part of the key. I think its due to my SQL that the option field is not copying over!! - Its a long integer and I am obviously using the wrong syntax somewhere!! Do I need to change how I reference it?

Fran

 

[RickSpr]

That explains everything. In the post where you first showed your code to me with all your actual names in it, you had left 'option' out of the SQL statement for copying the child records. I copied your code in order to get the names, so we've been missing one of the key fields ever since.

The first record copied ok because, by leaving option out, we caused Jet to set its value to Null. You don't have the field marked as Required, so that was ok with Jet. Jet created a record with ra_no and a Null option. But then the rest of the records had the same key and couldn't be inserted.

You must have gotten a "would create a duplicate record in a unique index" error--why didn't you tell me?

All you need to do is insert 'option' into the second SQL statement:
DoCmd.RunSQL "INSERT INTO Tb_options " _
& "(ra_no, option, Op_Consequence, OP_Likelihood, cost, uniqueid) " _
& "SELECT '" & NewRA_no & "', option, Op_Consequence, OP_Likelihood, cost, uniqueid " _

I think that's going to finish you up now.

One final comment. We've been doing a lot of 'remote control programming' here. The Tek-Tips management discourages that because they don't want the site to get a reputation as a free programming service; their intent is that it be a place for professionals to exchange ideas and help with difficult problems. So I would ask you now to learn from this code--study it, with the help of the Help file, until you understand in detail how it works. If you do learn something, then this has been more than just me doing your programming for free, and that makes it worth it. It also means that you may have knowledge you can use to help somebody else on a later occasion.

Good luck.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

That explains everything. Since we didn't include 'option', it was set to Null in the inserted records. The first one was inserted ok, but then the rest were duplicate primary keys. Why didn't you tell me you were getting an error message?

Revise the code:

    ' Duplicate the Tb_options records, replacing the ra_no with the new one
    DoCmd.RunSQL "INSERT INTO Tb_options " _
        & "(ra_no, option, Op_Consequence, OP_Likelihood, cost, uniqueid) " _
        & "SELECT '" & NewRA_no & "', option, Op_Consequence, OP_Likelihood, cost, uniqueid " _
        & "FROM Tb_options " _
        & "WHERE ra_no = '" & ra_no & "'"

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[RickSpr]

Sorry about the repost. For some reason my original reply didn't appear for a while, and I thought I had messed up.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[frantik]

Oh my goodness - Have finally got it working - thank you so much - I have learnt so much - not least that my sql is appalling - It never worked with option at all - but when I created a new field of a different name (called test for now!) to do the same as option it worked!! how is that!!

but it works perfectly and I really do appreciate all your help.

you're a star!

thanks

Fran