Okay, below is what we use in continuous forms.
Set these Properties for your form:
Key Preview Yes
On Key Down [Event Procedure]
Put this line in the event procedure:
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Call ContinuousUpDown(Me, KeyCode)
End Sub
The code below:
- responds only to the Up and Down keys;
- forces a save of the record (since there are bugs associated with trying
to move record without saving);
- does not move record if the active control is not in the Detail section;
- does not move record if the active control is a text box with its own
scrollbars or EnterKeyBehavior set;
- handles the common errors (cannot save, cannot move previous, etc.)
Replace the error logger call with your own, or use this one:
http://allenbrowne.com/ser-23a.html
------------------code begins---------------
Public Sub ContinuousUpDown(frm As Form, KeyCode As Integer)
On Error GoTo Err_ContinuousUpDown
'Purpose: Respond to Up/Down in continuous form, by moving record,
' unless the active control's EnterKeyBehavior is on.
'Usage: Call ContinuousUpDown(Me, KeyCode)
Dim sForm As String
sForm = frm.Name
Select Case KeyCode
Case vbKeyUp
If ContinuousUpDownOk Then
'Save any edits
If frm.Dirty Then
RunCommand acCmdSaveRecord
End If
'Go previous: error if already there.
RunCommand acCmdRecordsGoToPrevious
KeyCode = 0 'Destroy the keystroke
End If
Case vbKeyDown
If ContinuousUpDownOk Then
'Save any edits
If frm.Dirty Then
frm.Dirty = False
End If
'Go to the next record, unless at a new record.
If Not frm.NewRecord Then
RunCommand acCmdRecordsGoToNext
End If
KeyCode = 0 'Destroy the keystroke
End If
End Select
Exit_ContinuousUpDown:
Exit Sub
Err_ContinuousUpDown:
Select Case Err.Number
Case 2046, 2101, 2113, 3022, 2465
KeyCode = 0
Case Else
Call LogError(Err.Number, Err.Description, "ContinuousUpDown()",
"Form = " & sForm)
End Select
Resume Exit_ContinuousUpDown
End Sub
Private Function ContinuousUpDownOk() As Boolean
On Error GoTo Err_ContinuousUpDownOk
'Purpose: Suppress moving up/down a record in a continuous form if:
' - control is not in the Detail section, or
' - multi-line text box (vertical scrollbar, or
EnterKeyBehavior true).
'Usage: Called by ContinuousUpDown.
Dim bDontDoIt As Boolean
Dim ctl As Control
Set ctl = Screen.ActiveControl
If ctl.Section = acDetail Then
If TypeOf ctl Is TextBox Then
bDontDoIt = ((ctl.EnterKeyBehavior) Or (ctl.ScrollBars > 1))
End If
Else
bDontDoIt = True
End If
Exit_ContinuousUpDownOk:
ContinuousUpDownOk = Not bDontDoIt
Set ctl = Nothing
Exit Function
Err_ContinuousUpDownOk:
If Err.Number <> 2474 Then 'There's no active control
Call LogError(Err.Number, Err.Description, conMod &
"ContinuousUpDownOk()")
End If
Resume Exit_ContinuousUpDownOk
End Function
------------------code ends---------------
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Impexis said:
Hi Allen. Tks for preserving my sanity. I thought I was the crazy one! I
think if I use Single Form, then I can use PgDn to move to the same field on
the next record, but I lose the benefit of seeing the other records in the
area (lose context). I was wondering if I use Datasheet type form, I could
create an event upon Get Focus to empty the cell if it is already empty
(sounds dumb), but I don't even know at what precise moment (event) MS is
autofilling the cell. Can't risk emptying cells that are NOT already empty!
My solution, based on yours, is to have Continuous Form, and set in Tools,
Options, Keyboard, for the behaviour of the Enter key to move to Next
Record. NO, that doesn't work either, as the up & down arrows do the same as
the left & right arrows (ie move between fields, NOT between records). How
dumb!! I've been using Access for about 12 years, but never really had this
problem before. OK, I give up! Could I please have your code? Mark.