Dsum calculation is only picking up a single record

[randomaccess]

Hi,



I have a form embedded within my current form that allows multiple records and uses the table DCPOut. Each record has a qty and I am trying to get a total of the values for each record to appear in the Totals field of the current form (Me.totals). The current form has a field with an ID (Me.ID), this ID is also assigned to each record on tblDCPOut so where there are multiple records, they will share the same ID although they are of course assigned primary keys using another field in the DB.


Dim totalQty As Long
totalQty = DSum("qty", "tblDCPOut", "ID=" & Me.ID)
Me.totals = totalQty

In the case of the current ID there are 2 records on tblDCPOut but only 1 is being picked up and I cannot see why this is.

Please excuse my novice questions!

Any help is appreciated

[evander]

Can you create a GROUP BY query whose table source is tblDCPOut? Maybe it can give you a clue.

[Bob Fitz]

Hi

This is usually done like this:

Create a new field in the footer of the subform. I would name it "txtQtyTotal". Set the Control Source property of the new field to "=Sum([Qty])" without the quotes.

The textbox on your Main/Parent form that is to show the total needs to have its' Control Source property to somthing like "=YourSubFormName.Form!txtQtyTotal" without the quotes where YourSubFormName is the name of your subform.

[randomaccess]

Thanks Bob Fitz, I think I'm partially there.

I now have a Sum of the Qty for both records in my Sub form.

My Totals field needs to update the DB using the Control Source so I am trying to set it up so it does this via the Form On Load Event Procedure with the following code:

Me.totals = Form_frmDCPSub.txtQtyTotal

However, this does not update the form on load. In fact the totals field doesn't update with anything at all???

[Bob Fitz]

Hi

Me.totals = Form_frmDCPSub.txtQtyTotal

This expression doesn't look right to me.

I think it should be
Me.totals = YourSubFormName.Form!txtQtyTotal
where YourSubFormName is the name of your subform.

Check that you have set the Control Source property of the textbox called "totals" to "=YourSubFormName.Form!txtQtyTotal" then I think that is all you need.