CODEWITHSUNDEEP
functionms-access
xris23
xris23

Reputation: 353

Access Totals dynamically updated based on textboxes

All,

I run into some problems with totals using the DSUM function.

I have a Main form and a subform (details). The subform is displayed in datasheet view inside of the main form.

The subform contains a OppL_ID (PK, autonumber), Opp_ID (FK, is multiple times present), Product, Unit, Unit price. Now I want to calculate the total revenue per Opp_ID on the main form. In the main form an Opp_ID selected, several rows with products are shown in the subform connected with that Opp_ID. If the user enters data in the datasheet view, the revenue must be calculated again directly.

I first try to get the formula working in the footer of the subform. But I cannot get the correct sum of revenue. Now I have added in the query of the subform the Revenue: [Units]*[Unit Price]. This displays the revenue per unique line. I want the revenue per Opp_ID, so I tried the DSUM function.

What I tried:

  1. To total the Revenue per Opp_ID I used the formula: DSUM([Units]*[Unit Price];"Qry_lines";"[Opp_ID]=" & [Opp_ID])
  2. I also tried the control names instead: DSUM([txt_Units]*[txt_Unit Price];"Qry_lines";"[Opp_ID]=" & [Opp_ID])
  3. I aslo tried to reference the Revenue field: DSUM([Revenue];"Qry_lines";"[Opp_ID]=" & [Opp_ID])

All of the formulas end in the result:

Example:

Opp_ID 51 consists of two lines with products A and B, Line of product A = Units 1 Unit price 20. Line of product B = Units 2 unit price 30.

Result: If I stand on A, the total revenue is 40 and on the line of product B 120.

I would expect only one figure: 1 * 20 + 2 * 30 = 80

It looks like it calculated revenue times the number of rows? What am I doing wrong?

I also tried to create a sum query and to reference this, for example:

Select Qry_lines.Opp_ID, Sum(Qry_lines.Revenue) as SumofRevenue FROM Qry_lines Group by Qry_lines.Opp_ID.

Now I get the correct figure per Opp_ID. Only I cannot reference this in my form? Perhaps with a Dlookup?

I think I am close but I cannot really nail it. Please help :)

Upvotes: 0

Views: 71

Answers (1)

June7
June7

Reputation: 21379

Consider:

Use DLookup() domain aggregate function expression in textbox to reference the aggregate query.

Commit record edits to table with code in control's AfterUpdate event. One way is If Me.Dirty Then Me.Dirty = False

In same event, use Refresh or Requery or Recalc command on main form.

Upvotes: 1

Related Questions