copy record as a new record

[bluemoon9]

Hi,
I am trying to write a code to copy the current record as a new record (basicly, I would like to duplicate the current record). but then when I click on the command button, it didn't do anything. Here is my code:

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

Since I have problem with my code, I tried to use the "Duplicate Record" wizzard from access to write the code, but then when I test it, it gave me an error message: "The Command/Action "PasteAppen d" isn't available now".
here is the access wizzard code to duplicate record:

Private Sub cmdDup_Click()
On Error GoTo Err_cmdDup_Click
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 
Exit_cmdDup_Click:
    Exit Sub

Err_cmdDup_Click:
    MsgBox Err.Description
    Resume Exit_cmdDup_Click
End Sub

Anyone please help.

thanks!

[ADezii]

The following code works fine, and will automatically Add a New, Duplicate Record as intended. Where you will run into trouble is if you have a Primary Key or Unique Index such as a Social Security Number. The code will Add the New Record, but you will never be able to Save it. Hope this helps.

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

[bluemoon9]

thanks,
that's the code I used. It's from the wizzard. but when I click on it, it gave me an error message "The command/action "paste append" is not available now". I have no clue why it gave me that kind of message just on this particular form of my database. By the way, I don't have any unique key, the primary key is just an auto number.

thanks!

[Echidna]

Hi,

you could try this:

DoCmd.RunSQL "INSERT INTO tblName (Fieldnames)
SELECT fieldnames from tblname WHERE (autonumberfield = " &
Me.autonumberfield & ")"

this is the statement I usually use to replicate a record (or group of records) within a single table.

Hope this hepls


Leon

[ADezii]

Hi,

you could try this:

DoCmd.RunSQL "INSERT INTO tblName (Fieldnames)
SELECT fieldnames from tblname WHERE (autonumberfield = " &
Me.autonumberfield & ")"

this is the statement I usually use to replicate a record (or group of records) within a single table.

Hope this hepls


Leon

That's definately a nice little alternative.

[bluemoon9]

Thanks Leon,
Actually it worked, but then the form didn't show the new record which I've just copy, it still shows the old record which I've copy from.
What I was trying to do is: allow user select a record from search box, open the form with that record, copy the selected record as a new record by click on the cmdCopy button on the form. Your query is great. But is there a way that when user copy the record, the form show the new rec?

thanks alot.

bluemoon.

[ADezii]

Thanks Leon,
Actually it worked, but then the form didn't show the new record which I've just copy, it still shows the old record which I've copy from.
What I was trying to do is: allow user select a record from search box, open the form with that record, copy the selected record as a new record by click on the cmdCopy button on the form. Your query is great. But is there a way that when user copy the record, the form show the new rec?

thanks alot.

bluemoon.

But is there a way that when user copy the record, the form show the new rec?

After the User duplicates the Record, it should now be the Last Record, ergo:

DoCmd.GoToRecord , , acLast

[bluemoon9]

Thanks, but it doesn't quite work that way. I've posted the code you have suggested, but the form didn't go to the last record. I guess since when user searched for a record, the form only opened that particular record, therefore, there is actually only one record in that form.

Any other suggession?

thanks!

bluemoon

[ADezii]

Thanks, but it doesn't quite work that way. I've posted the code you have suggested, but the form didn't go to the last record. I guess since when user searched for a record, the form only opened that particular record, therefore, there is actually only one record in that form.

Any other suggession?

thanks!

bluemoon

Try a Requery on the Form, then advance to the Last Record.

[Echidna]

Hi.

Is the form filtered to that particular record?

if it is, clear the filter, and send the form to the last record.

just a thought

Leon

[missinglinq]

Leon's answer is the same thing that occurred to me. If the form was filtered to only show RecordA, RecordA is all that it's going to show., whether or not a new record was created.

Linq ;0)>

[Vancer]

Duplicate Wizard Button Does Not Save Record

ADezii pointed out the duplicate of the record does not get saved.

This code worked for duplicating a record on a form with several combo boxes, calculated fields, checkboxes...

'------------------------------------------------------------
' Command81_Click
'
'------------------------------------------------------------
Private Sub Command81_Click()

    Playsound ("C:\WINDOWS\media\Windows Navigation Start.wav ")
    
    On Error Resume Next
    DoCmd.RunCommand acCmdSelectRecord
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdCopy
    End If
    If (MacroError = 0) Then
'        .AddNew
        DoCmd.RunCommand acCmdRecordsGoToNew
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdSelectRecord
    End If
    If (MacroError = 0) Then
        DoCmd.RunCommand acCmdPaste
    End If
    If (MacroError <> 0) Then
        Beep
        MsgBox MacroError.Description, vbOKOnly, ""
    DoCmd.GoToRecord , , acLast
    End If

End Sub

Most of the code was generated by the Button Wizard, Record Operations, Duplicate Record.

The [B]DoCmd.GoToRecor d , , acLast[/B ]portion was added after seeing what ADezii wrote.

If the DoCmd.GoToRecor d , , acLast was indented to be in line with the prior line of text, it did not work. It worked as indented above.

The Playsound line can be omitted--just like to hear a click sound when a button is clicked on.

[Vancer]

The duplicate record did save using ADezii's addition to the code.

[Vancer]

Hello All,
If you get the message telling you "...first save the Record" odds are you have a default value in you primary table.
Vancer

[bhbp]

DoCmd.RunComman d acCmdSelectReco rd
DoCmd.RunComman d acCmdCopy
DoCmd.GoToRecor d , , acNewRec
SendKeys "^v"