Navigate subform using arrow keys

[Terry Holland]

I have an unbound form that contains two subforms. One of these subforms is
a continuous form comprising rows of textboxes. What I need is to allow the
user to navigate around the textboxes as if they are cells in an excel
spreadsheet using the arrow keys.
ie left key moves focus to control the left on the on same row
right key moves focus to control the right on the on same row
up key moves focus to same control on the on the row above
down key moves focus to same control on the on the row below

Im using the following code but getting an error on the line
DoCmd.GoToRecord acDataForm, Me.Name, acPrevious
The error description is "Runtime error 2489: The object 'MySubForm' isn't
open"



Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)

If KeyCode = vbKeyDown Then MoveDown
If KeyCode = vbKeyUp Then MoveUp
End Sub

Sub MoveDown()

Dim ctlFocus As Control
Set ctlFocus = Screen.ActiveControl '.Name
DoCmd.GoToRecord acDataForm, Me.Name, acNext
ctlFocus.SetFocus
End Sub

Sub MoveUp()

Dim ctlFocus As Control
Set ctlFocus = Screen.ActiveControl
DoCmd.GoToRecord acDataForm, Me.Name, acPrevious
ctlFocus.SetFocus
End Sub

 

[Allen Browne]

The Forms collection contains a list of the forms that are currently open.
When you open a form with a subform, Access adds the form to the Forms
collection, but the subform is not added to the Forms collection. That's
what it means when it says the subform is not open, i.e. it is not open in
its own right.

You will therefore need to ensure that the focus is in the subform (i.e.
that it is the active control of the main form), and you can then use
GotoRecord. If these 4 arrow buttons are on the main form, you will need
code like this:
Me.[Sub1].SetFocus 'Make sub1 the active control on the main form
Me.[Sub1].Form![Text1].SetFocus 'Set focus to something in the subform.
RunCommand acCmdRecordsGotoNext

There are several pitfalls in that approach, e.g. what to do if:
a) the subform cannot move record because the current edits cannot be saved
(e.g. required field missing)

b) it is already at the last record. (Move to new?)

c) is is already at the new record

d) the active control is in the Form Header or Footer

e) the active control is a dropped down combo or multi-line text box.