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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-12-2017, 12:45 PM   #1
Tupacmoche
Newly Registered User
 
Join Date: Apr 2008
Posts: 72
Thanks: 5
Thanked 4 Times in 4 Posts
Tupacmoche is on a distinguished road
PasteAppend Functionality

Hi All,

This code worked fine once when, I wrote it. Then when testing it again the PasteAppend button became grayed out. In this code, I call the Paste Append functionality from code but now, I get this error: Run-time error '2046' The command or action 'PasteAppend' isn't available now. Why? What did, I do to disable it? I have googled many articles about adding library references ect but it still does not work. I have even see numerous discussions on this site but can't get it to work. Can anyone see what, I'm missing? Here is the code:

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_MRN_Import").Connect
qdef.SQL = "EXEC dbo.sp_MRN_CleanUp"
qdef.ReturnsRecords = False
qdef.Execute dbFailOnError
Set qdef = Nothing
Set cdb = Nothing
Me.MRN_Inport_subform.Requery
MsgBox ("Old MRN's deleted, click ok to add new import.")
RunCommand acCmdPasteAppend
MsgBox ("Update Complete...!")
Else
MsgBox (msg2)
End If
End Sub

Tupacmoche is offline   Reply With Quote
Old 10-12-2017, 01:11 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,586
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

Not a method I've used, but make sure the record source of the form allows you to add a new record, and that the Allow Additions property is Yes.
__________________
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 online now   Reply With Quote
Old 10-12-2017, 01:54 PM   #3
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 6,760
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 it be because the focus is on a control or something else where you're not able to paste anything?

__________________
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-12-2017, 02:19 PM   #4
Mark_
Newly Registered User
 
Join Date: Sep 2017
Posts: 118
Thanks: 1
Thanked 17 Times in 17 Posts
Mark_ is on a distinguished road
Re: PasteAppend Functionality

What do you have selected when you try to paste? Has something been removed that would select something you could then paste?

The segment of code does not include neither the selection nor copy that would be needed prior to pasting.

Please see THIS for an example of what would normally be found when using paste.
Mark_ is offline   Reply With Quote
Old 10-13-2017, 05:54 AM   #5
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

Thanks for the comments. Yes, the additions property is set to on or yes. The focus is on a singular subform that accepts the data. Now, to the third comment. The way, I select the data is with the mouse. This is an end user tool so, I want to keep it as simple as possible. At first, I simple put the cursor on the first row, column where the data was being copied to and selected PasteAppend from the menu. But, that stopped working as I mentioned in the first post. I added the programmatic code AFTER the PasteAppend menu choose stopped working in an attempt to circumvent the grayed out button and then got the error code message that I mentioned. I will keep looking for an answer but if anyone else has a solution please post it. Thanks to all.
Tupacmoche is offline   Reply With Quote
Old 10-13-2017, 07:24 AM   #6
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,000
Thanks: 11
Thanked 632 Times in 596 Posts
missinglinq is just really nice missinglinq is just really nice missinglinq is just really nice missinglinq is just really nice missinglinq is just really nice
Re: PasteAppend Functionality

Quote:
Originally Posted by pbaldy View Post

...make sure the record source of the form allows you to add a new record
You need to check this, as Paul suggested, This error almost always means that the RecordSource for the Form is Read-Only, for whatever reason.

Can you edit a Record on the Form?

Linq ;0)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 10-13-2017, 07:47 AM   #7
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

Thanks for your comments missinglinq but as, I mentioned, I already did that. But there is a new interesting wrinkle to this problem that I discovered. This import process is on a tabbed form. When, I select other tabs the PasteAppend button become available. It only becomes grayed out when I select the Import MRN tab. Does this shed any light on the issue?

Tupacmoche is offline   Reply With Quote
Old 10-13-2017, 09:15 AM   #8
missinglinq
AWF VIP
 
missinglinq's Avatar
 
Join Date: Jun 2003
Location: Richmond (Virginia that is!)
Posts: 6,000
Thanks: 11
Thanked 632 Times in 596 Posts
missinglinq is just really nice missinglinq is just really nice missinglinq is just really nice missinglinq is just really nice missinglinq is just really nice
Re: PasteAppend Functionality

Quote:
Originally Posted by Tupacmoche View Post
Thanks for your comments missinglinq but as, I mentioned, I already did that.
Sorry, but you don't mention anywhere where you've checked this Form to see if you can add a Record...or edit a Record. The AllowAdditions property being set to Yes doesn't automatically mean that you can add new Records.

What the Form allowed before it stopped working has nothing to do with what it's doing now.

Linq ;0)>
__________________
The Devil's in the Details!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


(All code solutions tested in Access 2003/2007, before posting, unless otherwise noted.)
missinglinq is offline   Reply With Quote
Old 10-13-2017, 09:57 AM   #9
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

ok, so what else do, I have to check?
Tupacmoche is offline   Reply With Quote
Old 10-13-2017, 10:07 AM   #10
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,586
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

What is the record source of the form, a table or a query? If a query, can you add a record directly in the query?
__________________
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 online now   Reply With Quote
Old 10-13-2017, 10:54 AM   #11
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 record source is a linked table that is SQL Server 2008 R2. I can type into the table and it when I query the table in SQL MS, can see what, I added. Then, I deleted the rows and re-queried the table in SQL MS and it was gone. Now, I selected 250 rows from an Excel worksheet and tried to select PasteAppend and it is still grayed out.
Tupacmoche is offline   Reply With Quote
Old 10-13-2017, 11:51 AM   #12
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

Pbaldy,

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. When, I first started this I would simple put the cursor into the SourcePatientMRN filed and PasteAppend from the menu. When it stopped working, I tried the programmatic method that, I sent.
Attached Images
File Type: jpg MRN_Import.JPG (34.4 KB, 35 views)
Tupacmoche is offline   Reply With Quote
Old 10-13-2017, 12:37 PM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 30,586
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

I note in the navigation buttons at the bottom, the new record icon is disabled. That would indicate you can't add a new record via that form. You say the source is a linked SQL Server table? Can you add a record via the linked table, or are you adding directly in SQL Server. If the SQL Server doesn't have a primary key defined, you won't be able to add records via the linked table.
__________________
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 online now   Reply With Quote
Old 10-13-2017, 02:16 PM   #14
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 grid is a sub-form within the form. The linked SQL table has only two columns that are the data source bound to the linked table. When it worked, I simple pasted into the sub-form and the table was populated. I always add a PK but, I check to make sure.
Tupacmoche is offline   Reply With Quote
Old 10-13-2017, 07:43 PM   #15
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 6,760
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 pbaldy View Post
I note in the navigation buttons at the bottom, the new record icon is disabled. That would indicate you can't add a new record via that form. ...
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.
Attached Images
File Type: jpg NewRecord.jpg (41.1 KB, 66 views)

__________________
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 06:35 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