I am trying to calculate values in a form based on the values of 4 subforms

  • Thread starter Thread starter Roderick de Rijke via AccessMonster.com
  • Start date Start date
R

Roderick de Rijke via AccessMonster.com

I am trying to calculate values in a form based on the values of 4
subforms. It did work, it is to say only when all the fields in the
subforms contain a value. Therefor i am trying to write a code to check if
there are any values in the txtfields in the subforms.
I wrote this code only i already stops at the first rst.MoveFirst. Can
somebody please help me out on this one.
Many thanks Roderick Spain
Here is my code:
Private Sub Update_Click()
Dim TxtTotaalActiviteiten As Long, TxtTotaalLogies As Long,
TxtTotaalGastronomia As Long, TxtTotaalExtras As Long
Dim rst As Recordset

Set rst = Forms![FrmMainform]![Subformulario Presupuesta1]
..Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
TxtTotaalActiviteiten = Forms![FrmMainform]![ Subformulario Presupuesta1]
..Form.GrantotalActividadesConIva
Else
TxtTotaalActiviteiten = 0
End If
Me!GrantotalActividadesConIva = TxtTotaalActiviteiten

Set rst = Forms![FrmMainform]![ Subformulario AlojamientoHD]
..Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
TxtTotaalLogies = Forms![FrmMainform]![ Subformulario AlojamientoHD]
..Form.GrantotalAlojamentoConIva
Else
TxtTotaalLogies = 0
End If
Me!GrantotalAlojamentoConIva = TxtTotaalLogies

Set rst = Forms![FrmMainform]![ Subformulario Gastronomia eerste gang1]
..Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
TxtTotaalGastronomia = Forms![FrmMainform]![ Subformulario Gastronomia
eerste gang1].Form.GrantotalGastronomiaConIva
Else
TxtTotaalGastronomia = 0
End If
Me!GrantotalGastronomiaConIva = TxtTotaalGastronomia

Set rst = Forms![FrmMainform]![ Subformulario Extras].Form.RecordsetClone
rst.MoveFirst
rst.MoveLast
If Nz(rst.RecordCount, 0) > 0 Then
TxtTotaalExtras = Forms![FrmMainform]![Subformulario Extras]
..Form.GrantotalExtraConIva
Else
TxtTotaalExtras = 0
End If
Me!GrantotalExtraConIva = TxtTotaalExtras

rst.Close
End Sub
 
Roderick

In a standard module, make a user-defined function like this...

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

Then, you can use...
nnz(Me.[Subformulario
Presupuesta1]!GrantotalActividadesConIva)+nnz(Me.[Subformulario
AlojamientoHD]!GrantotalAlojamentoConIva)+ nnz(Me.[Subformulario
Gastronomia eerste gang1]!GrantotalGastronomiaConIva)+
nnz(Me.[Subformulario Extras]!GrantotalExtraConIva)
 
Thanks Steve Schapel,

I will try to solve my problem using your suggestions. If i don?t succeed,
i?ll post a new message.
Thanks again
Roderick
 
I succeeded in solving the problem. Only now I have a new problem. I want
to make a report containing 4 subreports based on the values mentioned
before. The problems is that if there is no record the value is not 0 but
the subreports isn?t shown at all, which is OK only i do need the value for
my total on the mainreport. Now the mainreport gives an error. I tried to
base the values on queries but the problem remains. Again any help please!
Best regards Roderick
 
Roderick,

Well, this is not what I would have expected. But anyway, try it like
this...

Put 4 unbound textboxes on the report. You can set them Visible = No
after you have it working ok. So each one will be for reference to the
totals control on each subreport, so the Control Source something like
this...
=[Subformulario Presupuesta1]![GrantotalActividadesConIva]
.... etc.
Give each of these textboxes a name... for simplicity I will say they
are Total1, Total2, etc.

Now you can still try the nnz() function for the grand total, for
example if it's the Control Source of a textbox, like this...
=nnz([Total1])+nnz([Total2])+nnz([Total3])+nnz([Total4])
 
Back
Top