Reading field values from a subform

[kfoxNevada]

I'm trying to calculate a total from values on a subform. Given the following:

MainForm:
BaseFee=xx (immaterial)
Subform:
Record1 Quantity=2
Record2 Quantity=5
Record3 Quantity=4

When I click a "Calculate" button on the MainForm-- I want to cycle through the Subform records and total the Quantity. I cannot for the life of me figure this out. please help.

 

[LittleSmudge]

Assuming that you have a standatd table setup underlying your forms, you have a main table bound to the main form with a field called something like 'BaseFeeId' which appears in the LinkParentField(s) property of the subForm control.

You then have a subTable bound to the subForm that has a 'BaseFeeRef' field that is the foreign Key and appears in the LinkChildField(s) property of the sub form control

So in the command button's on-click event:-

Dim rst etc.

rst.Open "SELECT Sum(Quantity) As SumOfQuantity FROM tblMainTable WHERE BaseFeeId = " & txtBaseFeeId

txtTotalQuantity = rst!SumOfQuantity

rst.CLose
etc.


As a ( MUCH ) easier alternative:-
In the Footer section of the continuous view subform add a text box control and call it txtSumOfQuantity
In the ControlSource put
=Sum([txtQuantity])

The control will then automatically update as soon as the values, or the number of records, in the subform changes.

If you want the data to appear on the main form only put a control on the main form and set its ControlSource to
=Forms!mainFormName!subFormControlName.Form!txtSumOfQuantity

and then set the txtSumOfQuantity.Visible = No

All automatic updates - No button needed.



'ope-that-'elps




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[kfoxNevada]

You second solution =sum([Units]) works beautifully, until I go to close the form. Then it hangs, and I have to ‘End Task’ on Access. BTW, if I use sum([txtUnits]) I get an #error. But if I use the underlying field name [units] it works (until I go to close the form.)

The first solution produces the error:
"Object Variable or With block variable not set."
Is this a DAO vs ADO problem? The Select statement works great in a query. Here's my code:
_____________________________
Dim rst As Recordset

rst.OpenRecordset "Select Sum(Units) As SumOfUnits From
tblAnnualSubstances Where AnnualFees_ID = " &
Me.txtAnnualFees_ID

Me.txtBaseFee = rst!SumOfUnits
rst.Close
______________________________
Anymore suggestions? Thanks for your time.

 

[kfoxNevada]

I got it to work with ADO the SELECT statement. Still wondering why the =SUM(Units]) hangs the form.

Thanks for your help.

 

[LittleSmudge]

Replace txtUnits with the ACTUAL name of the control that you use.

If the control name is not the same as the field name it is bound to then use the CONTROL NAME - eg. txtUnits rather than the field name eg. Units.

It all depends if you fully use the recommended naming conventions.


I've never had this approach HANG on me before. So it must be a local issue specific to your implementation.


BTW the Me. before the control names are unnecessary.




'ope-that-'elps.




G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.