Results 1 to 12 of 12
  1. #1
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47

    Unanswered: Copy records to same table

    Hi

    not sure if i'm just being stupid, but I need to create a copy of a record in my table (QUOTE), and add a new QuoteID to it. How can I do this without setting up variables for each field and copying to them before adding a new record. Surely there should be a quicker way? I am doing it is VBA.

    Thanks

  2. #2
    Join Date
    May 2003
    Location
    Teas
    Posts
    28

    look at the database attached...

    GOOD LUCK..

    Dianna C.
    Attached Files Attached Files

  3. #3
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47
    Thank a lot for your reply.

    Does anyone have any ideas on doing this using code and without having to set up variables for each field (since fields may change often)

  4. #4
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Originally posted by nstaward
    Thank a lot for your reply.

    Does anyone have any ideas on doing this using code and without having to set up variables for each field (since fields may change often)
    Is the quoteID an Autonumber or PK.

    And I assume that you would like to copy a record from the Quote table back into the quote table but give it a new QuoteID?
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  5. #5
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47
    Thats right, QuoteID is a PK number but not autonumber. I usually do a Dmax(QuoteID) + 1 to get the next QuoteID. And yes I wish to copy back into the same record, just with a new QuoteID.

    Thanks

  6. #6
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    into the same record is not a copy any more - it's just changing the quote ID.

    izy

  7. #7
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47
    sorry, meant back into the same table, not record!

  8. #8
    Join Date
    Sep 2002
    Location
    Land of OZ
    Posts
    173
    Originally posted by nstaward
    sorry, meant back into the same table, not record!
    Thoughtso, however in the land of OZ it is riday night, football is on TV, beer is beckoning.... I will send solution in two days when I get back.

    However to give you a start, you are looking at a Insert Into statement ...

    Be back to you soon ....
    Mona
    ________________________________
    Life is too short to be sane or sensible. Weird people rule and normal people suck

  9. #9
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    here's a nasty workaround.

    make a select query that finds the record you want to copy AND dmax

    make an append query that feeds on your select query - use dmax+1 to feed the QuoteID

    ...and what about two simultaneous copy requests?????? probably you should think about locking the table while all this is in progress. autonumbers are so much sweeter.

    izy

  10. #10
    Join Date
    Jul 2003
    Posts
    38
    Maybe it is easier with using two recordset variables rather than trying to do two SQL-operations on the table:

    private sub CopyRS(quoteID as int)
    dim src, dest as recordset
    dim i as integer

    set src=CurrentDB.OpenRecordset("Select * from TABLEXY where QUOTEID='" & quoteID & "';", dbopendynaset)

    set dest=CurrentDB.OpenRecordset("Select * from TABLEXY;", dbopendynaset)

    dest.addnew
    for i=0 to src.fields.count - 1
    dest.fields(i)=src.fields(i)
    next i

    dest.fields(QUOTEID)=max+1
    dest.update

    end sub

    didn't try it, so if you find errors in the code, it wouldn't be surprising...
    also you need to check for 'if not src is nothing ...'

    hugo

  11. #11
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    SubHugo: that is THE way to go!
    kicks my workaround into the dust - thanks, i've filed your code.


    izy

  12. #12
    Join Date
    Aug 2002
    Location
    Cambridge, England
    Posts
    47
    Great idea, Worked really well thanks!!!!

Posting Permissions

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