Copy and Paste Data

[GaelicFatboy]

I have a table on a form listing a schedule of parts which are allocated to a client and site. Behind each part listed on the table are two different lists of properties associated with each part.

What I'm looking to do is use the Ctrl C (copy) and Ctrl V (paste) to copy and paste records to the table.

When I go to paste two records, or more, at a time I get a popup asking me if I wish to proceed. What I'm looking to do is trap the Yes or No reply to this popup message so I can either run additional code or not. Any ideas?

I'm using Access 2016.

Cheers

D

 

[Ranman256]

dont copy & paste. (thats excel)
use an append query. This is access.

 

[GaelicFatboy]

The client has requested the Ctrl C and Ctrl V options, unless I can stop the users from using Ctrl V to paste records. Any thoughts?

Cheers

D

 

[isladogs]

You can block the use of ctl+c & ctl+v using VBA but if your users are just going to complain, there's no point

 

[GaelicFatboy]

So how do I trap Ctrl V, the on Key events don't catch it in the main form or the sub-form?

Cheers

D

 

[isladogs]

The code below blocks both ctl+c & ctl+v
Note that you also need to prevent use of roght click context menu as explained in the code

Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

On Error GoTo Err_Handler

'Colin Riddington - 04/10/2017
'prevents copying & pasting text using Ctrl+C ; Ctrl+V
'Also set form KeyPreview=True & shortcut menu=false(to prevent right click)

    Dim intCtrlDown As Integer
    intCtrlDown = (Shift And acCtrlMask) > 0
     
    If KeyCode = vbKeyV Then
      If intCtrlDown Then
        MsgBox "Sorry - pasting text is not allowed on this form", vbCritical, "ERROR"
        KeyCode = 0
      End If
    End If
 
    If KeyCode = vbKeyC Then
      If intCtrlDown Then
        MsgBox "Sorry - copying text is not allowed on this form", vbCritical, "ERROR"
        KeyCode = 0
      End If
    End If

Exit_Handler:
    Exit Sub

Err_Handler:
   MsgBox "Error " & err.Number & "in Form_KeyDown procedure: " & err.Description
    Resume Exit_Handler

End Sub

 

[GaelicFatboy]

I got the key press working using a control event trap, the form events don't catch the key press. Cheers for the right click heads up, I'd forgotten about that just now.

Cheers for the help, much appreciated.

D

 

[isladogs]

I'm surprised the Form_KeyDown didn't work for you as I've used it for several years in both A2010 & A2016.

Did you remember to set Form KeyPreview=True otherwise the KeyDown code won't do anything

 

[GaelicFatboy]

Good point, and no I didn't. Just rectified my mistake and the form event traps are now triggering nicely. This property is the last on one the list and the only one to drop off the bottom of the screen and out of view, so I forgot about it.

Cheers for your help.

D