 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / May 2008display totals on subform
Access 2007 I have a MainForm that includes a SubForm. I can enter values into the SubForm. On the SubForm (continuous form) there is a button that opens up another form (ItemForm - not a subform) to enter details of records that relate to the items on the subform. Is it possible that the sum(values) of the ItemForm can be displayed on the SubForm records? If so how? Thanks A one way would be to use a dSum equation in a textbox (this way, your ItemForm does not have to be open) make a textbox control Name --> SumItems ControlSource --> =nz(dSum("[fieldname]", "[Tablename]", "condition"),0) WHERE fieldname is the name of the field (or the expression) to add Tablename is the name of the table containing the field condition is the string with the condition for which to add the field/expression ie: "ItemID=" & [ItemID] where ItemID is the fieldname in Tablename [ItemID] is the controlname on the current form to match the value of nz is Null-to-Zero, so zero is returned in the event that condition is not found Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * > Access 2007 > [quoted text clipped - 12 lines] > > A Thanks - I'm not sure on the condition part of this. Please clarify I want it to sum where: EstimateNo = subform.EstimateNo and ItemNo = subform.ItemNo How would I write this? Thanks again A > one way would be to use a dSum equation in a textbox (this way, your > ItemForm does not have to be open) [quoted text clipped - 45 lines] >> >> A you're welcome are EstimateNo and ItemNo your fieldnames? what is the data type of each? Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * > Thanks - I'm not sure on the condition part of this. Please clarify > [quoted text clipped - 55 lines] >>> >>> A yes the control names on the form are txtEstimateNo and txtItemNo and they are both Text Thanks for your help > you're welcome > [quoted text clipped - 70 lines] >>>> >>>> A Hi (what is your name?) if you are in the code behind the subform: "EstimateNo =' & me.txtEstimateNo & "' and ItemNo='" & me.txtItemNo & "'" Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * > yes > the control names on the form are txtEstimateNo and txtItemNo and they are [quoted text clipped - 76 lines] >>>>> >>>>> A I am putting this in the Control source of an unbound text box on the subform =Nz(DSum("ItemQty * UnitRate","tblItemDetails","EstimateNo ='" & [me].[txtEstimateNo] & "' AND ItemNo= '" & [me].[txtItemNo] & "'"),0) the textbox now just displays Name? What am I doing wrong Thanks Alison > Hi (what is your name?) > [quoted text clipped - 95 lines] >>>>>> >>>>>> A Thanks for your help - I've got it to work! I removed the me and used the field name not the control name (ie. removed the txt... Thanks again >I am putting this in the Control source of an unbound text box on the >subform [quoted text clipped - 107 lines] >>>>>>> >>>>>>> A glad you got it, Alison ;) happy to help Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * > Thanks for your help - I've got it to work! > [quoted text clipped - 113 lines] >>>>>>>> >>>>>>>> A Hi Alison, since you have the equation in a ControlSource, remove 'Me.' in front of the controlnames ... "EstimateNo ='" & [txtEstimateNo] & "' AND ItemNo= '" & [txtItemNo] & "'" Warm Regards, Crystal Access Basics 8-part free tutorial that covers essentials in Access http://www.AccessMVP.com/strive4peace * (: have an awesome day :) * > I am putting this in the Control source of an unbound text box on the > subform [quoted text clipped - 107 lines] >>>>>>> >>>>>>> A |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.