Page 1 of 3 123 LastLast
Results 1 to 15 of 31
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unhappy Unanswered: create a duplicate record with new primary key

    I have read many threads on this but still don't know what to do. Any help is greatly appreciated it.

    I am running Access 2003. I use the tools box to create a duplicate command button.

    (note: Some records are very similar and I think it may save users' time to allow them to create a new record from duplicating an old record. Then just make a few changes in the new record.)

    I pick an existing record and then click the Duplicate button. I got the following error message

    The command or action 'PasteAppend' isn't available now.

    The Access 2003 generated the following code for the Duplicate button.

    Private Sub cmdDuplicateRecord_Click()
    On Error GoTo Err_cmdDuplicateRecord_Click


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70

    'Paste Append

    Exit_cmdDuplicateRecord_Click:
    Exit Sub

    Err_cmdDuplicateRecord_Click:
    MsgBox Err.Description
    Resume Exit_cmdDuplicateRecord_Click

    End Sub

    Thank you

  2. #2
    Join Date
    Oct 2002
    Location
    Baghdad, Iraq
    Posts
    697
    Can you attach an example of what you've done so far?

  3. #3
    Join Date
    Feb 2007
    Posts
    348
    oh scary.
    I don't normally usually use the wizards so I gave this a test drive. Two things.

    First, by your error I would suggest that you possibly removed the apostrophe before the words "Paste Append". Make sure it is there. You can even try removing the whole "'Paste Append" bit, it's a comment.
    When I first played with it, I did not use a bound form and it seems to have generated an error "The command or action 'Copy' is not available right now." So make sure you have a bound form.

  4. #4
    Join Date
    Sep 2007
    Posts
    148
    Thank you both. I find the following example in the forums (I cannot find the original thread)

    http://allenbrowne.com/ser-57.html

    I make some modification and it is working now. thank you for all the replies. Thanks

  5. #5
    Join Date
    Sep 2007
    Posts
    148
    Here is my code for the Duplicate Button. It is working fine until when I close the form without wanting to save the Duplicate. That is, when I choose a form which I want to make a duplicate, I click the duplicate button. It will create a duplicate form with new primary key and everything. It appears as a save record. Somehow with the code, I can edit the duplicate ONE time and do a new save and that is good. I can also delete the duplicate before it was save the first time. Now I am thinking maybe a user just want to leave without saving the duplicated form. That is not having a new form all to gether. Can anyone help me to modified the following code to make it a new editing record so that when I click the close button, the database won't save this newly duplicated record.

    *********************************************

    Private Sub cmdDuplicateRecord_Click()

    'A Duplicate Record is already a save record.

    On Error GoTo Exit_Handler
    'Purpose: Duplicate the main form record

    'Save and edits first
    If Me.Dirty Then
    Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
    MsgBox "Select the record to duplicate."
    Else
    'Duplicate the main record: add to form's clone.
    With Me.RecordsetClone
    .AddNew
    !Title = Me.txtTitle
    !Description = Me.txtDescription
    !Outcome = Me.txtOutcome
    !Submitter = Me.cboSubmitter
    !OtherRequestor = Me.cboOtherRequestor
    !Requestor = Me.cboRequestor
    !LocationID = Me.cboLocationID
    !CategoryID = Me.cboCategoryID
    !DateTimeStart = Now()
    !DateTimeEnd = Now()

    'etc for other fields.

    .Update

    'Display the new duplicate.
    Me.Bookmark = .LastModified

    End With

    'Unlock the following field for edit. Activate both the Undo, Save and Delete buttons

    Me.txtTitle.Locked = False
    Me.txtDescription.Locked = False
    Me.txtOutcome.Locked = False
    Me.txtStartDate.Locked = False
    Me.txtEndDate.Locked = False
    Me.cboStartTime.Locked = False
    Me.cboEndTime.Locked = False
    Me.cboOtherRequestor.Locked = False
    Me.cboRequestor.Locked = False
    Me.cboLocationID.Locked = False
    Me.cboCategoryID.Locked = False
    Me.cboSubmitter.Locked = False

    Me.txtStartDate.Enabled = True
    Me.txtEndDate.Enabled = True
    Me.cboStartTime.Enabled = True
    Me.cboEndTime.Enabled = True
    Me.cmdUndoRecord.Enabled = True
    Me.cmdDeleteRecord.Enabled = True
    blnConfirmUndo = True
    End If

    Exit_Handler:
    Exit Sub


    Me.cmdSaveRecord.Enabled = True
    Me.cmdUndoRecord.Enabled = True
    Me.cmdDeleteRecord.Enabled = True



    End Sub
    Last edited by sweetmail; 11-16-07 at 10:54.

  6. #6
    Join Date
    Jan 2014
    Posts
    4

    Duplicate button will not work proper

    I have a program for creating new "WIZ" tickets or (orders). I take an order for 10 loads. I create the first one and want to duplicate it 9 more times. If I fill in all the fields it will duplicate but if I leave out the driver info it throws an paste err.

    Here's what I have wrote.

    Private Sub Duplicate_Click()

    On Error GoTo Duplicate_Click_Err

    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdCopy
    DoCmd.GoToRecord , "", acNewRec
    DoCmd.RunCommand acCmdSelectRecord
    DoCmd.RunCommand acCmdPaste

    WizDriverID = ""
    WizDriverFirst = ""
    WizDriverLast = ""
    WizTruck = ""
    WizTrailer = ""
    WizShpTime = ""
    WizReTime = ""
    Me.WizShpTime.SetFocus

    SaveWizTicket.ForeColor = vbRed
    SaveWizTicket.BackColor = RGB(252, 204, 207)
    WizWayneLoadNumber.ForeColor = vbRed
    Save.ForeColor = vbRed
    Save.BackColor = RGB(252, 204, 207)
    InfoMessage.BackColor = RGB(252, 204, 207)
    InfoMessage.Caption = "You have modified this record. " & _
    " Click Save or the Esc key to Exit."
    InfoMessage2.BackColor = RGB(252, 204, 207)
    InfoMessage2.Caption = "Enter New Wayne Number." & _
    " Click Save or the Esc key to Exit."

    Duplicate_Click_Exit:
    Exit Sub

    Duplicate_Click_Err:
    MsgBox Error$
    Resume Duplicate_Click_Exit



    End Sub

  7. #7
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hi

    I am trying to create a duplicate record and its related records in another table. I have a button on a split form showing the main record and a data sheet view of the related (software list) records.

    When I run the code I get == number of query values and destination fields are not the same == and DEBUG takes me to the DB Engine (0) line in the code.

    So I get the basis of the problem but I need help with where exactly to fix it. Any pointers very welcome!

    Private Sub Command603_Click()
    'Purpose: Duplicate the main form record and related records in the subform.
    Dim strSql As String 'SQL statement.
    Dim Hardware_ID As Long 'Primary key value of the new record.

    'Save any edits first
    If Me.Dirty Then
    Me.Dirty = False
    End If

    'Make sure there is a record to duplicate.
    If Me.NewRecord Then
    MsgBox "Select the record to duplicate."
    Else
    'Duplicate the main record: add to form's clone.
    With Me.RecordsetClone
    .AddNew

    !VM = Me.VM
    !CPU = Me.CPU
    !RAM = Me.RAM
    !Stream = Me.Stream
    'etc for other fields.
    .Update

    'Save the primary key value, to use as the foreign key for the related records.
    .Bookmark = .LastModified
    Hardware_ID = !Hardware_ID

    'Duplicate the related records: append query.
    If Me.[dsSoftwareInventory].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [Software] ( Software_Name, Version, License_Required, License_Type, Hardware_ID, Software_Type, Memo ) " & _
    "SELECT " & Hardware_ID & " As NewHardware_ID, Software_Name, Version, License_Required, License_Type, Hardware_ID, Software_Type, Memo " & _
    "FROM [dsSoftwareInventory] WHERE Software_ID = " & Me.ID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError
    Else
    MsgBox "Main record duplicated, but there were no related records."
    End If

    'Display the new duplicate.
    Me.Bookmark = .LastModified
    End With
    End If

    Exit_Handler:
    Exit Sub

    Err_Handler:
    MsgBox "Error " & Err.Number & " - " & Err.Description, , "cmdDupe_Click"
    Resume Exit_Handler

    End Sub

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    What do you think
    the number of query values and the destination is not the same
    ..or whatever the error message is.
    Have you checked that the number of columns returned from the select is the same as the number of columns you are referring to in the insert
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    Hey Mr Healdem - glad to see you are still on this excellent forum!

    Yes completely get that the field names need to tally. Where I am not sure if specfically around the hardware_id creation

    'Duplicate the related records: append query.
    If Me.[dsSoftwareInventory].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [Software] ( Software_Name, Version, License_Required, License_Type, Software_Type, Memo ) " & _
    "SELECT " & Hardware_ID & " As NewHardware_ID, Software_Name, Version, License_Required, License_Type, Software_Type, Memo " & _
    "FROM [Software] WHERE Software_ID = " & Me.ID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError

    strSql is saying create insert into these fields which is fine
    FROM is saying, take the values from the records you can see fo rthe current record in the form
    but the SELECT - ...hmmm. What that needs to say is use the HARDWARE_ID we created above, as that is the foreign key for the software items in the software table.

    Is this set up to do that?

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    You have 7 columns in your INSERT fragement, and 8 columns in your (sub)SELECT hence the error message
    field names DO NOT have to tally (read the same name), BUT the number of columns on both sides MUST tally, as MUST the order, as MUST the datatypes. The ONLY exception this is if you don't specify any columns in the INSERT side as the SQL engine will assume that all columns will be populated in the INSERT and so all columns MUST be retrieved from the select. you cna specify literals as part of that select if you so wish

    eg:-
    Code:
    INSERT INTO MyTable (UserID, ComputerID, MyStatus) SELECT User,computer,"DIS" from aTable where User like '%Raddle%'
    If your problem is getting a new unique hardware ID, then don't do this as an insert into. is hardware id an autonumber column?
    if so don't specify it in the select.
    another approach is to save the previous value sin local variabels and set the text value of each control = those values when you say you want to copy the old value.
    right now this topic is too vague.

    can you copy values from a previous entry into another: yes

    how you do it, well thats up to your preferred approach, your table design
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    thanks sooo much for that. so right now I have lined up the field names (thanks for the hint that they must be in matching order).

    'Duplicate the related records: append query.
    If Me.[dsSoftwareInventory].Form.RecordsetClone.RecordCount > 0 Then
    strSql = "INSERT INTO [Software] ( Software_Name, Version, License_Required, License_Type, Hardware_ID ) " & _
    "SELECT " & Hardware_ID & " As NewHardware_ID, Software_Name, Version, License_Required, License_Type " & _
    "FROM [Software] WHERE Software_ID = " & Me.ID & ";"
    DBEngine(0)(0).Execute strSql, dbFailOnError

    This code will now create a new Hardware entry. Yes the hardware_id primary key is autonumber. That bit is working.

    However for the current record, which has say 6 items of software on it, these are NOT being created in the software table. I get a new hadware record, and ONE software record with the new Hardware_Id inserted into correct foreign key field.

    Q: the AsNewHardware_ID is not in right order position (?) the Hardware_ID in the INSERT is last. Is this a problem?

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so repeat the process to insert entries into the software table, the only wrinkle is that you need to discover what the new hardware ID is
    https://www.google.co.uk/webhp?sourc...utonumber%20id
    ...or a slightly cruder approach, assuming only one person is entering rows at anyone time is to use the DMAX function AFTER the hardware insert to find the current highest harwadreID ansd specify that in your insert for the software
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    I have the new hardware id. that piece is working no problem.

    How do I repeat the process?

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The smart aleck answer is I don't know, because I don't know your table design

    but Im guessing you want to run a similar 'insert into' query for the software table, sepcifying the old hardware id for the where clause AND the new hardware to act as the FK in the software table, the bit that identifies what software is on what computer.
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Dec 2010
    Posts
    134
    Provided Answers: 1
    hehe ok ...this code advertises that it does this, that is what I originally searched for. Well when I fix it I will shout back .. thanks for your input!

Posting Permissions

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