Clear clipboard use to fix PasteAppend error

[Kent T]

I am using the following routine to copy a row and append it to the table. The user then changes appropriate information relevant to the current date.

I keep getting an error when using CopyPA macro as posted. The error: Action failed Condition: True Action Name: RunCommand Arguments: 38.

When I halt it, the message "the command or action 'PasteAppend' is not available now". Sometimes it will work, but then other times, I get the error. From what I found while searching for this error, the clipboard is full at the time and cannot accept anything more. I attempted to add a routine to clear the clipboard, and that doesn't seem to be working. Perhaps I don't have it defined correctly. Could someone look at this and tell me if I have the functions/sub correctly defined in the right location? Can you tell me why I am getting the error and if I am on the right track? I am using Access 2003.
CopyPA macro:

 
 RunCommand SelectRecord
 RunCommand Copy
 RunCommand PasteAppend 

VBA:

 
Private Sub CopyRecord_Click() 
On Error GoTo Err_CopyRecord_Click
 
Dim stDocName As String 
stDocName = "CopyPA" 
DoEvents
DoEvents
DoEvents
DoEvents
DoCmd.RunMacro stDocName
 
Exit_CopyRecord_Click: 
Exit Sub
 
Err_CopyRecord_Click: 
MsgBox Err.Description 
Resume Exit_CopyRecord_Click
End Sub 

[NeoPa]

The macro looks wrong to me. I don't work with macros ever anyway, but I somehow doubt what you have there makes any real sense. Multiple commands on the same line surely can't be right.

The code looks wrong in a number of places, but you should be able to see that for yourself if you follow the instructions in Before Posting (VBA or SQL) Code. If/when you have a situation with an error that isn't caused by easily avoidable errors (The link should help with that), then please post the details correctly as indicated and we'll see what we can do for you.

[Kent T]

Sorry, there were some formatting problems from pasting in the message. I have adjusted it. I will also look at the link provided. Thanks.

[NeoPa]

Good job :-)

Line #25 of your code is still suffering from being impossible code though. The linked article should help with that, but if you're still stuck when your code is valid then repost it and we can look into it for you.

[Kent T]

Fixed again. The problem seems to be that the clipboard in Windows cannot respond fast enough, so I am trying the clear clipboard method. Is it possible that I need to utilize a delay between the copy and the paste?

[NeoPa]

Try using DoEvents(). You can find all the details using Context-Sensitive Help.

[Kent T]

I will try that and see what happens.
Thanks for the suggestion.

[NeoPa]

You're welcome.

PS. I like the way you fix the problems when found, rather than adding extra posts that only tend to confuse people reading the thread.

PPS. To format code the best way, add the closing [/code] tag to the end of the last line rather than on the following one. It saves an empty line at the bottom ;-) Also, within [code] tags, spaces are always shown as is and are the same width as all other chars, so they can be used to tabulate data, whereas TAB chars are useless for that.

[Kent T]

Updated code to utilize DoEvents. I have multiple instances because when testing it several times in a row (clicking quickly), the same issue appears. However, in actual use, probably only one record will be copied in a session, so this may not occur. I think it is rather caveman to put in several DoEvents, but until I learn more, it may do. I will continue to test it and repost.

[NeoPa]

Kent T:
I think it is rather caveman to put in several DoEvents

I agree. Without closer contact with the project it's hard to know how best to handle, but if a single one doesn't do it then I'd be looking to see why.

PS. Be careful of what you retro-fit to the question (Post #1). Where changes represent the question as it should have been, then retro-fit avidly. Where they represent progress in the thread, then it's not so good an idea. You're obviously someone who picks up ideas well, so I'm sure this will make sense to you.

PPS. Another idea to consider might be to avoid the use of macros entirely. I'm not a fan anyway, but if all is VBA then you can predict the flow of the code, whereas with macros calling code, that's less clear. Just a thought.

[Kent T]

It's a good thought. I had it banging through my little brain all weekend, and dummy here found that the same commands are available in VBA that I was using in the macro. I have nixed the macro and changed the code to include DoCmd.RunCommand as needed. I was making it entirely too hard. Thanks for all your direction.

[NeoPa]

Good to hear :-) There's no reason everyone should have to learn all the same lessons from scratch.