I hate DSum! :-) Please help, newbie STILL can't get it to work! 2

[Stevehewitt]

Hey,

This is making me scream. I was tempted to throw the computer out of the Window until the boss reminded me he would deduct my pay!

Basically, I have a subform (sub1) a main form (main1) and a field (field1). Field1 is in subform1, and the subform is a subform of main1. All I want to do is get the totals (added up) of field1 to be displayed in main1. Thats all. The control source which I am trying is:

DSum("field1","subform1",)

From what I have read in the help I don't need a criteria, but if VBA (seeing if I can get it to work in code) asks me for a "=" again I will scream.

Can anyone help this mad newbie?!

Thanks,


Steve.

P.S. Thanks to Rollie and Wemier for helping out earlier. Unfortunatly the DB was on my home comp and there was a accident. (E.G. My little sister thought it would be OK to have a fag whilst playing on my PC and...!)

 

[scriverb]

What is the query that is the RecordSource for the subform form? Does this query have a criteria related to the controls on the main form? Just post that query so we may use it to give you the summed value that you want.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Stevehewitt]

The query has the field1 created in it. E.G:

field1:[field2]*[field3]

The control realted to the main for is a order number which is on both the subform and main form and seems to be working OK.

Many thanks for this, I have looked, researched and shouted and I STILL can't get it to work!

Thanks,


Steve.

 

[danvlas]

1. You don't need DSum...

Create a textbox in the form footer of the subform.
Give it a name (txtSubformTotal)
Set its control source to:
=Sum([field1])

Create a text box in the main form and name it txtMainTotal.
Set its control source to:

=Forms![MainFormName]![SubformName].Form!txtSubformTotal

Open the main form.
The total from the subform will be carried to the main form.

2. If you however want the DSum:

DSum has the syntax:
DSum("FieldName", "TableOrQueryName",["OptionalCriteria"])

A subform usually displays records that are related to the main form data, so you will need some criteria, otherwise the value will reflect the total of ALL records. txtMainTotal will have the controlsource:

=DSum("FieldName","TableSubformIsBasedOn","FieldInLinkChildFields = " & Forms![MainFormName]![FieldInLinkMasterFields])

If by any chance the join fields are text, the expression should be:

=DSum("FieldName","TableSubformIsBasedOn","FieldInLinkChildFields = '" & Forms![MainFormName]![FieldInLinkMasterFields] & "'&quot

If join fields are of Date/Time type:

=DSum("FieldName","TableSubformIsBasedOn","FieldInLinkChildFields = #" & Forms![MainFormName]![FieldInLinkMasterFields] & "#&quot


I'd use the first choice, as DSum is slower, especially for large tables.

Good luck



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[Stevehewitt]

Hey guys and gals,

I have tried both methods. I'm really sorry but I still cannot get it to work. The DSum I am using:

DSum("GP","qrySalesUnsStockNew","TT Number = '" & [Forms]![frmSalesNew]![TT Number] & "'"

What am I doing wrong?! Its nothing to do with the fact that the main form is a single form, yet the subform is a datasheet?

GP

= Field I want to add up and show in the main form, but its located in the subform

qrySalesUnsStockNew

= Query the subform is using. (Query creates the 'GP' field)

TT Number

= Child and Master links. Do I need a table name infront? E.G. tblSales.TT Number?

frmSalesNew

= Name of the main form. (To be changed!)

Sorry to be a pain, I'm pretty new to VBA and Access! ;-)

Cheers, & thanks for the patience!


Steve.

 

[scriverb]

Hopefully this problem is this simple of a fix. If you posting is correct and not a typo just add a closing parentesis:

DSum("GP","qrySalesUnsStockNew","TT Number = '" & [Forms]![frmSalesNew]![TT Number] & "'")

Let me know if this works now.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[scriverb]

Also, danvlas gave you an easy solution with the creation of a subform subtotal with a corresponding control on the main form which displays the calculated value of the subform control value. Give that one a try. Just change the subform subtotal control's control source to the following:
=Sum([GP])

That one should work just fine also. Post back with additional problems or questions.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[Stevehewitt]

Hey.

Thanks fellas! Got the SUM solution to work OK now. Many thanks. You are both a fine example of how TT really helps people get into both IT for the first time and new area's of IT such as programming (me!).

See attached Stars!

Steve.


P.S. I still hate DSum!

 

[scriverb]

Thanks for the Star, Steve. Good luck to you with your project. Don't give-up on the DSum function as it is useful in many places. Once you figure out the syntax of DSum, Dlookup, DCount, etc. they will be very useful tools for you.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[xuanb]

Bob,
I used your 1. solution with =Sum([field]) but the problem I get is when the query of the subform returns null, i.e. no rows. For example when students have no classes registered and therefore no credits. I've tried

If IsNull(Forms!MainForm!SubForm!Credits) Then Me.TotalCredits.Visible = False

Could you help me with this problem?

 

[scriverb]

Try this:

=Sum(NZ([field],0))

This update with the NZ function will convert the control values of null to a 0 so that the Sum function will work propertly.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???