• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 431
  • Last Modified:

Multiple Paste Append destabilises subform ACC2K2

I have a main form with a subform.  In the main form there is a command button (paste from Clipboard) which causes data which has been copied to the clipboard from Excel to be paste-appended into the subform using the following code:

Private Sub cmdPaste_Click()
On Error GoTo proc_err

Me.sfmGenFeed.Form.AppendMe  'this just runs DoCmd.RunCommand acCmdPasteAppend

RecalcTotals        'A procedure to calculate aggregates

    Exit Sub
    Call fncErrorLog("Form", Me.Name, "cmdPaste_Click", Err.Description)
    Resume proc_exit

End Sub

This works fine on the first click.  However the problem occurs on subsequent clicks.  The behaviour is as follows: although more appended data appears in the subform, inspection of the underlying table indicates that although the first batch of appended records are present, subsequent appends do not in fact append any more data into the table.  Furthermore the aggregate calculations do not change and there is no standard confirmatation dialogue box.

Any ideas anyone ?
  • 5
  • 3
2 Solutions
Jonathan KellyCommented:
are you turning the warnings off anywhere?

you may be violating RI or another type of constraint.

Ive never seen this approach before! pasting data from the clipboard in this way sounds a little dodgy.
are u sure that the clipboard will contain what you expect?

NatchiketAuthor Commented:
I have tried commenting out RecalcTotals
This has not improved the situation
NatchiketAuthor Commented:
no I'm not turning off warnings
There is no contsraint violation as no RI has been set up in the db
I'm setting the recordsource of the subform in code
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Jonathan KellyCommented:
I would try pasting the contents of the clipboard into something like notepad after your first 'click' to see whats been pasted on the seocnd click.

NatchiketAuthor Commented:
The contents are the same
Jonathan KellyCommented:
and if you manully paste the records do they get added to the table?

have you tried removing the error trap to see if an error is being generated ?

Could it be that the clipboard needs to be cleared between each "cut and paste operation"?  If so, than this may help.

You can use these three API calls in sequence, to open, clear, and then close the clipboard:
Private Declare Function apiOpenClipboard Lib "user32" Alias "OpenClipboard" _
    (ByVal hwnd As Long) As Long
Private Declare Function apiEmptyClipboard Lib "user32" Alias "EmptyClipboard" _
    () As Long
Private Declare Function apiCloseClipboard Lib "user32" Alias "CloseClipboard" _
    () As Long
Public Sub sClearClipboard()
'   Procedure to clear the contents of the Windows clipboard
    On Error GoTo E_Handle
    Dim lngReturn As Long
    lngReturn = apiOpenClipboard(Application.hWndAccessApp)
    lngReturn = apiEmptyClipboard
    lngReturn = apiCloseClipboard
    On Error Resume Next
    Exit Sub
    MsgBox Err.Description & vbCrLf & "sClearClipboard", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub  
NatchiketAuthor Commented:
Manual pasting does work, however because I can't trap the pasting event, the aggregate totals don't get updated until the subsequent paste
NatchiketAuthor Commented:
As usual I have managed to sort this out by myself :)
I'm using an API to retrieve the contents of the clipboard into a string
Marshaling the data into a standalone ADO recordset
Using the ADO recordset in combination with the target table chatacteristics to create an SQL INSERT for each row of data
Requerying the subform
Run aggregate functions
Seems to work ok

Thanks for your input guys, however I can only award a B since no one actually came up with an answer

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now