Results 1 to 11 of 11
  1. #1
    Harribone is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Location
    UK
    Posts
    3

    Cancel Ctrl+Z action with VBA


    Hi,

    I want to trap and cancel Ctrl+Z on a form and so far I can't seem to stop the action.

    Code:
    Private Sub Form_KeyPress(KeyAscii As Integer)
    
    
    ' Attempting to disable Ctrl+Z but doesn't seem to behave as expected
    
    
    ' 26 = Ctrl+Z
    If KeyAscii = 26 Then
        KeyAscii = 0
    End If
    
    
    End Sub
    Above is what I have and even though KeyAscii is set to 0 this doesn't have the effect of disabling the undo action.
    I have also tried KeyDown but this seems to fire when hitting Ctrl so will not capture Ctrl + Z.

    Key Preview property on the form is Yes/True.

    Hoping I am missing something obvious...

    Thanks
    Harribone

  2. #2
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Have you set Key Preview to Yes on the form?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  3. #3
    Harribone is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Location
    UK
    Posts
    3
    I have indeed as mentioned in post.
    I have also added a line of code after KeyAscii = 0 with a breakpoint to check the code triggers and KeyAscii has picked up the new 0 value.

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Quote Originally Posted by Harribone View Post
    I have indeed as mentioned in post.
    I have also added a line of code after KeyAscii = 0 with a breakpoint to check the code triggers and KeyAscii has picked up the new 0 value.
    Sorry, I missed that part.
    All I tested was to msg the code in the event and received the confirmation that the code was actually 26

    You will need to wait for an expert.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think you cannot simply cancel that as it is a Windows function/feature and that you'd have to use Undo in order to cancel that change. However, you could end up undoing the whole record since the event applies to the form, not the control. If that were an issue, you'd need to know which control needed to be undone and run that event for the control only. If adding undo to your code doesn't work, I would use keydown, not keypress as I know that event works with undo. Also, I might use the key constants vbKeyZ and acCtrlMask rather than numbers.

    This sort of thing is almost impossible to test/troubleshoot because it will fire once for each key pressed as it's almost a sure thing that both keys will never make contact at the same millisecond in time.

    EDIT - just tested Me.Undo vs Screen.ActiveControl.Undo
    1st one seems to do what you want. 2nd one does not. That undo restores what was there, which basically allows ctr+z to restore what was deleted. That's strange.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    One minute this seems to work, next it doesn't. I could probably play with this all day and never be sure it's reliable.
    Code:
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyZ And Shift = acCtrlMask Then KeyCode = 0
    End Sub
    Methinks you'd have to code for esc key as well, since that can behave like ctr+z.

    Have to wonder why it's important to have this function in a db.
    Last edited by Micron; 09-22-2022 at 01:55 PM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    There are a few issues with your code as already mentioned. First of all, ASCII code 26 isn't Z. It should be 90.
    For a complete list, see my article ASCII Character Set (isladogs.co.uk)

    Secondly you need to trap for Ctrl together with Z as Micron suggested.
    You might find another of my example apps useful for the code needed Kiosk Style App (isladogs.co.uk)

    Sorry but I'm unable to just correct your code at the moment
    Last edited by isladogs; 09-23-2022 at 11:29 AM.
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  8. #8
    Harribone is offline Novice
    Windows 10 Office 365
    Join Date
    Sep 2022
    Location
    UK
    Posts
    3
    Thanks for the responses.

    I was using 26 for the ASCII number as that is what I've picked up when testing Ctrl+Z. Ctrl on it's own doesn't trigger my code, Z gives me 100.

    Code:
    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
    If KeyCode = vbKeyZ And Shift = acCtrlMask Then KeyCode = 0
    End Sub
    The above seems to work for my requirements - I will continue to test anyway.

    Reason I need this behaviour in my DB is because I am updating a quantity in a record on the form and then also altering another table (or appending) to create a transaction. Discovered a user could undo the change made to the record linked to the form but the vba created/altered transaction record will remain unchanged which creates problems later on.

    Still exploring options and approaches to determine best way to handle my requirements and will probably find a better way to solve this and therefore may not need to consider this undo problem.
    New to Access databases and there is a lot to learn!

  9. #9
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    The problem with your approach as I see it is that you don't allow for correcting mistakes. If I could not fix this with table relationships and cascading edit/delete option then what I'd probably do instead is create staging table(s) and write temporary records to it. Only if the process is completed would I then append/edit the staged data back to the original table and delete from the staging table. Staging table should not have pk fields (e.g. not autonumber) but you would store the pk's of the original record there so that when editing you do so to the proper original record. Appending is simpler. If the db is split it's easier to have the staging table in each users front end, otherwise you would have to distinguish between the records on the basis of user id.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Harribone View Post

    I was using 26 for the ASCII number as that is what I've picked up when testing Ctrl+Z. Ctrl on it's own doesn't trigger my code, Z gives me 100.
    Sorry but I don't understand your comment. Below is part of the ASCII character set showing both ASCII 90 (Z) and ASCII 100 (d)

    Click image for larger version. 

Name:	ASCII.PNG 
Views:	13 
Size:	32.3 KB 
ID:	48784
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  11. #11
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    IIRC I did see 26 at times during my testing but can't remember which event. That's why I said I'd use the constants rather than the numbers. What's particularly odd at the moment is that if I just have this,
    Code:
    Private Sub Form_KeyPress(KeyAscii As Integer)
         MsgBox "KeyAscii  " & KeyAscii
    End Sub
    ctrl alone doesn't even fire it, so it seems like it's not the correct event. However, KeyDown does fire with ctrl press.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Ctrl-F problem
    By Gina Maylone in forum Access
    Replies: 16
    Last Post: 04-02-2016, 03:36 AM
  2. Replies: 5
    Last Post: 05-27-2014, 02:44 PM
  3. Print reports in a tab ctrl in a tab ctrl
    By marcsessoms in forum Reports
    Replies: 1
    Last Post: 02-18-2012, 12:15 AM
  4. Ctrl-Select problem
    By Maria in forum Access
    Replies: 3
    Last Post: 11-28-2011, 10:27 AM
  5. CTRL +' Short Cut Key
    By desireemm1 in forum Access
    Replies: 2
    Last Post: 09-10-2009, 03:25 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums