PasteAppend Functionality

[Tupacmoche]

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

 

[pbaldy]

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.

 

[JHB]

Could it be because the focus is on a control or something else where you're not able to paste anything?

 

[Mark_]

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.

 

[Tupacmoche]

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.

 

[missinglinq]

...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)>

 

[Tupacmoche]

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?

 

[missinglinq]

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)>

 

[Tupacmoche]

ok, so what else do, I have to check?

 

[pbaldy]

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?

 

[Tupacmoche]

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]

Pbaldy,

I have attached a screen shot of the form used to paste in the Excel values. Just to show the process more completely.:banghead: 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.

 

[pbaldy]

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.

 

[Tupacmoche]

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.

 

[JHB]

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.

 

[JHB]

..
I have attached a screen shot of the form used to paste in the Excel values. Just to show the process more completely.:banghead: 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.

Me.TheNameOfContainerForTheDataForm.SetFocus

 

[pbaldy]

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.

 

[Tupacmoche]

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???

 

[JHB]

Could be if you pasting the wrong type of data, ex. text in a number field type.

 

[Tupacmoche]

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.