PasteAppend Functionality

[pbaldy]

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]

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]

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]

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]

Post your database + name of the form in which you've the problem.

 

[Tupacmoche]

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]

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!

...
[B]Me.MRN_Inport_subform.Setfocus[/B]
DoCmd.RunCommand acCmdPasteAppend
...

 

[Tupacmoche]

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]

You're welcome, good luck.

 

[Tupacmoche]

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]

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.