[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • 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.SetFocus
Me.sfmGenFeed.Form.AppendMe  'this just runs DoCmd.RunCommand acCmdPasteAppend
Me.sfmGenFeed.Requery

RecalcTotals        'A procedure to calculate aggregates

proc_exit:
    Exit Sub
proc_err:
    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 ?
0
Natchiket
Asked:
Natchiket
  • 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?





0
 
NatchiketAuthor Commented:
I have tried commenting out RecalcTotals
This has not improved the situation
0
 
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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.

0
 
NatchiketAuthor Commented:
The contents are the same
0
 
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 ?
0
 
puppydogbuddyCommented:
Natchiket,

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
sExit:
    On Error Resume Next
    Exit Sub
E_Handle:
    MsgBox Err.Description & vbCrLf & "sClearClipboard", vbOKOnly + vbCritical, "Error: " & Err.Number
    Resume sExit
End Sub  
0
 
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
0
 
NatchiketAuthor Commented:
As usual I have managed to sort this out by myself :)
FYI
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


0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

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