Prevent moving to next record when hitting Enter?

Question

I have a form in Access 2003 that should only be working with a single record. I can set the Cycle property to Current Record, but the form still jumps to the next record when I press Enter. My first thought was a KeyPreview property, but I'm not seeing one. My other thought is maybe the KeyPress or KeyUp event, but I thought I'd ask in case of unintended consequences. Any ideas?

Vickery

If you are going to down vote and (I assume the same person) vote to close. Please comment on why. I regularly get accused of being close happy and even I don't see the problem here. – Diversion

I don't see the problem either. This seems like a legitimate programming question. It just needs a better title - like adding "in Access VBA" to the end. – Schiffman

Really? I would think that the tags were sufficient. Duplication of data is usually a bad thing. – Diversion

I figured the tags are there for a reason. I also posted this because I couldn't find an answer Googling. "Access" and "enter" are too generic. – Vickery

I haven't done Access VBA for years so I'm not comfortable putting this as an answer. When I had to do this I essntially used the OnKeyPress event I believe. – Diversion

Answer 1

The Cycle property only affects the TAB key.

To control the behaviour of the Enter Key that's a global property.

Go to Tools/Options - Keyboard tab and on "Move After Enter" select "Next Field"

There are KeyPress and KeyDown events that you can use to trap the Enter key too but that's more work.

Alliber

Funny, it never occured to my that my "user preference" was affecting my application. Fortunately, my users are probably not savvy enough to go digging through the options. This works for me. Thanks. – Vickery

Won't that affect the whole application. What if you just want to do that on just some forms, not all? Isn't "Next field" the default behavior too? – Unamuno

You should probably have your app check the setting for this option, using GetOption and if it's not what you want, set it with GetOption. If you want to return the user's setup to it's original setting, you'll want to cache the original value and reset when the app closes. – Herby

The Cycle property works with Enter as well as Tab now. The documentation makes no mention of it, but I just tried it. – Titfer

Answer 2

This can also be done in vba.

Application.GetOption "Move After Enter" 'get current setting
Application.SetOption "Move After Enter", 0 'don't move
Application.SetOption "Move After Enter", 1 'Next Field
Application.SetOption "Move After Enter", 2 'Next Record

http://www.pcreview.co.uk/forums/enter-key-moving-next-field-t3454281.html

Decussate

Answer 3

The moment keys like TAB, Alt, PgUP, PgDn, Enter are pressed at the end of adding the record (after the last field mostly), the database auto saves all the info you entered on the form and wipes out the fields so that you can enter the next value. So what happens is that the data is saved but the form looks empty.

3 things to do:

1. Form Cycle Property set to Current Record.
2. Form Key Preview Property set to Yes.

3. Add following code to the KeyDown Event of the form:

   '33 - PgUp; 34 - PgDown; 9 - Tab; 18=Alt; 13=Enter
   
   Select Case KeyCode
   Case 33, 34, 18, 9, 13
   KeyCode = 0    
   Case Else
   'Debug.Print KeyCode, Shift
   End Select
   

   I found this while scouring the web and don't take credit/responsibility for the code but I don't know where I found it. Works for me!

Amigo

Answer 4

The Cycle property only works with the Tab key.

There are two options you could pursue.

You could trap the Enter key in KeyDown/KeyUp/KeyPressed
- OR -
You could filter the data source to the one record you want them editing, and disable adding new records through that form.

Unamuno

Answer 5

You can add below code to your form 'BeforeUpdate' event. If use want to move to next record, it will ask user to save then close the form before they can move to another recorde.

  Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("Save?", vbYesNo)
      Case vbYes
        DoCmd.Close
      Case vbNo
        Cancel = True
    End Select
  End Sub

Egwin

This code only fire if the record has been updated, so it's not really a solution to the original problem at all. – Herby

Answer 6

If you go into Access Options on the file page, go to Client Settings and the first setting will let you choose where your focus changes to when you press enter. At least in Access 2013.

Debauch

Answer 7

If this is in a form, and the main options tab is unhelpful, that's because it's a field property.

I had the same issue and I found that in the access version i'm using (365 i think??) the options in the main 'options' menu are limited to 'don't move', 'move to next field' and 'move to next record' which didn't help me.

After clicking around a bit i found that if you click on the feild you're looking at in the form, right click and go to 'properties' (not 'form properties') you'll see 'enter key behaviour' in the 'other tab' which allows you to move to the next line in the field for multi-line fields.

Vizzone