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.

Who is Participating?

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

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.

Leigh PurvisDatabase DeveloperCommented:
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.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Ricky11Author Commented:
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?

Leigh PurvisDatabase DeveloperCommented:
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)
Ricky11Author Commented:
harfang thanks for the answer.. just wanted i needed.

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

Leigh PurvisDatabase DeveloperCommented:
<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.  :-)

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!

Leigh PurvisDatabase DeveloperCommented:
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.
Ricky11Author Commented:
Wow. Thanks Your Right.

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

Anyway Thanks.
Leigh PurvisDatabase DeveloperCommented:
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)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.