Calculating total from subform on the main form

[Polly]

Hello!

I'm a little new to Access, but do have a fairly good knowledge (al
self taught). I get lost quite easily with visual basic, but can wor
some out if I stare hard enough.....

Ok, my problem:
I've created a form to make quotes. On the main form you enter compan
name, quote number etc. The subform contains the product informatio
(description, quantity, cost etc)

How can i create a control on the main form that calculates the tota
product cost (info taken from records on subform)?

I've tried copying what I think I need from one of the Access templat
databases, but it doesnt work, I just keep getting "error" an
"#name?".

Any help is greatly appreciated

 

[dandgard]

Are you wanting to calculate a total for a specific record in th
subform, or for all records??

 

[Polly]

I'd like to total all the records that show on that order (but not ever
single one in the database...)

Thanks in advance, and thanks for the help with opening word - works
treat (and helped with my explorations into vba!

 

[dandgard]

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")