Auto-compute on cells of Datasheet

[keirnus]

Hello,

I got something interesting which I hope it really exist.

If the title isn't understandable enough, I will explain further.

I have a SubForm in Datasheet view.
I set SubForm's Control Source with a SQL query in order to display a table of data.

Example:


Same as the automation in Excel, I want something to be implemented in Access.
Everytime a data in a single cell is changed (input/edit/delete) at runtime in Value column,
the data in Accumulating column will also change.
I tried setting the Control Source of the Accumulating textbox only and it works for a "single row".

Example:
Control Source =[txtValue]+1

But since it says "Accumulati ng", I wanna add the current Value data with the previous Accumulating data.

Like the one below:


I don't know yet how to set the Control Source.
This is where I am stuck.

How to get the row source of the previous record?
Or....is there a better way to mimic the automation in Excel?
(just accumulating the data by adding the current value with the previous value...just like in Excel but implemented in SubForm Datasheet view)

[missinglinq]

I wouldn't use the Control Source but rather would do the calculation in the txtValue_AfterU pdate() event:

Private Sub txtValue_AfterUpdate()
 Me.txtAccumulating = Nz(Me.txtValue.Value, 0) + Nz(Me.txtAccumulating.Value, 0)
End Sub

Linq ;0)>

[Stewart Ross]

Hi Keirnus. If what you are looking for is an overal total to be updated each time you add a record or change an existing one this can be done. If you are looking for a running sum that adds the previous row value onto the current one then that is not really practicable in an Access data entry form.

For an overall total, if you were displaying your subform in continuous forms view (not datasheet) you would have the form header and footer available to you. In these you can place an unbound textbox whose control source is set to a statement such as

=sum([price]*[unit cost])

or whatever would really represent what you are actually trying to achieve in your accumulator column. Datasheet view does not show the header or footer, unfortunately, but it is still possible to use an unbound textbox on the main form to accomplish this.

As mentioned, if what you are looking for is some way to carry forward intermediate totals from one row to the next - as can be done in Excel - it is not easily possible to do this in Access (or any other SQL-based database). In Excel it is very simple to refer to the previous row and calculate a running sum, say; SQL onthe other hand has no concept of record position, so such an intermediate running sum is not achieveable in the way you might expect if you are used to Excel.

Be aware also that datasheet or continuous form views only have one set of controls in use for the rows displayed, which means that you can't use unbound controls in such rows and get meaningful results displayed. We have an article on why unbound form control values don't persist which explains more than I can here on the reasons why.

-Stewart