Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-13-2017, 09:13 PM   #16
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 6,758
Thanks: 2
Thanked 1,790 Times in 1,756 Posts
JHB will become famous soon enough JHB will become famous soon enough
Re: PasteAppend Functionality

Quote:
Originally Posted by Tupacmoche View Post
..
I have attached a screen shot of the form used to paste in the Excel values. Just to show the process more completely. The clear import button simple calls a sproc to truncate the table. The second button Update... paste in the selected Excel values and updates some other tables. ..
Looking at the picture I see the button for pasting isn't in the same form as the data, so you need to set focus to the form in which you want to paste data into.
Code:
Me.TheNameOfContainerForTheDataForm.SetFocus

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-14-2017, 07:42 AM   #17
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,581
Thanks: 8
Thanked 3,602 Times in 3,546 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: PasteAppend Functionality

Quote:
Originally Posted by JHB View Post
That would be correct if you're not standing in a new record ready for inputting data. The star to the left indicate you're in a new record.
I didn't notice that. I always hide the record selectors so don't really notice them.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-16-2017, 01:23 PM   #18
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 72
Thanks: 5
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: PasteAppend Functionality

I don't know what changed but PasteAppend menu item is now back and available. But, I prefer to have the end user simple click on a button and run the paste code. This is what, I have used but it is not working. I copy the values from an Excel spreadsheet which means the values are in the clip-board and then just click on the button to paste. Here is the code:

Private Sub btnPaste_Click()
Me.MRN_Import.Form.SetFocus
Me.MRN_Import.Form.AllowAdditions
DoCmd.RunCommand acCmdPasteAppend
Me.MRN_Import.Requery
Me.MRN_Import.Form.Refresh
Me.MRN_Import.Form.Repaint
End Sub

What am I missising???

Tupacmoche is offline   Reply With Quote
Old 10-16-2017, 09:10 PM   #19
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 6,758
Thanks: 2
Thanked 1,790 Times in 1,756 Posts
JHB will become famous soon enough JHB will become famous soon enough
Re: PasteAppend Functionality

Could be if you pasting the wrong type of data, ex. text in a number field type.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-17-2017, 07:07 AM   #20
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 72
Thanks: 5
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: PasteAppend Functionality

No data types are all clearly mapped. And it works using the menu paste->PasteAppend without any issues. But, I want to do it in code.
Tupacmoche is offline   Reply With Quote
Old 10-17-2017, 07:15 AM   #21
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,581
Thanks: 8
Thanked 3,602 Times in 3,546 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: PasteAppend Functionality

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?
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-17-2017, 07:41 AM   #22
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 72
Thanks: 5
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: PasteAppend Functionality

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?

Tupacmoche is offline   Reply With Quote
Old 10-17-2017, 08:15 AM   #23
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,581
Thanks: 8
Thanked 3,602 Times in 3,546 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: PasteAppend Functionality

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.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 10-17-2017, 09:02 AM   #24
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 72
Thanks: 5
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: PasteAppend Functionality

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.
Tupacmoche is offline   Reply With Quote
Old 10-17-2017, 09:44 PM   #25
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 6,758
Thanks: 2
Thanked 1,790 Times in 1,756 Posts
JHB will become famous soon enough JHB will become famous soon enough
Re: PasteAppend Functionality

Post your database + name of the form in which you've the problem.
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Old 10-18-2017, 07:22 AM   #26
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 72
Thanks: 5
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
Re: PasteAppend Functionality

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.
Tupacmoche is offline   Reply With Quote
Old Yesterday, 01:48 AM   #27
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 6,758
Thanks: 2
Thanked 1,790 Times in 1,756 Posts
JHB will become famous soon enough JHB will become famous soon enough
Re: PasteAppend Functionality

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:
...
Me.MRN_Inport_subform.Setfocus
DoCmd.RunCommand acCmdPasteAppend
...

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
PasteAppend not working on a different machine... ChrisSedgwick Forms 2 04-15-2016 06:27 AM
[SOLVED] he command or action PasteAppend isn't available now EddiRae Modules & VBA 5 10-13-2015 09:01 AM
2003 PasteAppend not available error message when using a Duplicate Record Command Bu glennsa1@aol.com Modules & VBA 0 11-29-2012 08:56 AM
Tab Order Functionality ajetrumpet Forms 2 03-22-2008 05:19 PM
Database Functionality Without Vba Steepleboy04 General 15 10-06-2007 02:00 PM




All times are GMT -8. The time now is 08:48 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World