Supress "#Error" in a Textbox

[Guest]

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!

 

[Guest]

try this instead

=iif(IsError([TextBox]),0,[TextBox])

 

[Guest]

Tried to put the suggested expression but am unable to do - so as before I
keep getting the same error msg. saying the "expression you entered contains
invalid syntax".

Currently the text box-(MainForm) contains the following to read the textbox

try this instead

=iif(IsError([TextBox]),0,[TextBox])

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!

 

[Guest]

I think your syntax should just be
=[x_F_Area subform]!Text10

Tried to put the suggested expression but am unable to do - so as before I
keep getting the same error msg. saying the "expression you entered contains
invalid syntax".

Currently the text box-(MainForm) contains the following to read the textbox

try this instead

=iif(IsError([TextBox]),0,[TextBox])

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!

 

[Guest]

Thanks for the pointer there, but that code is working fine (i.e. it is able
to read what is in the textbox on the subform). Now how do I get your
suggested solution incorporated in the Text box-(Main form)?

=iif(IsError([TextBox]),0,[TextBox])

It keeps coming up with a error msg "expression you entered contains invalid
syntax".

I think your syntax should just be
=[x_F_Area subform]!Text10

Tried to put the suggested expression but am unable to do - so as before I
keep getting the same error msg. saying the "expression you entered contains
invalid syntax".

Currently the text box-(MainForm) contains the following to read the textbox

try this instead

=iif(IsError([TextBox]),0,[TextBox])

:

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!

 

[Guest]

Is it just as simple as putting the iif IsError around the field on the sub
form instead ?

Thanks for the pointer there, but that code is working fine (i.e. it is able
to read what is in the textbox on the subform). Now how do I get your
suggested solution incorporated in the Text box-(Main form)?

=iif(IsError([TextBox]),0,[TextBox])

It keeps coming up with a error msg "expression you entered contains invalid
syntax".

I think your syntax should just be
=[x_F_Area subform]!Text10

Tried to put the suggested expression but am unable to do - so as before I
keep getting the same error msg. saying the "expression you entered contains
invalid syntax".

Currently the text box-(MainForm) contains the following to read the textbox
on the subform:
=[x_F_Area subform].Form!Text10



:

try this instead

=iif(IsError([TextBox]),0,[TextBox])

:

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!

 

[Guest]

Forgive my ignorance as I just started developing with access/forms.

Where excactly should I put your suggested code

Here is the layout
Subform Textbox (named Text10) Control Source Property has:
=Sum([Quantity SSU])

Which is being read by the other Text box (named Text16) on the Main form
Mainform Textbox Control Source Property has:
=[x_F_Area subform].Form!Text10

Given the above where should I put the code, thanks.

Is it just as simple as putting the iif IsError around the field on the sub
form instead ?

Thanks for the pointer there, but that code is working fine (i.e. it is able
to read what is in the textbox on the subform). Now how do I get your
suggested solution incorporated in the Text box-(Main form)?

=iif(IsError([TextBox]),0,[TextBox])

It keeps coming up with a error msg "expression you entered contains invalid
syntax".

I think your syntax should just be
=[x_F_Area subform]!Text10

:

Tried to put the suggested expression but am unable to do - so as before I
keep getting the same error msg. saying the "expression you entered contains
invalid syntax".

Currently the text box-(MainForm) contains the following to read the textbox
on the subform:
=[x_F_Area subform].Form!Text10



:

try this instead

=iif(IsError([TextBox]),0,[TextBox])

:

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!

 

[Guest]

in the control source of Text10 on your subform
=iif(IsError(Sum([Quantity SSU])),0,Sum([Quantity SSU]))

Forgive my ignorance as I just started developing with access/forms.

Where excactly should I put your suggested code

Here is the layout
Subform Textbox (named Text10) Control Source Property has:
=Sum([Quantity SSU])

Which is being read by the other Text box (named Text16) on the Main form
Mainform Textbox Control Source Property has:
=[x_F_Area subform].Form!Text10

Given the above where should I put the code, thanks.

Is it just as simple as putting the iif IsError around the field on the sub
form instead ?

Thanks for the pointer there, but that code is working fine (i.e. it is able
to read what is in the textbox on the subform). Now how do I get your
suggested solution incorporated in the Text box-(Main form)?

=iif(IsError([TextBox]),0,[TextBox])

It keeps coming up with a error msg "expression you entered contains invalid
syntax".


:

I think your syntax should just be
=[x_F_Area subform]!Text10

:

Tried to put the suggested expression but am unable to do - so as before I
keep getting the same error msg. saying the "expression you entered contains
invalid syntax".

Currently the text box-(MainForm) contains the following to read the textbox
on the subform:
=[x_F_Area subform].Form!Text10



:

try this instead

=iif(IsError([TextBox]),0,[TextBox])

:

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!

 

[Marshall Barton]

Try using two text boxes on the main form. Make a new one
named txtSubTotal and set its Visible property to No and
control source expression to:
=[x_F_Area subform].Form!Text10
This text box will have #Error, but since it's invisible it
won't matter.

Now your existing text box can use Dennis' expression:
=IIf(IsError([txtSubTotal]), 0, [txtSubTotal])
--
Marsh
MVP [MS Access]

Thanks for the pointer there, but that code is working fine (i.e. it is able
to read what is in the textbox on the subform). Now how do I get your
suggested solution incorporated in the Text box-(Main form)?

=iif(IsError([TextBox]),0,[TextBox])

It keeps coming up with a error msg "expression you entered contains invalid
syntax".

I think your syntax should just be
=[x_F_Area subform]!Text10

Tried to put the suggested expression but am unable to do - so as before I
keep getting the same error msg. saying the "expression you entered contains
invalid syntax".

Currently the text box-(MainForm) contains the following to read the textbox
on the subform:
=[x_F_Area subform].Form!Text10



:

try this instead

=iif(IsError([TextBox]),0,[TextBox])

:

I have a text box-(MainForm) that is reading a value from a subtotal text
box-(subform) from a subform.

When the form is opened initially it always has an "#Error" in this text
box-(MainForm) as a result of subtotal on the subform not having a value to
sum, thus causing the error.

So I tried using the following code in the Control Source property of my
text box-(MainForm) but I keep getting an error message "saying the
expression you entered contains invalid syntax".

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

Would appreciate if someone could point out where the above expression is
wrong.

Thanks!