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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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?

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!


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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.