| | |
|
|
|
Calculating total from subform on the main form |
|---|
| message from Polly on 10 May 2004 |
Hello!
I'm a little new to Access, but do have a fairly good knowledge (all
self taught). I get lost quite easily with visual basic, but can work
some out if I stare hard enough.....
Ok, my problem:
I've created a form to make quotes. On the main form you enter company
name, quote number etc. The subform contains the product information
(description, quantity, cost etc)
How can i create a control on the main form that calculates the total
product cost (info taken from records on subform)?
I've tried copying what I think I need from one of the Access template
databases, but it doesnt work, I just keep getting "error" and
"#name?".
Any help is greatly appreciated!
|
| dandgard replied to Polly on 10 May 2004 |
Are you wanting to calculate a total for a specific record in the
subform, or for all records???
|
| Polly replied to dandgard on 11 May 2004 |
I'd like to total all the records that show on that order (but not every
single one in the database...)
Thanks in advance, and thanks for the help with opening word - works a
treat (and helped with my explorations into vba!)
|
| dandgard replied to Polly on 11 May 2004 |
You would probably be better served to redefine the recordset that is
used in your subform in the main form.
If you tie a piece of code to the oncurrent event of the form (this
event fires every time you change the record in the form). You can do
a DSum calculation in the code.
Private Sub Form_Current()
Dim strWhere as string
Dim ival as double
strWhere = "Criteria used to define subform"
ival = DSum("NbrItems * ValueOfItem", "TableOrQueryName", strWhere)
txtBox = ival
End Sub
The criteria is whatever link you have between the form and subform.
The subform's data will be filtered usually by a piece of information
from the main form (keyfield), and this is the criteria you want to use
to create the where clause for the DSum.
TableOrQueryName is the name of the recordsource for the subform, and
NbrItems and ValueOfItem are fields in that recordsource that you want
to get the sum for.
Another way to do this is to directly put the dsum function in the
Default Value property of the textbox that will contain the Sum. Only
you won't be able to use the strWhere variable but will have to spell
out the criteria in a string.
=DSum("expression", "Recordset", "Criteria")
|
|
Archived message: Calculating total from subform on the main form (Microsoft Access)
