Subform #ERROR

  • Thread starter Thread starter Joe Williams
  • Start date Start date
J

Joe Williams

I have a subform that calculates two user entry fields from each subform
record into a total that is displayed for each row of the subform. No
Problem.

however, prior to the user making an entry into a new row, the subform
displays #ERROR in the field that calculates the total. I understand wy it
is giving me the error (no information entered yet), but is there a way to
suppress the display of the error until the user enters in the needed
information?

The way that #ERROR is displayed gives the user the feeling that they are
doing something wrong, which is not really the case.

THanks

joe
 
What is the reason for the error?

If the subform has no record AND no new records can be added, its Detail
section goes completely blank. Then if you attempt to refer to the
non-existent text box, you get an error. To solve that, check if there are
no records. The Control Source of the text box would look something like
this:
=IIf([Form].[RecordsetClone].[RecordCount]=0, Null, Sum([Quantity]))

Another common cause is a malformed argument for one of the domain aggregate
functions. For example, if you have:
=DSum("Quantity", "Table1", "OrderID = " & [OrderID])
and OrderID is Null, the 3rd argument becomes just:
OrderID =
which Access can't make sense of. Use Nz() to fix this:
=DSum("Quantity", "Table1", "OrderID = " & Nz([OrderID],0))
 
Hi Allen

The #ERROR is being displayed in the subform datasheet, for the new record.
Let's say my subform has three fields, QTY, PRICE, and TOTAL. QTY and PRICE
are entered by the user, TOTAL is just a calculated field that is displayed
to the user.

When the subform lists the records, the last one listed is always the new
record and thus #ERROR is displayed for TOTAL until the user fills out QTY
and PRICE. I was just wondering if there was a way to suppress or get around
#ERROR being displayed, until the user entered the correct data in which
case TOTAL would then be displayed.

What do you think?

THanks

Joe
Allen Browne said:
What is the reason for the error?

If the subform has no record AND no new records can be added, its Detail
section goes completely blank. Then if you attempt to refer to the
non-existent text box, you get an error. To solve that, check if there are
no records. The Control Source of the text box would look something like
this:
=IIf([Form].[RecordsetClone].[RecordCount]=0, Null, Sum([Quantity]))

Another common cause is a malformed argument for one of the domain
aggregate functions. For example, if you have:
=DSum("Quantity", "Table1", "OrderID = " & [OrderID])
and OrderID is Null, the 3rd argument becomes just:
OrderID =
which Access can't make sense of. Use Nz() to fix this:
=DSum("Quantity", "Table1", "OrderID = " & Nz([OrderID],0))

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

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

Joe Williams said:
I have a subform that calculates two user entry fields from each subform
record into a total that is displayed for each row of the subform. No
Problem.

however, prior to the user making an entry into a new row, the subform
displays #ERROR in the field that calculates the total. I understand wy
it is giving me the error (no information entered yet), but is there a
way to suppress the display of the error until the user enters in the
needed information?

The way that #ERROR is displayed gives the user the feeling that they are
doing something wrong, which is not really the case.
Click to expand...
Click to expand...
 
There is an error in the expression that is the ControlSource of the text
box.

If you can't see the cause of the error, post the Control Source expression,
with an explanation of what each part is.

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

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

Joe Williams said:
Hi Allen

The #ERROR is being displayed in the subform datasheet, for the new
record. Let's say my subform has three fields, QTY, PRICE, and TOTAL. QTY
and PRICE are entered by the user, TOTAL is just a calculated field that
is displayed to the user.

When the subform lists the records, the last one listed is always the new
record and thus #ERROR is displayed for TOTAL until the user fills out QTY
and PRICE. I was just wondering if there was a way to suppress or get
around #ERROR being displayed, until the user entered the correct data in
which case TOTAL would then be displayed.

What do you think?

THanks

Joe
Allen Browne said:
What is the reason for the error?

If the subform has no record AND no new records can be added, its Detail
section goes completely blank. Then if you attempt to refer to the
non-existent text box, you get an error. To solve that, check if there
are no records. The Control Source of the text box would look something
like this:
=IIf([Form].[RecordsetClone].[RecordCount]=0, Null, Sum([Quantity]))

Another common cause is a malformed argument for one of the domain
aggregate functions. For example, if you have:
=DSum("Quantity", "Table1", "OrderID = " & [OrderID])
and OrderID is Null, the 3rd argument becomes just:
OrderID =
which Access can't make sense of. Use Nz() to fix this:
=DSum("Quantity", "Table1", "OrderID = " & Nz([OrderID],0))

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

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

Joe Williams said:
I have a subform that calculates two user entry fields from each subform
record into a total that is displayed for each row of the subform. No
Problem.

however, prior to the user making an entry into a new row, the subform
displays #ERROR in the field that calculates the total. I understand wy
it is giving me the error (no information entered yet), but is there a
way to suppress the display of the error until the user enters in the
needed information?

The way that #ERROR is displayed gives the user the feeling that they
are doing something wrong, which is not really the case.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top