423,095 Members | 2,030 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,095 IT Pros & Developers. It's quick & easy.

copy record as a new record

P: 56
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunCommand acCmdSelectRecord
  2. DoCmd.RunCommand acCmdCopy
  3. DoCmd.GoToRecord , , acNewRec
  4. DoCmd.RunCommand acCmdPaste
  5.  
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 "PasteAppend" isn't available now".
here is the access wizzard code to duplicate record:
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDup_Click()
  2. On Error GoTo Err_cmdDup_Click
  3.     DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  4.     DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
  5.     DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70 
  6. Exit_cmdDup_Click:
  7.     Exit Sub
  8.  
  9. Err_cmdDup_Click:
  10.     MsgBox Err.Description
  11.     Resume Exit_cmdDup_Click
  12. End Sub
  13.  
Anyone please help.

thanks!
Oct 9 '08 #1
Share this Question
Share on Google+
16 Replies


ADezii
Expert 5K+
P: 8,584
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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
  2. DoCmd.DoMenuItem acFormBar, acEditMenu, 2, , acMenuVer70
  3. DoCmd.DoMenuItem acFormBar, acEditMenu, 5, , acMenuVer70
Oct 9 '08 #2

P: 56
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!
Oct 10 '08 #3

P: 53
Hi,

you could try this:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tblName (Fieldnames)
  2. SELECT fieldnames from tblname WHERE (autonumberfield = " &
  3. 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
Oct 10 '08 #4

ADezii
Expert 5K+
P: 8,584
Hi,

you could try this:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.RunSQL "INSERT INTO tblName (Fieldnames)
  2. SELECT fieldnames from tblname WHERE (autonumberfield = " &
  3. 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.
Oct 10 '08 #5

P: 56
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.
Oct 10 '08 #6

ADezii
Expert 5K+
P: 8,584
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:
Expand|Select|Wrap|Line Numbers
  1. DoCmd.GoToRecord , , acLast
Oct 10 '08 #7

P: 56
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
Oct 11 '08 #8

ADezii
Expert 5K+
P: 8,584
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.
Oct 11 '08 #9

P: 53
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
Oct 14 '08 #10

missinglinq
Expert 2.5K+
P: 3,532
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)>
Oct 14 '08 #11

P: 3
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...

Expand|Select|Wrap|Line Numbers
  1. '------------------------------------------------------------
  2. ' Command81_Click
  3. '
  4. '------------------------------------------------------------
  5. Private Sub Command81_Click()
  6.  
  7.     Playsound ("C:\WINDOWS\media\Windows Navigation Start.wav ")
  8.  
  9.     On Error Resume Next
  10.     DoCmd.RunCommand acCmdSelectRecord
  11.     If (MacroError = 0) Then
  12.         DoCmd.RunCommand acCmdCopy
  13.     End If
  14.     If (MacroError = 0) Then
  15. '        .AddNew
  16.         DoCmd.RunCommand acCmdRecordsGoToNew
  17.     End If
  18.     If (MacroError = 0) Then
  19.         DoCmd.RunCommand acCmdSelectRecord
  20.     End If
  21.     If (MacroError = 0) Then
  22.         DoCmd.RunCommand acCmdPaste
  23.     End If
  24.     If (MacroError <> 0) Then
  25.         Beep
  26.         MsgBox MacroError.Description, vbOKOnly, ""
  27.     DoCmd.GoToRecord , , acLast
  28.     End If
  29.  
  30. End Sub
  31.  
Most of the code was generated by the Button Wizard, Record Operations, Duplicate Record.

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

If the DoCmd.GoToRecord , , 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.
Dec 31 '14 #12

P: 3
The duplicate record did save using ADezii's addition to the code.
Dec 31 '14 #13

P: 3
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
Sep 5 '15 #14

P: 1
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCopy
DoCmd.GoToRecord , , acNewRec
SendKeys "^v"
Feb 11 '18 #15

twinnyfo
Expert Mod 100+
P: 2,486
bhbp,

I have learned to avoid the SendKeys option as much as possible. Although it can work in controlled environments, it is not the preferred method. Perhaps the reason the OP's original code was not working was because once the code goes to a new record, it does not know what to do with an entire record when it is pasted, as only the first field is selected or has the focus. My guess is that the following code would have worked perfectly:

Expand|Select|Wrap|Line Numbers
  1.     With DoCmd
  2.         .RunCommand acCmdSelectRecord
  3.         .RunCommand acCmdCopy
  4.         .RunCommand acCmdRecordsGoToNew
  5.         .RunCommand acCmdSelectRecord
  6.         .RunCommand acCmdPaste
  7.     End With
Notice how the code selects that new record, which allows the paste to add entire record, not just the current (or first) field.

Hope this hepps!
Feb 12 '18 #16

PhilOfWalton
Expert 100+
P: 1,254
I suspect there may be problems if there are any unique keys other than the primary key

Phil
Feb 12 '18 #17

Post your reply

Sign in to post your reply or Sign up for a free account.