PasteAppend Functionality (1 Viewer)

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:06
Joined
Aug 30, 2003
Messages
36,118
Typically I'd expect to be able to run an append query. Is the source linked, and can you identify the records to be pasted?
 

Tupacmoche

Registered User.
Local time
Today, 01:06
Joined
Apr 28, 2008
Messages
291
The source is a linked SQL table with two columns. This table was setup to paste two columns from an Excel spreadsheet so they are mapped to work together. Can an append query use data in the clipboard which is the data source after coping it from an Excel spreadsheet?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 22:06
Joined
Aug 30, 2003
Messages
36,118
That's the destination, not the source. Is the spreadsheet linked (or can it be)? You're still relying on the user to copy something. An append query can't use the clipboard.
 

Tupacmoche

Registered User.
Local time
Today, 01:06
Joined
Apr 28, 2008
Messages
291
Spreadsheet can't be linked. True, the user is copying but that is the extent of what is expected. So, getting back to the original issue is there vba code using a call to the DoCmd.RunCommand acCmdPasteAppend that can populate the copied Excel spreadsheet which is now in the clipboard into this subform? Again, it works from the menu by clicking Paste->PasteAppend menu item.:banghead:
 

JHB

Have been here a while
Local time
Today, 06:06
Joined
Jun 17, 2012
Messages
7,732
Post your database + name of the form in which you've the problem.
 

Tupacmoche

Registered User.
Local time
Today, 01:06
Joined
Apr 28, 2008
Messages
291
The table has two columns data type is nvarchar(25) called import_MRN. The form is called Collect - MRN and has a subform that is bound to the SQL table that is linked to Access DB called MRN. Here is the code on the button that deletes old MRN and adds new ones:

Private Sub btnClear_Click()
Dim LResponse As Integer
Dim msg1 As String
Dim msg2 As String
Dim cdb As DAO.Database
Dim qdef As DAO.QueryDef
msg1 = "You have choosen to continue..."
msg2 = "Exiting... User has canceled."
LResponse = MsgBox("Are you sure you want to clear old MRN's and add new ones?", vbYesNo, "Continue")
If LResponse = vbYes Then
MsgBox (msg1)
Set cdb = CurrentDb
Set qdef = cdb.CreateQueryDef("")
qdef.Connect = cdb.TableDefs("dbo_Import_MRN").Connect
qdef.SQL = "EXEC dbo.sp_MRN_CleanUp" 'Simple runs a truncate table
qdef.ReturnsRecords = False
qdef.Execute dbFailOnError
Set qdef = Nothing
Set cdb = Nothing
MsgBox ("Old MRN's deleted.")
Me.MRN_Inport_subform.Requery
DoCmd.RunCommand acCmdPasteAppend
MsgBox ("Update Complete...!")
Else
MsgBox (msg2)
End If
End Sub

The error, I get when it gets to DoCmd.RunCommand acCmdPasteAppend is Run-time error '2046' The command or action 'PasteAppend' isn't available now. :banghead:
 

JHB

Have been here a while
Local time
Today, 06:06
Joined
Jun 17, 2012
Messages
7,732
It looks like we a going round in circles. First you show some code which doesn't work so suddenly it works and then you show another code that doesn't work, and now we're back again at the first code that obviously doesn't work.
But maybe I did not understand correctly what you wrote.
I've mention it before, it is about the focus. To paste something in a control it needs to have focus and it should be able to receive a value.
You click on a button to run your code, when you do that the focus moves to the button, and a button can't receive a value so the command 'PasteAppend' isn't available.
Therefore in your code you need to set the focus to the control which should receive the value before you paste anything!
Code:
...
[B]Me.MRN_Inport_subform.Setfocus[/B]
DoCmd.RunCommand acCmdPasteAppend
...
 

Tupacmoche

Registered User.
Local time
Today, 01:06
Joined
Apr 28, 2008
Messages
291
JHB

Thank you for this simple solution. I had two versions of this code and the subform name was different. So, when, I tried your code it did not work the mistake was simple enough to fix but, I was doing several thinks at the same time and missed it. So, it works. Thanks!
 

JHB

Have been here a while
Local time
Today, 06:06
Joined
Jun 17, 2012
Messages
7,732
You're welcome, good luck.
 

Tupacmoche

Registered User.
Local time
Today, 01:06
Joined
Apr 28, 2008
Messages
291
JHB,

I have a follow-up question on this matter. The code that, I used works fine but I ran into a problem by accident. I copied a large block of SQL code and when, I clicked on the button, I pasted in SQL. How can, I validate the data that is in the clip-board and prevent this?

Me.MRN_Import.SetFocus
DoCmd.RunCommand acCmdPasteAppend
 

JHB

Have been here a while
Local time
Today, 06:06
Joined
Jun 17, 2012
Messages
7,732
Do you've some rules what valid data is?
If yes, then you can test it against the rules after you've pasted it.
And if the data not followed the rules, then clear it.
 

Users who are viewing this thread

Top Bottom