SubForm Blues

[twig]

I am constructing an order entry system using a form 'frmOrder' and subform
'frmOrderLine'.

The subform is linked to the mainform using 'OrderNo' and has a primary key
of 'OrderNo' and 'LineNo'.
'LineNum' is calculated so that each order starts with line 1

The subform has the usual 'Qty', 'Description' and 'Price', plus two
calculated fields, first change currency to 'RMPrice' using an exchange rate
lookup from the main form 'Supplier', then multiply by 'Qty' to get
'LineTotal'.

The subform has a hidden textbox 'OrderTotal' with a control source
Sum([LineTotal]).

The main form has 'Lines Count' also used to calculate line numbers (new
'LineNo' = 'LinesCount' + 1) and 'Order Total', which is copied from the
subform, plus some other calc fields such as deposit required. I need the
main form to display running values as items are entered or edited. I also
wish to store the LineCount and OrderTotal in tblOrders (yes, I know its not
'normalised')

In order to keep all the calculations always up to date, I perform them all
in a procedure 'UpdateTotals' called from the AfterUpdate Events of subform
'Qty' and 'Price'. The procedure calculates and posts the order line
values, then Me!Recalc appears to be necessary to keep subsequent
calculations good, then calculate and post parent form values.

It all seems to work... but here's the rub. When I go back to an earlier
line to edit a field then press tab to move on, the cursor does not change
focus to the next field (because of the Recalc I suppose). I have kludged
this in Qty_AfterUpdate by using SetFocus. however the following tab from
the 'Price' field will be 'Qty' in the next subform tentative add line. I
don't know how to move the focus there! Is there a SetFocus.Next?

Any suggestions, or is there a better way to do the whole thing!

Regards

Twig

 

[Van T. Dinh]

If you look in the Orders Form / Orders Subform in the sample database
"NorthWind", you will find the similar set-up but Microsoft hardly had to
use code to do the same thing.

 

[twig]

Thanks forv the pointer, but the Northwind fails my requirements in two
ways... which is what drove me to code in the first place:

1) The Order table does not store the calculated OrdersTotal value, so can
have its control source look up the subform total.

2) The subform and main form calculations are not always updated after a
value is edited. That does not happen until the focus moves out of the
subform line

Regards

Twig