Update records with calculated results.

[Guest]

I have a desktop database made of two tables I use to track stock trades.

tblTrades contains a primary key of ID (autonumber) and other fields including Gain/Loss, Return, Days.
tblLots contains a primary key of ID (autonumber) and other fields including TradeNumber (foreign key to tblTrades), Date, Shrs, Price, Commission.

I have a form and subform (frmTrades, frmLots). The RecordSource property of frmTrades is set to tblTrades. The RecordSource for frmLots is set to Lots. The subform's (frmLots) LinkMasterFields property is set to ID and the LinkChildFields property set to TradeNumber.

It is exactly what I need and works great in most ways except one. I calculate the gain or loss on the subform footer, and I have written a macro to run update queries to populate Gain/Loss, Return, and Days in tblTrades. However, I'd like to do the calculations realtime on the forms and populate the fields in tblsLots and tblTrades realtime.

I hate to admit I can't do something, but I have spent several man-days searching documentation, reading books and discussion boards and trying various combinations of record sources (using tables and queries), link fields, event procedures and VB code, but nothing I have tried does the job. Once I figure out what the key elements are, there are other similar calculation and record update procedures I'd like to add.

I could desperately use some guidance on what the key elements are to properly linking the tables, nesting the forms, setting the properties and updating fields in both tables with calculations.

Thank you.

 

[Bob]

Calculated fields aren't bound to any tables and therefore
don't save. You'll have to create an unbound control to
run the calculation and then use VB code or a macro to set
the value of your bound control for that field equal to
the value of your calculated control.

Use the BeforeUpdate event something like:

Forms!MyForm!GainLoss = Forms!MyForm!CalculatedGainLoss

-----Original Message-----
I have a desktop database made of two tables I use to track stock trades.

tblTrades contains a primary key of ID (autonumber) and

other fields including Gain/Loss, Return, Days.

tblLots contains a primary key of ID (autonumber) and

other fields including TradeNumber (foreign key to
tblTrades), Date, Shrs, Price, Commission.

I have a form and subform (frmTrades, frmLots). The

RecordSource property of frmTrades is set to tblTrades.
The RecordSource for frmLots is set to Lots. The subform's
(frmLots) LinkMasterFields property is set to ID and the
LinkChildFields property set to TradeNumber.

It is exactly what I need and works great in most ways

except one. I calculate the gain or loss on the subform
footer, and I have written a macro to run update queries
to populate Gain/Loss, Return, and Days in tblTrades.
However, I'd like to do the calculations realtime on the
forms and populate the fields in tblsLots and tblTrades
realtime.

I hate to admit I can't do something, but I have spent

several man-days searching documentation, reading books
and discussion boards and trying various combinations of
record sources (using tables and queries), link fields,
event procedures and VB code, but nothing I have tried
does the job. Once I figure out what the key elements
are, there are other similar calculation and record update
procedures I'd like to add.

I could desperately use some guidance on what the key

elements are to properly linking the tables, nesting the
forms, setting the properties and updating fields in both
tables with calculations.