How to suppress the default "#Error" display in Text box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

My form has a subform and I have a a text box on the main form which is
showing the total value from the footer of the subform.

When the form is opened initially the Tesxt box by default always displays
"#Error" in the text box.

Is there a way I can suppress or have it default to "0" instead?
 
The crucial thing is to understand what is causing the error.

Examples of possibilities:

1. The subform has no records and new records cannot be added. In this case,
the subform's detail section goes completely blank, and any reference to the
non-existent text box causes an error.
The solution would be something like this:
=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))

2. Malformed statement.
If your text box contains something like this:
=DLookup("MyField", "MyTable", "[Field2] = " & [Field2])
you are asking Access to concatenate the value of Field2 into the 3rd
argument. But if Field2 is null (e.g. at a new record), the 3rd argument
becomes just:
[Field2] =
which is clearly mal-formed. To avoid this, add Nz(), e.g.:
=DLookup("MyField", "MyTable", "[Field2] = " & Nz([Field2],0))

3. Other error
A simple statment like:
=[Field1] / [Field2]
generates an error when Field2 is zero. (Divide by zero error).
Solution:
=IIf([Field2]=0, 0, [Field1] / [Field2])
Again, there are heaps of way to generate an error: even setting a control's
Format property to Short Date and then entering any text that is not a date.

So, figure out what is causing the error, and you know how to solve it.
 
Thanks for your input, in my case Case1 applies:

My only question is where do I put the code you suggested
"=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))"

In the Text box properties (under Data tab) which variable?

Thanks for your input!


Allen Browne said:
The crucial thing is to understand what is causing the error.

Examples of possibilities:

1. The subform has no records and new records cannot be added. In this case,
the subform's detail section goes completely blank, and any reference to the
non-existent text box causes an error.
The solution would be something like this:
=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))

2. Malformed statement.
If your text box contains something like this:
=DLookup("MyField", "MyTable", "[Field2] = " & [Field2])
you are asking Access to concatenate the value of Field2 into the 3rd
argument. But if Field2 is null (e.g. at a new record), the 3rd argument
becomes just:
[Field2] =
which is clearly mal-formed. To avoid this, add Nz(), e.g.:
=DLookup("MyField", "MyTable", "[Field2] = " & Nz([Field2],0))

3. Other error
A simple statment like:
=[Field1] / [Field2]
generates an error when Field2 is zero. (Divide by zero error).
Solution:
=IIf([Field2]=0, 0, [Field1] / [Field2])
Again, there are heaps of way to generate an error: even setting a control's
Format property to Short Date and then entering any text that is not a date.

So, figure out what is causing the error, and you know how to solve it.

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

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

MSA said:
My form has a subform and I have a a text box on the main form which is
showing the total value from the footer of the subform.

When the form is opened initially the Tesxt box by default always displays
"#Error" in the text box.

Is there a way I can suppress or have it default to "0" instead?
Click to expand...
Click to expand...
 
Put the expression into the Control Source property of the text box.

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

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

MSA said:
Thanks for your input, in my case Case1 applies:

My only question is where do I put the code you suggested
"=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))"

In the Text box properties (under Data tab) which variable?

Thanks for your input!


Allen Browne said:
The crucial thing is to understand what is causing the error.

Examples of possibilities:

1. The subform has no records and new records cannot be added. In this
case,
the subform's detail section goes completely blank, and any reference to
the
non-existent text box causes an error.
The solution would be something like this:
=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))

2. Malformed statement.
If your text box contains something like this:
=DLookup("MyField", "MyTable", "[Field2] = " & [Field2])
you are asking Access to concatenate the value of Field2 into the 3rd
argument. But if Field2 is null (e.g. at a new record), the 3rd argument
becomes just:
[Field2] =
which is clearly mal-formed. To avoid this, add Nz(), e.g.:
=DLookup("MyField", "MyTable", "[Field2] = " & Nz([Field2],0))

3. Other error
A simple statment like:
=[Field1] / [Field2]
generates an error when Field2 is zero. (Divide by zero error).
Solution:
=IIf([Field2]=0, 0, [Field1] / [Field2])
Again, there are heaps of way to generate an error: even setting a
control's
Format property to Short Date and then entering any text that is not a
date.

So, figure out what is causing the error, and you know how to solve it.

MSA said:
My form has a subform and I have a a text box on the main form which is
showing the total value from the footer of the subform.

When the form is opened initially the Tesxt box by default always
displays
"#Error" in the text box.

Is there a way I can suppress or have it default to "0" instead?
Click to expand...
Click to expand...
Click to expand...
 
Thanks once again for your input. The problem is that the "Control Source
property" of the text box is referencing the text box on the sub form to get
the total values(i.e. =[x_F_Area subform].Form!Text10).

As you very correctly pointed out in the Case1 situation -The subform has no
records and new records cannot be added. In this case,the subform's detail
section goes completely blank, and any reference to the non-existent text box
causes an error.

So when I tried to paste the suggested solution I keep getting syntax error.
Please advise!

Thanks



Allen Browne said:
Put the expression into the Control Source property of the text box.

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

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

MSA said:
Thanks for your input, in my case Case1 applies:

My only question is where do I put the code you suggested
"=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))"

In the Text box properties (under Data tab) which variable?

Thanks for your input!


Allen Browne said:
The crucial thing is to understand what is causing the error.

Examples of possibilities:

1. The subform has no records and new records cannot be added. In this
case,
the subform's detail section goes completely blank, and any reference to
the
non-existent text box causes an error.
The solution would be something like this:
=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))

2. Malformed statement.
If your text box contains something like this:
=DLookup("MyField", "MyTable", "[Field2] = " & [Field2])
you are asking Access to concatenate the value of Field2 into the 3rd
argument. But if Field2 is null (e.g. at a new record), the 3rd argument
becomes just:
[Field2] =
which is clearly mal-formed. To avoid this, add Nz(), e.g.:
=DLookup("MyField", "MyTable", "[Field2] = " & Nz([Field2],0))

3. Other error
A simple statment like:
=[Field1] / [Field2]
generates an error when Field2 is zero. (Divide by zero error).
Solution:
=IIf([Field2]=0, 0, [Field1] / [Field2])
Again, there are heaps of way to generate an error: even setting a
control's
Format property to Short Date and then entering any text that is not a
date.

So, figure out what is causing the error, and you know how to solve it.

My form has a subform and I have a a text box on the main form which is
showing the total value from the footer of the subform.

When the form is opened initially the Tesxt box by default always
displays
"#Error" in the text box.

Is there a way I can suppress or have it default to "0" instead?
Click to expand...
Click to expand...
Click to expand...
 
Given those names, the Control Source expression will be:

=IIf([x_F_Area subform].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([x_F_Area subform].[Form].[Text10],0))


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

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

MSA said:
Thanks once again for your input. The problem is that the "Control Source
property" of the text box is referencing the text box on the sub form to
get
the total values(i.e. =[x_F_Area subform].Form!Text10).

As you very correctly pointed out in the Case1 situation -The subform has
no
records and new records cannot be added. In this case,the subform's detail
section goes completely blank, and any reference to the non-existent text
box
causes an error.

So when I tried to paste the suggested solution I keep getting syntax
error.
Please advise!

Thanks



Allen Browne said:
Put the expression into the Control Source property of the text box.


MSA said:
Thanks for your input, in my case Case1 applies:

My only question is where do I put the code you suggested
"=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))"

In the Text box properties (under Data tab) which variable?

Thanks for your input!


:

The crucial thing is to understand what is causing the error.

Examples of possibilities:

1. The subform has no records and new records cannot be added. In this
case,
the subform's detail section goes completely blank, and any reference
to
the
non-existent text box causes an error.
The solution would be something like this:
=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))

2. Malformed statement.
If your text box contains something like this:
=DLookup("MyField", "MyTable", "[Field2] = " & [Field2])
you are asking Access to concatenate the value of Field2 into the 3rd
argument. But if Field2 is null (e.g. at a new record), the 3rd
argument
becomes just:
[Field2] =
which is clearly mal-formed. To avoid this, add Nz(), e.g.:
=DLookup("MyField", "MyTable", "[Field2] = " & Nz([Field2],0))

3. Other error
A simple statment like:
=[Field1] / [Field2]
generates an error when Field2 is zero. (Divide by zero error).
Solution:
=IIf([Field2]=0, 0, [Field1] / [Field2])
Again, there are heaps of way to generate an error: even setting a
control's
Format property to Short Date and then entering any text that is not a
date.

So, figure out what is causing the error, and you know how to solve
it.

My form has a subform and I have a a text box on the main form which
is
showing the total value from the footer of the subform.

When the form is opened initially the Tesxt box by default always
displays
"#Error" in the text box.

Is there a way I can suppress or have it default to "0" instead?
Click to expand...
Click to expand...
Click to expand...
 
I put it in the main form Text box Control property as suggested, but it
keeps giving me an error saying the expression you entered contains invalid
syntax.

Thanks!


Allen Browne said:
The crucial thing is to understand what is causing the error.

Examples of possibilities:

1. The subform has no records and new records cannot be added. In this case,
the subform's detail section goes completely blank, and any reference to the
non-existent text box causes an error.
The solution would be something like this:
=IIf([MySub].[Form].[RecordsetClone].[RecordCount] = 0, 0,
Nz([MySub].[Form].[Text0],0))

2. Malformed statement.
If your text box contains something like this:
=DLookup("MyField", "MyTable", "[Field2] = " & [Field2])
you are asking Access to concatenate the value of Field2 into the 3rd
argument. But if Field2 is null (e.g. at a new record), the 3rd argument
becomes just:
[Field2] =
which is clearly mal-formed. To avoid this, add Nz(), e.g.:
=DLookup("MyField", "MyTable", "[Field2] = " & Nz([Field2],0))

3. Other error
A simple statment like:
=[Field1] / [Field2]
generates an error when Field2 is zero. (Divide by zero error).
Solution:
=IIf([Field2]=0, 0, [Field1] / [Field2])
Again, there are heaps of way to generate an error: even setting a control's
Format property to Short Date and then entering any text that is not a date.

So, figure out what is causing the error, and you know how to solve it.

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

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

MSA said:
My form has a subform and I have a a text box on the main form which is
showing the total value from the footer of the subform.

When the form is opened initially the Tesxt box by default always displays
"#Error" in the text box.

Is there a way I can suppress or have it default to "0" instead?
Click to expand...
Click to expand...
 
Back
Top