Query with data from subform and sum

Question

I have three tables:

Products(ID,Name,Price) / 
Customer(ID, Name, Surname)
Buys(ID, ID_customer, ID_product)

I have a form with subform. Subform gets populated with query from Buys table and then connected to Customer(ID) via ID_customer. ID and ID_customer gets hidden on subform.

Then I have two more fields/controls added on subform: Name and aPrice which gets populated via ProductsQuery:

SELECT Products.Name, Products.Price
FROM Products
WHERE (((Products.ID)=[Forms]![PregledKupcev-Form]![NAKUPI-Subform]![ID]));

//ID in this case is control on subform which holds ID of a product

using:

=DLookUp("[Name]";"[ProductsQuery]")

and

=DLookUp("[Price]";"[ProductsQuery]")

So far everything works but it gives me alot of troubles later when i try to sum one control (Price in this case).

Is there any way to do this better?

Then I try to sum up things in aPrice control into PriceSum control on subform's footer:

=Sum([Forms]![PregledKupcev-Form]![NAKUPI-subform]![aPrice])

and transfer it to form with:

=[Forms]![PregledKupcev-Form]![NAKUPI-subform]![PriceSum]

but I get error..

How do I sum up values in Price control on subform?

Pictures: Form

Gord Thompson Gord Thompson · Accepted Answer · 2013-03-24T12:06:09

Let's say that your main form has a text box named txtInvoiceNo in which you display the Invoice Number (or whatever field links your parent table to your child table).

Let's also say that your main form has a text box named txtInvoiceTotal where you want to display the sum of the [Price] values for each child record.

Set the Control Source of the txtInvoiceTotal text box to do a DSum() on the child table (which I've called InvoiceLineItems):

=DSum("[Price]","InvoiceLineItems","InvoiceNo=" & [txtInvoiceNo])

In the After Update event of the subform, add a line to .Requery the parent form's txtInvoiceTotal text box:

Private Sub Form_AfterUpdate()
Me.Parent.txtInvoiceTotal.Requery
End Sub

See if that does the trick for you.

Query with data from subform and sum

1 Answers