Variant of the subform/Tab key problem

[Aino]

I have a variant of the classic question of how to go from a subform
to the main form using the tab key. Here is the twist:

My subform must always have exactly 5 records, so whenever a new
record is created in the main form, I create the 5 new records in the
subforms table with code, hence it must be possible to add records.
The subform is continuous and with all the settings to make the user
unaware, that the whole thing is not just one form. The subform has no
navigation buttons, no scroll etc. and is just big enough to
accomedate 5 rows. When tabbing through the form, the user would enter
the subform and tab through each control in each row (record).

When pressing the tab key in the last control (a textbox) in the last
record the user should enter the next control after the subform. Right
now what happens is, a new 6th record is added, and my subform is
scrolled down to show this.

Any ideas?

Thanks
Aino

 

[Guest]

Well, if you add your records programmatically using VBA from code in the
main form, you won't have to allow ADD NEW in your subform. You could
actually create a "single record" subform that would display all five records
for editing. To me, that'd be the best and easiest method.

 

[Aino]

If I don't allow record additions to the subform, the code in the main
form (see below) that adds them won't run. And if the subform is
"single record", I don't see all 5 lines?

Private Sub txtRutenummer_AfterUpdate()

On Error GoTo ErrorSub
If Me.NewRecord Then
If Me![txtRutenummer] > 0 Then
DoCmd.GoToControl "Rute Underformular"
Forms![Rute]![Rute Underformular]![txtUgedag_ID] = 1
Forms![Rute]![Rute Underformular]![cmbLeverandoer] = 1
DoCmd.GoToRecord , , acNewRec
Forms![Rute]![Rute Underformular]![txtUgedag_ID] = 2
Forms![Rute]![Rute Underformular]![cmbLeverandoer] = 1
DoCmd.GoToRecord , , acNewRec
Forms![Rute]![Rute Underformular]![txtUgedag_ID] = 3
Forms![Rute]![Rute Underformular]![cmbLeverandoer] = 1
DoCmd.GoToRecord , , acNewRec
Forms![Rute]![Rute Underformular]![txtUgedag_ID] = 4
Forms![Rute]![Rute Underformular]![cmbLeverandoer] = 1
DoCmd.GoToRecord , , acNewRec
Forms![Rute]![Rute Underformular]![txtUgedag_ID] = 5
Forms![Rute]![Rute Underformular]![cmbLeverandoer] = 1
DoCmd.GoToRecord , , acFirst
End If
End If

GoTo ExitSub

ErrorSub:
MsgBox Err.Description

ExitSub:

End Sub

The field txtUgedag_ID is invisible, but its value is a foreign key in
the tabel behind the subform, and I also use it as the control source
for the next field =DLookUp("[Navn]";"Ugedage";"[Ugedag_ID] = " &
[Forms]![Rute]![Rute Underformular]!txtUgedag_ID) which is a label-
looking textfield that ends up displaying monday to friday for each
row, where the user then adds/edits data.

cmbLeverandoer links to yet an other table with a 1 to many relation,
which is why I have to give it a value.

Thanks
Aino