I have a form that has 4 sub-forms 1

[wcj]

I have a form that has 4 sub-forms within it. Each of the 4 sub-forms is based on a query. I also have a box in the main form that adds up the values from the 4 sub-form’s. My problem is that whenever there is No data generated from one of the sub-form’s I get a #ERROR that shows up in the text box that does the adding based on the sub-form values. I’m guessing this is because a null value can’t be added. Is there a way I can get around this?

 

[joebloeonthego]

Try:
IIF(IsNull(Variable), 0, Variable)

maybe?

 

[BillPower]

Hi wcj

Try adding this Function to a Module:

Function NullToZero(anyValue As Variant) As Variant
On Error Resume Next
If IsNull(anyValue) Then
NullToZero = 0
Else
NullToZero = anyValue
End If
End Function

In each Sub Form's Form Footer (Hide the Footer)add a Text Box, call them, say txtSubFormTotal. Make the Control Source in each:

=NullToZero(Sum([SummedFieldName]))


Replacing SummedFieldName with your Field Name(s).

The existing Text Box's Control Source in your Main Form:

=Subform1.Form!txtSubFormTotal+ Subform2.Form!txtSubFormTotal+ Subform3.Form!txtSubFormTotal+ Subform4.Form!txtSubFormTotal

Replace Subform1 - Subform4 with your Sub Form names.

NullToZero converts Null values to Zero and in my opinion is more reliable than Access's built-in NZ Function.

Let me know if this works for you.

Bill

 

[satya1965]

hi Bill,
I have the same problem but instead of 4 subforms I am using 3subforms.There is Total field in 2subforms and I need the grandtotal of these two totals to be displayed on Main form(i.e. Total1(subform1)+Total2(subform2). I have tried
=Subform1.Form!txtSubFormTotal+ Subform2.Form!txtSubFormTotal but "#Name" is being displayed in the txt box of the main form. could you please help me.
Thanks in advance

 

[willir]

I use something similar to Bill's solution, but testin the main form.

For the on current record event

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL as String, intTest as Integer

strSQL = "select * from myTable where myTableid = " & me.ID

Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset(strSQL1)
intTest = rst.RecordCount

If intTest < 1 Then
mySubfrm.Visible = False
End If

rst.Close

And repeat...

As always, Bill's answer is way cool since you can use his solution in a template when creating the subform. Star for you Bill.

Richard

 

[satya1965]

Hi willir,
Is this the solution for my query.If so could you please explain
If intTest < 1 Then
mySubfrm.Visible = False
End If

rst.Close
I am still struggling to solve the problem
&quot;#Name&quot; is being displayed in the txt box of the main form.
Thanks in advance

 

[willir]

satya1965

The SQL statement runs a select query against the table used in your subform using the variable to link the main form to the subform.

inTest is the record count from the query. It should be -1 if it fails to find a record. (I use <1 from when I didn't know any better)

If no records are found, then make the subform invisible.

Then close the record set.

If the subform is invisible, you should not see the error in the subform. (I am assuming the error is a result that their are no records for the subform.)

By the way, this may be a better way of writing the code...

If intTest < 1 Then
mySubfrm.Visible = False
Else
mySubfrm.Visible = True
End If

If you decide to try this suggestion, in sequence, you will need to build SQL statements and run for each subform you wish to hide / unhide.

Richard