Full Version: subform total on main form
UtterAccess Forums > Microsoft® Access > Access Forms
waseemthonse
now i am facing new problem. I want to make the total of subform on the main form.I tried this formula
IIf((([Products Subform].[Form]![UnitsOnHand])>0),([Products Subform].[Form]![UnitsOnHand]),"0")
It works fine ,if there is record in the subform.and if there is no record,then it should give me "0'.But it gives me error message.
so can any body solve this problem
balaji
Get rid of the quotes around the 0 in the false part of the iif(). Better still, use the nz() function instead of using iif() to check for >0.
waseemthonse
i could not get you. can you write the formula
balaji
My first solution is:
IIf((([Products Subform].[Form]![UnitsOnHand])>0),([Products Subform].[Form]![UnitsOnHand]),0)
My second and preferred solution is:
= nz(([Products Subform].[Form]![UnitsOnHand]),0)
waseemthonse
hi,
i checked this on, it works only if the subform is having record.If the subform is not having any record ,then it shows error, so suggest some other method
balaji
What is the structure of your subform? Is it a single form view or continuous form? Where does the control [UnitsOnHand] get its value from?
waseemthonse
Subform is embedded in the main form and is continuous form.it calculates the item qty received,qty issued(Unit transaction).Units on hand is the text box. which given by reducing the qty issued from the received qty. if any record is not there then the text box on subfrom does not shows any thing. so in the main form it shows error. if any transaction is there then it shows qty.
balaji
What is the error message you get when the subform does not have any records? Is the subform opened even if there is no records in it?
waseemthonse
subform is linked embedded in main form. so whenever main form opens it checks and shows the records related to main form in the sub form. if the record is not there then in the text box of On hand Qty: it shows"#Error".
balaji
Maybe you should check whether there are subform records corresponding to that mainform record using a dcount() before trying to access the value in the subform's control.
waseemthonse
that is what i want.if there is no record in subform corresponding to record in my main form, then it gives error message. else it gives me on hand qty. please tell in detail, how i can solve it.......
balaji
As posted above, take a look at the dcount() function in Access VBA help files.
waseemthonse
still i could not solve the problem.....Any body can Help???
Can i use like this formula.
=IIf(Nz([subfrmloc].[Form].[RecordsetClone].[RecordCount])=Null,0,[subfrmloc].[F
orm]![OHQ])
if there is no record in subform ,then it should show o, else the ohq. may be above formula need some changes....
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.