Subform total in main form

[doorok]

I have a form with a subform. Both forms are connected to individual tables. The main form has two fields: custname and custid. The subform has a relationship to the main form based on custid. The subform has two other fields: chargedesc and charge (currency). Basically the purpose is to enter a customer into the db and put in all the charges for the customer by charge type. What I would like to do is put a third field into the main form that would calculate the total charges based on the sum of the charges in the subform for the customer currently being viewed. This field should automatically display the total on the form. What is the best way to do this?

 

[JeremyNYC]

Add an unbound text box to your main form.

Make a function that puts the total of all the items displayed on your form into that text box.

Call that function in the afterUpdate, Delete, and AfterInsert events of your subform and the Current event of your main form.

The function will look like this, with appropriate name changes (though it's untested aircode):

function AddItUp
dim db as dao.database
dim rst as dao.recordset
dim strSql as string

strsql = "SELECT sum(charge) as TotCharge " _
& " FROM tblCharges " _
& " WHERE CustID = " & forms!MainFormName!txtCustID
set db = currentdb
set rst = db.openrecordset(strsql, dbopensnapshot)
if not rst.eof and rst.bof then
forms!mainFormName!txtTotCharge = rst("TotCharge&quot
else
forms!mainformname!txttotcharge = 0
end if

rst.close
set rst = nothing
db.close
set db = nothing
end function

Hope this helps.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[PeteJohnston]

Another option would be to add a text box (call it txtTotal or something) to the Footer section of the subform with data source

=Sum([Charges])

then change the data source for the text box on the main form to be

=[mainformname].[Form]![txtTotal]

Doing it this way means you don't have to put any code into the Update/Delete/Insert events.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[JeremyNYC]

Pete,

What happens if your subform's in datasheet or continuous view? I've never played with that setup.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Affordable Development, Professionally Done

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.

 

[PeteJohnston]

I've only ever used subforms in datasheet view showing a parent and several related child records, eg. an order entry form with customer and address info on the main form and order items on the subform. When I tried changing the subform to single record or continuous it showed all parts of the subform, header, detail and footer sections. The textbox which I called txtTotal could be seen on the subform footer, and still held the total of (in my database) order value. The textbox on the main form also still showed the order total.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[PeteJohnston]

Sorry, I've just noticed a Woops in my previous post! The data source for the total box should have been

=[subformname].[Form]![txtTotal]

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[isampurna]

Hi Pete,
How if I want to have the sum field in the main form is a column in a table? I mean, how can I save the sum into the column, since I have a function as a data source.
Any idea?

Thanks
Irwan

 

[PeteJohnston]

Eh? Not quite sure what you mean.

Do you mean that you have a column in the parent table which holds the total of all of the child rows? I don't think Access will let you do this directly. The data source can only be set to one thing - either the function or the column from the table. If you add a text box with Visible=False and set it's data source to your column on the parent table you can add some code to the txtTotal_Change event to set this new hidden field to the value of txtTotal. I think txtTotal_Change should fire when you add/change/delete any of the child rows so this should keep the new field in line.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[isampurna]

Hi Pete,
Yes, my intention is to have a text field related to a column, and this field is programatically content the value of the txtTotal_Change. I already created one hidden text field, named txtTotal_Hidden with a column as the data source. And I put a code in AfterUpdate trigger in txtTotal_Change field, which:
Me!Form.txtTotal_Hidden = Me!Form.txtTotal_Change

But the txtTotal_Hidden always come out as null. Is it because the txtTotal_Change is not explicitly updated? (since the value is based on the sum of values in the subform).

I still don't be able to find out which trigger should I use.
Any idea?

Thanks
Irwan

 

[PeteJohnston]

Irwan

the txtTotal_Hidden field should be set to the value of the txtTotal field which you have already set up as the total of the fields on the subform. It should be changed in either the txtTotal_Change or the BeforeUpdate something like this

Private Sub txtTotal_Change()

   txtTotal_Hidden = txtTotal

End Sub

If you put this bit of code in place and stick a break point in it you should be able to watch it change the txtTotal_Hidden field any time that you change any of the rows on the subform.

ps. if you are referring to fields which are on the form you are writing the code for you don't need the Me!Form. bit, just the name of the field. It always seems to me that it just makes the code that little bit harder to read.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[isampurna]

Hi Pete,
I have these in my forms:
- txtSubtotal (in the subform), with Control Source = nz(Sum([Income_Line_Amount])) --> the total of the subform amount
- txtTotalHeader (in the main form), with Control Source =[Income_Line].Form!SubTotal --> the total amount displayed in the main form
- txtTotalInsert, with Control Source = clAmount (column)

Then I put this in the BeforeUpdate trigger of txtTotalHeader:
[txtTotalInsert] = [Subform].Form!txtSubtotal

But the trigger is never fired. I think this is because the form never go into field txtTotalHeader explicitly. How can I push the form so that the trigger will be fired?

Thanks
Irwan

 

[PeteJohnston]

I've just had a thought - the Form_BeforeUpdate event on the main form won't fire unless some of the header info is changed, even when any of the subform rows have been changed. The code to assign the value to txtTotalInsert needs to go in the txtTotal_Change event.

So, to recap, you now have three text boxes:

1 - txtSubTotal on the subform footer with data source
Sum([Income_Line_Amount])
2- txtTotalHeader on the main form with data source
[Subform].Form!txtSubtotal to display the value
3- txtTotalInsert, the hidden textbox whose data source is
the column which you want to update on your table.

(That table is the data source for the main form, isn't it? )

When the txtTotalHeader value changes for any reason (if you change a value on the subform or when the form loads) the txtTotalHeader_Change event should fire so that is where you need to add the code. Don't set txtTotalInsert to the field on the subform, just set it to the value of txtTotalHeader and it should work Okay.

PeteJ
(Contract Code-monkey)

It's amazing how many ways there are to skin a cat
(apologies to the veggies)

 

[isampurna]

Hi Pete,
I followed your suggestion, but the txtTotalInsert is still doesn't changed. If I put the checkpoint in the txtTotalHeader_Change event, the system doesn't trigger the event when I enter some value in the subform. I think the on change trigger also need a refresh or explicit focus in the field txtTotalHeader.
Would be glad for any further assistance .

Thanks
Irwan