"Duplicate Record" Macro Error Message

[NoSmoke]

One of the actions available with the Command Button Wizard is "Duplicate Record" (which I gather is to copy the current record and append it as a new record). Trouble is, it results in a "The command or action 'Paste' isn't available now" error message.

I've looked at the Macro the action creates and find that the "paste" action is not even listed as available with the RunMenuCommand (although it shows up as the command when the macro is displayed). Same thing for "copy" BTW although the copy commend seems to work as if "paste" is clicked on the ribbon after the error message is cleared, the record is successfully appended ("paste is grayed out when the error message appears and goes to bold when the error message is cleared.

Can anyone please help me with this? I have also been trying BTW to perform this task with a macro using SendKeys but it doesn't get past the copy part.

 

[Alansidman]

I want to be sure I understand what you are attempting. You have a record with some fields that you want to copy to a new record. Here is some VBA that I have used in similar situations. Put this code in the on click event for a command button. You will need to change the control names to match yours. Additionally, you may have to add new variables depending on how many fields you are attempting to copy.

Private Sub copyrecordbutton_Click()
On Error GoTo Err_copyrecordbutton_Click
Dim pown As Variant
Dim psource As Variant
Dim pclient As Variant
Dim ptax As Variant

pown = txtcurrent1.Value
psource = txtcurrent2.Value
pclient = txtcurrent3.Value
ptax = txtcurrent4.Value

RunCommand acCmdRecordsGoToNew

txtnew1.Value = pown
txt2new.Value = psource
txtnew3.Value = pclient
txtnew4.Value = ptax

   
Exit_copyrecordbutton_Click:
    Exit Sub

Err_copyrecordbutton_Click:
    MsgBox Err.Description
    Resume Exit_copyrecordbutton_Click
    
End Sub

This code is for only four fields. If you need more just amend the code.

Alan

 

[NoSmoke]

Thanks Alan. Your understanding of the problem is correct and, while I know little about VB programming, I think I can follow what the code does. Trouble is though, rather than four fields in the record, I have 102 which will result in a lot of code.

I'm thinking there must be an easier way - do you have any idea why the Duplicate Record action (or Copy in SendKeys) doesn't work??

 

[Alansidman]

I have no idea about the duplicate record action. But having 102 fields concerns me and leads me to believe you may have a normalization issue. Why do you have so many fields? Are you familiar with the rules of normalization? Why would you want to copy 102 fields to a new record?

If you are not familiar with normalization, perhaps this short white paper will help.

http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

Good luck with your project.

 

[NoSmoke]

I'm M/L familiar with the topic but my application is more a "flat" (if that's the term) file than anything else (it doesn't have/need any keys). The app is "patient" admission data for a wildlife rescue organization. There are many fields required to store the applicable data such as contact information for whomever found or brought in the injured animal, circumstances of the capture, and, results of the medical examination where there are many potential items to be checked off or filled in. There is no duplication of data in the various tables as the tables other than the admissions data are only for population of pull-down lists for filling in the admissions table.

Since there are so many fields to fill out for each patient, a copy/append function would be useful for similar instances eg. a duck and many ducklings are admitted so copies could be made for each of the ducklings as their circumstances would likely be identical or similar (and we wish to keep a separate record for each critter).

BTW, I originated this database in Access 97 and converted it to 2010 but had no issues at all with '97' functions apparently not working properly. I'm also a bit bummed that 2010 does not apparently do user permissions(?).

I also have a "statistics" form using the Count action to display how many animals of various types were admitted to the facility during the year - it worked fine in 97 but sporadically shows "#error" messages in all the fields in 2010. Simply closing and reloading the form usually clears it.

 

[Alansidman]

Understand what you are saying. Something you mentioned triggered a thought. Enter a minimum amount of information for your new record to establish a new unique record. Run an update query to fill in the missing data with data from another record. This may be the way to go. As to permissions, look at this link. I have used this as a means to allow permissions.

http://www.mrexcel.com/forum/microsoft-access/248191-tutorial-access-menu-permissions-system.html

 

[NoSmoke]

Thank you again Alan for the help. What I've come up with to append-paste a record copy is split the select/copy/append-paste SendKey macro (which doesn't work because the copy part deselects the record for some reason) into three steps; the first selects the current record, the user then types ctrl/c to do the copy, the third step is another SendKey macro that performs the append-paste. The first and third steps are invoked by button(s). The third step can be repeated for additional copies as required so the process is pretty quick & easy.

And thanks for the permissions tip - I will certainly try it.

 

[NoSmoke]

Tried a couple of things:

Created an entirely new database and added a "Duplicate Record" macro within a command button.
Ran the macro and got the same error message (".....Paste isn't available now") as above.

Reran the macro in single step and it worked!

Reran the same macro in my original project database in single step and it worked too.

What the heck is wrong here??