How to make a datasheet work like a spreadsheet?

I'd like to be able to do in-cell editing of a datasheet and have other fields
automatically update. I know... the easy answer is to use Excel, but I'm
wondering if I can pull this off in Access since all my data is in tables.

For example, I have a SubForm Datasheet with these text boxes:

txtAccountNumber
txtCurrentBalance
txtPendingTransactions
txtLedgerBalance


The RecordSource of the Datasheet is a Table - tblAccounts - which has all the
Account Numbers. I have a query that totals up all the Pending Transactions,
and txtCurrentBalance is bound to a field in tblAccounts.

If I put this in txtLedgerBalance:

=DLookUp("TotalPending","qryBudgetPendingTotals"," Payment_ID=Forms!frmBudget!frm
Budget_AcctsDatasheet.Form!txtAccountNumber")

txtLedgerBalance is calculated for each row in the Datasheet, and updates after
I make a change in txtCurrentBallance

But how to total all txtLedgerBalance text boxes? The constraints as I see
them are this:

* I have to use a table as a record source for the datasheet so I can "in-cell
edit"
* I have to use DLookup in txtLedgerBalance to get a spreadsheet effect that
calculates each row of the datasheet

Is there a better way to go about this? Should I do the calculations in queries
or VBA code in the Form's module? Both?

any pointers welcome...

re: How to make a datasheet work like a spreadsheet?

deko wrote:
[color=blue]
> I'd like to be able to do in-cell editing of a datasheet and have other fields
> automatically update. I know... the easy answer is to use Excel, but I'm
> wondering if I can pull this off in Access since all my data is in tables.[/color]


Create a query as source for your datasheet, and include the
calculations in that query. Maybe you'll have to do some manual
instigation when you edit fields (like me.recalc or so)

--
Bas Cost Budde
http://www.heuveltop.nl/BasCB

re: How to make a datasheet work like a spreadsheet?

> Create a query as source for your datasheet, and include the[color=blue]
> calculations in that query. Maybe you'll have to do some manual
> instigation when you edit fields (like me.recalc or so)[/color]

The Recalc method is the way to go - thanks!