Setting control source in VBA

  • Thread starter Thread starter Joan
  • Start date Start date
J

Joan

Hi,
I have a report with a total control that is to display the sum of the
totals of three subreports. Currently setting the control source in the
property sheet is not working in Access 2003 because as I understand it, the
security level of this edition is so high that it will not accept
calculations as the control source of a control. Consequently, no total
shows on the main report. I was hoping that a work-around might be to set
the control source in VBA code. When I try to do this in the On Open event
of the main report, I get a run-time error 2455: " You entered an
expression that has an invalid reference to the property Form/Report." I
have the following code written for the On Open event:

Private Sub Report_Open(Cancel As Integer)
Reports!rptInvoiceStatement!txtPay =
nnz(Reports!rptInvoiceStatement!rptInvoiceDogs!txtSumSalesPrice) +
nnz(Reports!rptInvoiceStatement!rptInvoiceCharges!txtSumTotCharge) +
nnz(Reports!rptInvoiceStatement!InvoiceAdjustments!txtAdjTot)
End Sub

nnz is a public function that checks to see if the total of a subreport is a
number ( in other words, if there are any records for the subreport based on
report parameters). If it isn't a number, the function sets the result to 0.
Below is the public function:

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


Why am I getting the run time message above?
Joan
 
Joan, I don't think this message has anything to do with security.
It's more about timing.

You cannot assign a value to a control in Report_Open. The Format event of
the report Header section is the earliest you can assign values to controls.
Normally you would assign a value to an unbound control in the Format event
of the section that contains the control.

(BTW, your code is assigning a value to a control. That's not the same thing
as assigning a Control Source property to a control. The control has to be
unbound (no contorl source), or you cannot assign a value to it)

More importantly, though, it should not be necessary to assign a value in
this way at all? No code is needed. At design time, set the Control Source
of the text box to something like this:
=IIf([rptInvoiceDogs].[Report].[HasData],
Nz([rptInvoiceDogs].[Report]![txtSumSalesPrice],0), 0)
+ IIf([rptInvoiceCharges].[Report].[HasData],
Nz([rptInvoiceCharges].[Report]![txtSumTotCharge],0), 0)
+ IIf([InvoiceAdjustments].[Report].[HasData],
Nz([InvoiceAdjustments].[Report]![txtAdjTot],0), 0)


Final note: the nnz() function won't work as you hoped, because IsNumeric()
will not cope with Error being passed in. Further, if you pass an
unitialized Variant (the value is Empty), IsNumeric() returns True.
 
Allen,
Thanks for replying to my post. I made the changes to the Control Source
property at design time that you recommended and the total appears on the
report, however my environment here is Access 2002 and the total appeared on
the report in this environment before the change. I will try this change
in the customer's Access 2003 environment tomorrow. This problem only
appeared after the customer's Access 2003 Beta Edition expired and they
purchased the retail version of Access 2003.

I am having the same problem with the EditDeleteInvoice form where the total
on the main form should show the sum of the totals of two sub-forms.
However, I noticed that the [HasData] property applies to only reports and
not forms. Below is the expression that I currently have the Control Source
of the InvoiceTotal on the main form set to:

=Nz(Forms!EditDeleteInvoice!EditDog!txtTotalSalePrice,0)+Nz(Forms!EditDelete
Invoice!InvoiceDetailsSubEdit!ExtendedPriceSum,0)

How should I check to see if the sub-forms have data in them first?
Thanks again for your help.

Joan


Allen Browne said:
Joan, I don't think this message has anything to do with security.
It's more about timing.

You cannot assign a value to a control in Report_Open. The Format event of
the report Header section is the earliest you can assign values to controls.
Normally you would assign a value to an unbound control in the Format event
of the section that contains the control.

(BTW, your code is assigning a value to a control. That's not the same thing
as assigning a Control Source property to a control. The control has to be
unbound (no contorl source), or you cannot assign a value to it)

More importantly, though, it should not be necessary to assign a value in
this way at all? No code is needed. At design time, set the Control Source
of the text box to something like this:
=IIf([rptInvoiceDogs].[Report].[HasData],
Nz([rptInvoiceDogs].[Report]![txtSumSalesPrice],0), 0)
+ IIf([rptInvoiceCharges].[Report].[HasData],
Nz([rptInvoiceCharges].[Report]![txtSumTotCharge],0), 0)
+ IIf([InvoiceAdjustments].[Report].[HasData],
Nz([InvoiceAdjustments].[Report]![txtAdjTot],0), 0)


Final note: the nnz() function won't work as you hoped, because IsNumeric()
will not cope with Error being passed in. Further, if you pass an
unitialized Variant (the value is Empty), IsNumeric() returns True.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Joan said:
Hi,
I have a report with a total control that is to display the sum of the
totals of three subreports. Currently setting the control source in the
property sheet is not working in Access 2003 because as I understand it, the
security level of this edition is so high that it will not accept
calculations as the control source of a control. Consequently, no total
shows on the main report. I was hoping that a work-around might be to set
the control source in VBA code. When I try to do this in the On Open event
of the main report, I get a run-time error 2455: " You entered an
expression that has an invalid reference to the property Form/Report." I
have the following code written for the On Open event:

Private Sub Report_Open(Cancel As Integer)
Reports!rptInvoiceStatement!txtPay =
nnz(Reports!rptInvoiceStatement!rptInvoiceDogs!txtSumSalesPrice) +
nnz(Reports!rptInvoiceStatement!rptInvoiceCharges!txtSumTotCharge) +
nnz(Reports!rptInvoiceStatement!InvoiceAdjustments!txtAdjTot)
End Sub

nnz is a public function that checks to see if the total of a subreport
Click to expand...
is
a
number ( in other words, if there are any records for the subreport
Click to expand...
based
on
report parameters). If it isn't a number, the function sets the result
Click to expand...
to
0.
Below is the public function:

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


Why am I getting the run time message above?
Joan
Click to expand...
Click to expand...
 
Joan said:
Allen,
Thanks for replying to my post. I made the changes to the Control Source
property at design time that you recommended and the total appears on the
report, however my environment here is Access 2002 and the total appeared on
the report in this environment before the change. I will try this change
in the customer's Access 2003 environment tomorrow. This problem only
appeared after the customer's Access 2003 Beta Edition expired and they
purchased the retail version of Access 2003.

I am having the same problem with the EditDeleteInvoice form where the total
on the main form should show the sum of the totals of two sub-forms.
However, I noticed that the [HasData] property applies to only reports and
not forms. Below is the expression that I currently have the Control Source
of the InvoiceTotal on the main form set to:

=Nz(Forms!EditDeleteInvoice!EditDog!txtTotalSalePrice,0)+Nz(Forms!EditDelete
Invoice!InvoiceDetailsSubEdit!ExtendedPriceSum,0)

How should I check to see if the sub-forms have data in them first?
Click to expand...

In lieu of HasData, you can always use the IsError function:

=IIf((IsError(EditDog.Form!txtTotalSalePrice), 0,
EditDog.Form!txtTotalSalePrice) +
IIf((IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),
0, InvoiceDetailsSubEdit.Form!ExtendedPriceSum)
 
Allen,
I tried the expression you recommended using as the Control Source in the
user's Access 2003 environment and it works great! ... The Invoice Total is
displayed. Thanks for explaining why the nnz() function does not work.
Thank you so much for all of your help!

Joan


Allen Browne said:
Joan, I don't think this message has anything to do with security.
It's more about timing.

You cannot assign a value to a control in Report_Open. The Format event of
the report Header section is the earliest you can assign values to controls.
Normally you would assign a value to an unbound control in the Format event
of the section that contains the control.

(BTW, your code is assigning a value to a control. That's not the same thing
as assigning a Control Source property to a control. The control has to be
unbound (no contorl source), or you cannot assign a value to it)

More importantly, though, it should not be necessary to assign a value in
this way at all? No code is needed. At design time, set the Control Source
of the text box to something like this:
=IIf([rptInvoiceDogs].[Report].[HasData],
Nz([rptInvoiceDogs].[Report]![txtSumSalesPrice],0), 0)
+ IIf([rptInvoiceCharges].[Report].[HasData],
Nz([rptInvoiceCharges].[Report]![txtSumTotCharge],0), 0)
+ IIf([InvoiceAdjustments].[Report].[HasData],
Nz([InvoiceAdjustments].[Report]![txtAdjTot],0), 0)


Final note: the nnz() function won't work as you hoped, because IsNumeric()
will not cope with Error being passed in. Further, if you pass an
unitialized Variant (the value is Empty), IsNumeric() returns True.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Joan said:
Hi,
I have a report with a total control that is to display the sum of the
totals of three subreports. Currently setting the control source in the
property sheet is not working in Access 2003 because as I understand it, the
security level of this edition is so high that it will not accept
calculations as the control source of a control. Consequently, no total
shows on the main report. I was hoping that a work-around might be to set
the control source in VBA code. When I try to do this in the On Open event
of the main report, I get a run-time error 2455: " You entered an
expression that has an invalid reference to the property Form/Report." I
have the following code written for the On Open event:

Private Sub Report_Open(Cancel As Integer)
Reports!rptInvoiceStatement!txtPay =
nnz(Reports!rptInvoiceStatement!rptInvoiceDogs!txtSumSalesPrice) +
nnz(Reports!rptInvoiceStatement!rptInvoiceCharges!txtSumTotCharge) +
nnz(Reports!rptInvoiceStatement!InvoiceAdjustments!txtAdjTot)
End Sub

nnz is a public function that checks to see if the total of a subreport
Click to expand...
is
a
number ( in other words, if there are any records for the subreport
Click to expand...
based
on
report parameters). If it isn't a number, the function sets the result
Click to expand...
to
0.
Below is the public function:

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


Why am I getting the run time message above?
Joan
Click to expand...
Click to expand...
 
Marshall,
Thank you for your help. The new expression with the IsError function
works! The InvoiceTotal on the InvoiceForm and EditDeleteInvoice form now
displays as it should. So if a subform has no data, it returns an error?
This will be good to know for future reference.

Joan

Marshall Barton said:
Joan said:
Allen,
Thanks for replying to my post. I made the changes to the Control Source
property at design time that you recommended and the total appears on the
report, however my environment here is Access 2002 and the total appeared on
the report in this environment before the change. I will try this change
in the customer's Access 2003 environment tomorrow. This problem only
appeared after the customer's Access 2003 Beta Edition expired and they
purchased the retail version of Access 2003.

I am having the same problem with the EditDeleteInvoice form where the total
on the main form should show the sum of the totals of two sub-forms.
However, I noticed that the [HasData] property applies to only reports and
not forms. Below is the expression that I currently have the Control Source
of the InvoiceTotal on the main form set to:

=Nz(Forms!EditDeleteInvoice!EditDog!txtTotalSalePrice,0)+Nz(Forms!EditDelet
Click to expand...
e
Invoice!InvoiceDetailsSubEdit!ExtendedPriceSum,0)

How should I check to see if the sub-forms have data in them first?
Click to expand...

In lieu of HasData, you can always use the IsError function:

=IIf((IsError(EditDog.Form!txtTotalSalePrice), 0,
EditDog.Form!txtTotalSalePrice) +
IIf((IsError(InvoiceDetailsSubEdit.Form!ExtendedPriceSum),
0, InvoiceDetailsSubEdit.Form!ExtendedPriceSum)
Click to expand...
 
Back
Top