David, the code below causes a continuous-view form to behave like a
datasheet when you press the Up or Down arrow.
To use it:
1. Paste it into a Standard Module (one created through the Modules tab
of the Database Window.
2. Set these properties for your form (i.e. the one that is the
subform):
KeyPreview yes
Key Down [Event Procedure]
3. Then click the Build button (...) beside the KeyDown property.
Access opens the Code window.
Between the "Private Sub ..." and "End Sub" lines, enter:
Call ContinuousUpDown(Me, KeyCode)
The Up/Down arrow will move record provided:
a) The active control is in the Detail section (not in the Form Header
or Form Footer.)
b) The active control is not a multi-line control.
c) Any edits in the current record can be saved (e.g. not violating a
validation rule.)
Replace the error handler call with your own, or use this one:
http://allenbrowne.com/ser-23a.html
Public Function 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)
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 Function
Err_ContinuousUpDown:
Select Case Err.Number
Case 2046, 2101 'Already at first record, or save failed.
KeyCode = 0
Case Else
Call LogError(Err.Number, Err.Description, "ContinuousUpDown()")
End Select
Resume Exit_ContinuousUpDown
End Function
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:
Call LogError(Err.Number, Err.Description, conMod &
"ContinuousUpDownOk()")
Resume Exit_ContinuousUpDownOk
End Function
I used the Wizard to create a tabular form based on a query. The tabular
format looks good but the up and down arrows don't move me up to the
previous record or down to the next record - they move me left or right.
Can I fix this? I want the same key behavior as the datasheet view, but
I don't want to use that view.
Also, I'm having trouble with subforms. They don't "fit" very well in
datasheet view. When there aren't enough rows to fill the subform there
is an ugly blank area at the bottom. Also, when there are not enough
rows the vertical scrollbar is gone leaving another ugly blank space in
its place. Is there anything I can do to get rid of these blank spaces
and get a better "fit" of the subform?
