Formula problem when subform contains no records

  • Thread starter Thread starter eb1mom
  • Start date Start date
E

eb1mom

This formula on main form
=[budgetfigure]-([MonthReqTotalForm].Form!TotalReqsAmount+[SplitMonthReqTotalForm].Form!TotalSplitReqsAmount)
works only if both subforms, MonthReqTotalForm and
SplitReqTotalForm, contain records. I tried NZ,and it does
not seem to work with no records. I searched but, couldn't
find answer in archives. Any suggestions would be
appreciated. Thanks
 
I had similar problem last week and could not solve it any other way except
throught VB (vba) code.

I used SQL to sum the total of what ever was supposed to be in the form like

sCriteria will be your MainForm Link to subform Value

Place code in your form Code module

Function subFormVal(sCriteria)
Dim sSQL as string, db as Database
sSQL = "SELECT Sum(tbl_Sales.SubTotal) AS SumOfSubTotal " _
& "FROM tbl_Sales WHERE (((tbl_Sales.Date)=#" & sCriteria & "#))"
Set db = Currentdb()
Set rs = db.OpenRecordset(ssql)
If rs.recordcount = 0 then
YourTargetTextBox = 0
Else
YourTargetTextBox = cdbl(rs!SumOfSubTotal)
rs.close
db.Close
End if
End Function

Private Sub Form_Current()
subFormVal ([YourTextBoxWithLinkValue])
End Sub

'Note... You must reference DAO 3.6 ... Also you must edit sSQL to match
your table, field names and link criteria
Or let me know your table name, the field you are summing and criteria (link
between Master sub form) and I will edit the ssql.







This formula on main form
=[budgetfigure]-([MonthReqTotalForm].Form!TotalReqsAmount+[SplitMonthReqTota
lForm].Form!TotalSplitReqsAmount)
works only if both subforms, MonthReqTotalForm and
SplitReqTotalForm, contain records. I tried NZ,and it does
not seem to work with no records. I searched but, couldn't
find answer in archives. Any suggestions would be
appreciated. Thanks
 
Thank-you, I reused your code with my database names and
after a bit of messing around to get correct references my
formula now works.
 
I had a similar problem and found this solution:

Put this in a module:

Public Function nnz(TestValue As Variant) As Variant
If Not (IsNumeric(TestValue)) Then
nnz = 0
Else
nnz = TestValue
End If
End Function

Then use it like this:

=nnz([budgetfigure]) - (nnz([MonthReqTotalForm].Form!TotalReqsAmount) +
nnz([SplitMonthReqTotalForm].Form!TotalSplitReqsAmount))

It sure solved my problem.

HTH,
Debbie


This formula on main form
=[budgetfigure]-([MonthReqTotalForm].Form!TotalReqsAmount+[SplitMonthReqTotalForm].Form!TotalSplitReqsAmount)
works only if both subforms, MonthReqTotalForm and
SplitReqTotalForm, contain records. I tried NZ,and it does
not seem to work with no records. I searched but, couldn't
find answer in archives. Any suggestions would be
appreciated. Thanks
 
Back
Top