Calculating Subtotal in Textbox from Results from multiple rows in subform.

[mdschuetz]

I have a Form, frmPurchaseOrders

On this form I have an object txtSubtotal

Also, this Form contains a subform which pulls applicable results from tblInventoryTransactions

The fields in the subform are txtUnitsOrdered, txtUnitPrice, txtPartNumber, txtTransactionDate, txtItemDescription

This is the calculation I want to perform.

Row1: txtUnitsOrdered * txtUnitPrice +
Row2: txtUnitsOrdered * txtUnitPrice +
Row3: ect....

I want these results displayed in txtSubtotal on frmPurchaseOrders

Can someone please give me a clue on where to start and what I need to do?

Thanks,

Marty

 

[mdschuetz]

I would think the expression builder would allow me perform this. However, I'm having no luck getting more than the first row to calculate.

=[Purchase Orders Subform].Form!UnitsOrdered*[Purchase Orders Subform].Form!txtPrice

What am I doing improperly?

 

[neileg]

I would add another control for each row and calculate the txtUnitsOrdered * txtUnitPrice and then sum this control. If you dont want it visible to the user, set the Visible property to No. Access can still see it!

 

[mdschuetz]

I would add another control for each row and calculate the txtUnitsOrdered * txtUnitPrice and then sum this control. If you dont want it visible to the user, set the Visible property to No. Access can still see it!

What do you mean add another control for each row? each record will have multiple rows which are unique to that record and are not contiguous in the amount of rows for each record.

Can you elaborate a little please so I may better understand what you are suggesting?

Thanks,

Marty

 

[neileg]

Isn't your subform a continuous form based on tblInventoryTransactions? If not, what is it? Can you post a screen shot?

 

[mdschuetz]

 

[mdschuetz]

Isn't your subform a continuous form based on tblInventoryTransactions? If not, what is it? Can you post a screen shot?

datasheet view I think

 

[mdschuetz]

=DSum([Purchase Orders Subform].Form!UnitsOrdered*[Purchase Orders Subform].Form!UnitPrice,[InventoryTransactions],[Purchase Orders Subform].Form!PurchaseOrderID=[Me.PurchaseOrderID])

What am I doing wrong? It seems that the expression is built properly, but it returns a "#Name?" error.

Ive checked and I believe that I've made sure that the field specified in the control's ControlSource property hasn't been removed from the underlying table or record source.Checked the spelling of the field names in the control's ControlSource property. I've specified an expression in the control's ControlSource property and made sure that there is an equal sign preceding the expression.

I'm at a loss.

Marty

 

[neileg]

DSum doesn't work on forms, you use Sum.

I don't use datasheet view, as I prefer continuous forms, but that's your choice. You need to add a calculated field to the query that your datasheet is based on and carry out the multiplication there. Add a control to your subform footer that has as its datasource =Sum([WhateverYouHaveCalledTheCalculatedField])