locked
How to prevent pasting of multiple records into a datasheet? RRS feed

  • Question

  • I have many forms in Datasheet view in my application. Many users have been pasting multiple records into the datasheet from their clipboard. This is causing a lot of problems. Is it possible to prevent pasting of multiple records at once? Is it possible to prevent pasting of even a single record too? I would like to allow users to past values into a cell, but not multiple cells, or records, at once.

    If its only possible by completely denying the user the ability to paste into a Datasheet, I will have to go that route. I hope I don’t have to, but if so, what is the best way to do that?

    Thanks for the advice!
    Thursday, January 5, 2017 11:01 PM

Answers

  • Hi,

    You won't use DoCmd.CancelEvent because the KeyDown event is not cancellable. Rather, you would set the KeyCode variable to 0. For example:

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If Shift = 2 Then
        If KeyCode = 86 Then
            KeyCode = 0
        End If
        
    End If
    
    End Sub

    And if you're going to use the form event (rather than the control's), then make sure the Key Preview property is set to Yes.

    Hope it helps...

    • Marked as answer by HTHP Monday, January 9, 2017 3:26 PM
    Friday, January 6, 2017 3:41 PM

All replies

  • Hi,

    Just thinking off the top of my head I would probably try to disable pasting any data at all. For example, remove the Ribbon, disable right click, and trap Ctrl + V.

    Just a thought...

    Thursday, January 5, 2017 11:25 PM
  • I've got the Ribbon and Right Click things.

    However, I'm having a hard time capturing Ctrl+V. I must also capture the seldom used but equivalent Ctrl+Insert too. I have 2 issues.

    One, the DoCmd.CancelEvent does not cancel the paste.

    Two, If user presses V or Insert twice or more, like: Ctrl+V+V+V+V, the last two captured keys are both V. Equally important, if the user presses any sequence of keys after Ctrl and the last letter is V or Insert, paste is still happening. Like: Ctrl+A+B+V. I don't know how to test for CONCURRENT keypresses using VBA.

    Option Compare Database
    Option Explicit
    Dim KeyPress(1) As String
    
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    KeyPress(0) = KeyPress(1) & "+"
    KeyPress(1) = KeyCode
    'Debug.Print KeyPress(0) & KeyPress(1)
    If (KeyPress(0) & KeyPress(1) = "17+86") Or (KeyPress(0) & KeyPress(1) = "17+45") Then
        DoCmd.CancelEvent
        Debug.Print "Should Cancel: " & KeyPress(0) & KeyPress(1)
    End If
    End Sub

    • Edited by HTHP Friday, January 6, 2017 2:59 PM
    Friday, January 6, 2017 2:56 PM
  • Hi,

    You won't use DoCmd.CancelEvent because the KeyDown event is not cancellable. Rather, you would set the KeyCode variable to 0. For example:

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If Shift = 2 Then
        If KeyCode = 86 Then
            KeyCode = 0
        End If
        
    End If
    
    End Sub

    And if you're going to use the form event (rather than the control's), then make sure the Key Preview property is set to Yes.

    Hope it helps...

    • Marked as answer by HTHP Monday, January 9, 2017 3:26 PM
    Friday, January 6, 2017 3:41 PM
  • Thanks, Is there a way to test if the CTRL key is being held down? Still have problem with CTRL+C+C+C, or CTRL+A+S+D+F+C. The KeyCode does not register that CTRL is still pressed down, so my code computes 86+86 as the last two KeyCodes. I can't think of a good way to prevent sequences that may lead to paste.
    Friday, January 6, 2017 8:52 PM
  • Thanks, Is there a way to test if the CTRL key is being held down? Still have problem with CTRL+C+C+C, or CTRL+A+S+D+F+C. The KeyCode does not register that CTRL is still pressed down, so my code computes 86+86 as the last two KeyCodes. I can't think of a good way to prevent sequences that may lead to paste.

    Hi HTHP,

    To test for CtlA, I use (in the KeyDown event):

          If (Keycode = vbKeyA) And (Shift = acCtrlMask) Then ...

    You can also test for Shift or Alt:

    Constante Waarde
    acShiftMask 1
    acCtrlMask 2
    acAltMask 4

    See the Access Help for more detailed information.

    Imb.   

    Friday, January 6, 2017 9:21 PM
  • Thanks, Is there a way to test if the CTRL key is being held down? Still have problem with CTRL+C+C+C, or CTRL+A+S+D+F+C. The KeyCode does not register that CTRL is still pressed down, so my code computes 86+86 as the last two KeyCodes. I can't think of a good way to prevent sequences that may lead to paste.

    Hi,

    I'm not sure I understand why what I gave you earlier didn't work, so I created a small demo. Could you please give it a try and let me know what keys to press to paste something in the forms? 

    https://1drv.ms/u/s!Ag9k7-z-047Qi35-R2dcRWlOJO8K

    Thanks!

    Saturday, January 7, 2017 7:33 PM
  • So sorry. I didn't realize what the Shift parameter was doing. The name is a bit misleading, I thought it only tracked the Shift Key.

    It works great. Thank you DbGuy and Imb-hb!

    Btw, I did look up the what the Shift did, but couldn't find anything. And, when I go to the help page for KeyDown it doesn't explain the parameter: https://msdn.microsoft.com/en-us/library/office/jj543283(v=office.15).aspx
    • Edited by HTHP Monday, January 9, 2017 3:35 PM
    Monday, January 9, 2017 3:29 PM
  • Okay.. I just found the correct documentation. Sorry again! My mistake. It is clearly explained here: https://msdn.microsoft.com/en-us/library/office/ff834507.aspx
    Monday, January 9, 2017 3:37 PM
  • Hi,

    Glad to hear you got it sorted out. Good luck with your project.

    Monday, January 9, 2017 3:53 PM