Control Total Error

[Guest]

I am using a text box to display a control total on a form. This control
refers to a text box an a subform attached to the main form. The routine
works perfectly well unless the underlying query returns no records.

I have tried the following, without success, to try and force a zero into
the text box

Iif([Adjustment]) Is Null,0,Sum([Adjustment])
IIf(IsEmpty(Adjustment)=True,0,Adjustment)

Any advice would be appreciated.

 

[Allen Browne]

If the subform returns no records AND no records can be added to the
subform, its Detail section goes completely blank. Then referring to the
non-existent control in the subform generates an error.

This example shows what to put into the Control Source of a text box on your
main form, assuming the subform control is named "Child1":
=IIf([Child1].[Form].[RecordsetClone].[RecordCount]=0, 0,
Nz([Child1].[Form]![Adjustment], 0))

That avoids the error by testing the RecordCount of the RecordsetClone of
the Form in the subform control.

If records cannot be added to the subform just because its AllowAdditions
property is set to Yes, you could also work around the issue by changing the
property to No. Instead, block new records by cancelling its BeforeInsert
event.

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

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

in message
news:[email protected]...