Posted on 2006-04-25
Last Modified: 2008-01-09
I have a form and in it I have a subform in datasheet view.

And when I enter a new record in datasheet view a record is automatcily entered in the corresponding table.

Now as soon as key is pressed in the "PRODUCTID" colum, a record is created, but if the user hits esc, it delets the record from datasheet view, but the record still exisits in the master table.

Is their a way I can disable the esc key....

instead i would like to create a delete button and delete the record instead of the esc key...

i can make the button, but wondiner how to delete the esc key.

Question by:Ricky11
    LVL 77

    Expert Comment

    Hi Ricky11,
     'but if the user hits esc, it delets the record from datasheet
     view, but the record still exisits in the master table.'

    I'm sorry but you are wrong.  Hitting escape twice while you creating a new record removes the record from the screen and since it has never been saved it never gets into the table.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    I'd say that you're missing a piece of infomation that Pete has just supplied you with.

    Hitting Escape Twice.
    Once will undo - twice will abandon the entry of the current new record.

    However - you've got a subform and you're open to further complications.
    I'd guess you're moving to a new record in the main form - and then entering a sub record.
    This is generally a bad idea, can even lead to orphaned records and if you lose focus to the main record for any reason then you're sub record is committed.

    Do things sequentially, and in a deliberate manner and all should go fine.

    Author Comment

    My datasheet view has a field called product id.

    On the main form, I have a field called add record. if the button is pressed rs.addnew is initiated and a entry is made in the products table... then the form will call the newly created record and pull it up in datasheet view..   so the record is created and then entered in datasheet view.. items in this subform datasheet view get stored in another table.

    if the user presses esc, pete is correct, the info does not get stored in the subform datasheet view table.. however becuase of my code rs.addnew a record is created in the main products table... so if the user preses esc, even after the row is finished, the record in the products table is still there but orphaned.

    this is very similar to who the northwind default database works in msaccess they have a products table and then a order details table...

    however i have modified it to enter purchases as well as sales.

    ... back to my original post is their anyway to disable the esc undo key?

    LVL 58

    Accepted Solution


    Just trapping the [Esc] key is easy. So is Ctrl+Z (undo). But if you leave the custom menus, some sort of undo will still be available. Anyway:

    In the form properties, set "Key Preview: Yes" and "On Key Down", add this:

    Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
        If KeyCode = vbKeyEscape Then KeyCode = 0
        If KeyCode = vbKeyZ And (Shift And acCtrlMask > 0) Then KeyCode = 0
    End Sub

    LVL 44

    Expert Comment

    by:Leigh Purvis
    Oh yeah - that was the question I suppose wasn't it? :-)
    Forgot about that lol.

    If you add your records first - and then view them then there's no chance of a user undoing it, leaving you with orphaned records.

    Alternatively you might consider Cancelling the Undo events in your main and sub forms?
    A somewhat bold strategy - but you could slightly protect yourself with

    On Error Resume Next
    If Me.NewRecord Then Cancel = True

    (The error handling, albeit it blanketing, often a good idea when cancelling an event)

    Author Comment

    harfang thanks for the answer.. just wanted i needed.

    I guess i canr remove the menu bar altogether...

    LVL 44

    Expert Comment

    by:Leigh Purvis
    <mental note to actually attempt to answer the actual question in future :-) >

    Hijacking Q for a quick word with Harfang if that's OK?  
    (We're big on hijacking here - but it's relevant I reckon - read on for fun).

    Markus, do you usually do that sort of thing with a bitwise comparison?
    (Rather than just
    If KeyCode = vbKeyZ And Shift = 2 Then)

    Is that to deliberately catch *any* ctl/shift/fn key combo  -  as it will also capture and disable combinations like "ctl shift z" won't it?
    (though it's sooo unlikely that there's an Autokeys with that particular option :-)

    But in the example won't it also disable the shift key for capitalization?
    Did you mean to have the bracket a bit earlier
    If KeyCode = vbKeyZ And (Shift And acCtrlMask) > 0 Then KeyCode = 0
    to force the comparison with the constant before the greater than 0?

    Just a thought.  :-)
    LVL 58

    Expert Comment


    Leigh's comment above is not "just a thought", it's a correction of a bug in my two (!) lines of code.
    Playing the "VB Line Interpretor"...:

        If KeyCode = vbKeyZ And (Shift And acCtrlMask > 0) Then   ' apply operator precedence:
        If KeyCode = vbKeyZ And (Shift And True) Then                 ' "... And True" does nothing:
        If KeyCode = vbKeyZ And Shift Then                                 ' tranlastion:
        "if the key [z] is pressed whith *any* combination of shift  keys, then..."

    Observe that your form does not allow the upper case 'Z' any more!
    In other words, please use the correction:

        If KeyCode = vbKeyZ And (Shift And acCtrlMask) > 0 Then KeyCode = 0

    Also look up help on "KeyDown, KeyUp Events" and look at the "Event Procedures" section, which contains exactly this example, as a matter of fact.

    Sorry about that!

    <now some more "hijacking", if you don't mind>


    The fact that VB does not distingush between "logical And" [&&] and "binary And" [&] has always caused problems for me. Partly because you get integers when you expect booleans, and partly because these operators have different positions in my mental operator precedence list.

    In my test code, I had written:

        If KeyCode = vbKeyZ And Shift And acCtrlMask Then


        if (KeyCode == vbKeyZ && Shift & acCtrlMask) { ...

    But getting:

        if (KeyCode == vbKeyZ) & Shift & acCtrlMask)) { ...

    Which does the same, but by mere luck, IMHO.

    So I thought "this is not good practive for an EE post", and then "let's add brackets the an explicit <> 0 test". I did, but got caught again.
    I just can't accept that VB has only a bit-wise And, but at the precedence level of the logical And! Soooo illogical for logical expressions.

    (OK, I'm obviously just trying to justify myself, and you probably reconstructed most of the above when you saw the misplaced  bracket... ah well...)

    Anyway, thanks for the correction!

    LVL 44

    Expert Comment

    by:Leigh Purvis
    Hey, any excuse for a good hijacking ;-)

    It is a shame you have end up bracketing the statements to death isn't it?  Just to be sure.  Then all it takes is one misplaced one and it's kaput. :-S

    Oh well - sandwich time.

    Author Comment

    Wow. Thanks Your Right.

    I wish I could split the points becuae Lpurivs desrves 200 of the 500 for the correction.

    Anyway Thanks.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Nooo don't give it a second thought.
    Happy to help.

    (If Markus and I tried to keep count of each other's catches we'd never get any work done lol)
    Not that we ever make mistakes of course :-S

    (I hate being fallible, it's so inconvenient)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now