Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Forms Programming / April 2005

Tip: Looking for answers? Try searching our database.

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

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
MSA - 28 Apr 2005 13:22 GMT
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?
Allen Browne - 28 Apr 2005 13:42 GMT
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.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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.
[quoted text clipped - 3 lines]
>
> Is there a way I can suppress or have it default to "0" instead?
MSA - 28 Apr 2005 14:14 GMT
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.
>
[quoted text clipped - 35 lines]
> >
> > Is there a way I can suppress or have it default to "0" instead?
Allen Browne - 28 Apr 2005 15:23 GMT
Put the expression into the Control Source property of the text box.

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> Thanks for your input, in my case Case1 applies:
>
[quoted text clipped - 50 lines]
>> >
>> > Is there a way I can suppress or have it default to "0" instead?
MSA - 28 Apr 2005 15:48 GMT
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

> Put the expression into the Control Source property of the text box.
>
[quoted text clipped - 52 lines]
> >> >
> >> > Is there a way I can suppress or have it default to "0" instead?
Allen Browne - 28 Apr 2005 15:56 GMT
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))

Signature

Allen Browne - Microsoft MVP.  Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 74 lines]
>> >> >
>> >> > Is there a way I can suppress or have it default to "0" instead?
MSA - 28 Apr 2005 17:56 GMT
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!

> The crucial thing is to understand what is causing the error.
>
[quoted text clipped - 35 lines]
> >
> > Is there a way I can suppress or have it default to "0" instead?
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.