Subform Navigation/Tabbing Problem

[ExcelMan]

I have a nested datasheet subform (actually nested 3 layers deep) that
the user enters data into. The user tabs or hits enter to move from
field to field. When the user gets to the last field in the subform
and hits ENTER or TAB the focus pops out to the grandparent form.

I have the Cycle property for the subform set to Current Record. It is
also set to Current Record in the Parent and GrandParent forms.

When the user hits TAB or ENTER in the subform I want the focus to go
to the next record in the subform. This may often be a new record.

Most of my efforts thusfar have centered around trying to set the focus
to the first control on the subform after the last one is updated, but
this doesn't seem to work. I either get crashes or it still pops out
to the grandparent form.

So, How do I keep the focus in the subform, moving to the next record,
new or existing, when the user hits TAB or ENTER after having TABBED
to, ENTERED to, or updated the last control?

Thanks in advance for your help.

 

[Marshall Barton]

I have a nested datasheet subform (actually nested 3 layers deep) that
the user enters data into. The user tabs or hits enter to move from
field to field. When the user gets to the last field in the subform
and hits ENTER or TAB the focus pops out to the grandparent form.

I have the Cycle property for the subform set to Current Record. It is
also set to Current Record in the Parent and GrandParent forms.

When the user hits TAB or ENTER in the subform I want the focus to go
to the next record in the subform. This may often be a new record.

Most of my efforts thusfar have centered around trying to set the focus
to the first control on the subform after the last one is updated, but
this doesn't seem to work. I either get crashes or it still pops out
to the grandparent form.

So, How do I keep the focus in the subform, moving to the next record,
new or existing, when the user hits TAB or ENTER after having TABBED
to, ENTERED to, or updated the last control?

All you should need to do is set the subform's Cycle
property to All Records.

How it could jump to the grandparent form, without you
having some code to make it do that, is beyond me.
Definitely not normal.

 

[ExcelMan]

Marshall, thanks.

Yes, I started out with the Cycle property set to All Records but put
it to Current Record on the off chance this might "hold" the cursor on
the form. Of course you are right since I want the cursor to move to
the new record.

But your other comment made me think and I seem to have isolated where
the problem is occuring. In the After_Update event of the subform
(grandchild) I requery a calculated text box on the grandparent form.
(These forms are of the nature of the typical customer, order, order
details and the text box contains the total value of the order.) The
text box is populated with a DLookup function in the controlsource
property that executes a stored query and grabs a field from that
query.

Anyway, when I comment out the requery, the cursor stays in the subform
and behaves as you said. The requery seems to cause it to bounce to
the grandparent. So I've tried bringing it back with variations on the
use of the SetFocus method, but even when the syntax is accetable,
executing the SetFocus method on the subform after the requery in the
Subform After_Update event causes the application to crash and exit
Access.

I need the box to recalculate after each update in the subform as well
as numerous other times during the use of the grandparent, parent and
child forms. Any ideas how to get the functionality I am looking for?

Thanks again.

 

[Marshall Barton]

Yes, I started out with the Cycle property set to All Records but put
it to Current Record on the off chance this might "hold" the cursor on
the form. Of course you are right since I want the cursor to move to
the new record.

But your other comment made me think and I seem to have isolated where
the problem is occuring. In the After_Update event of the subform
(grandchild) I requery a calculated text box on the grandparent form.
(These forms are of the nature of the typical customer, order, order
details and the text box contains the total value of the order.) The
text box is populated with a DLookup function in the controlsource
property that executes a stored query and grabs a field from that
query.

Anyway, when I comment out the requery, the cursor stays in the subform
and behaves as you said. The requery seems to cause it to bounce to
the grandparent. So I've tried bringing it back with variations on the
use of the SetFocus method, but even when the syntax is accetable,
executing the SetFocus method on the subform after the requery in the
Subform After_Update event causes the application to crash and exit
Access.

I need the box to recalculate after each update in the subform as well
as numerous other times during the use of the grandparent, parent and
child forms. Any ideas how to get the functionality I am looking for?

Displaying the subsubform's total is usually simple in the
subsubform's footer section. Just use a text box with the
Sum function.

However, getting that value back to the main form can be
tricky. I would not have tried using DLookup on a query to
do it so you're in unknown territory for me.

I know that trying to use any normal kind of code in an
event procedure to copy the total from the subsubform to the
main form won't work.

Let's try having the main form text box refer to the
subsubform total text box and see what happens. Create a
text box named txtTotal in the subsubform footer section and
set its expression to something like:
=Sum(price * quantity)
where each of the terms in the sum expression are fields in
the subsubform's record source table/query, not controls on
a form.

Then set the main form's text box to this kind of thing:
=subform.Form.subsubform.Form.txtTotal

 

[ExcelMan]

Marshall,

I can't so the simple Sum(price*quantity) because I'm really searching
a total over all the grandchildren (i.e., the total purchases of the
customer, not just the total on the current order) so I need a query to
go back into the database.

But I did find that the text box and the subform will work properly if
I remove the requery from the after update event and place the text box
on the subform rather than on the grandparent form. It seems to update
automatically that way and the cursor moves around the subform the way
you would expect with Cycle set to All Records.

Now, for visual appeal I really want to place the textbox elsewhere,
but this will do. I'll give your referencing approach that you
mentioned in the last sentence a try. My initial attempt at it didn't
work, but I may have some syntax problems.

Thanks again for all your help.