Getting Rid of #Error | Member | | Join Date: Mar 2008
Posts: 41
| | |
Hello,
I need help on getting rid of #Error in my fields on a form when no data exist. I was using the below formula:
="" & [System]
and when #Error showed I switched over to
=IIf(IsNull([System])," ",[System])
and that came up with the same results. If data exist it works fine but when data is not there I want it to be blank or say "No data exist".
What formula can I use in the control source to fix this. Any assistance would be most appreciative.
Thanks
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,103
| | | re: Getting Rid of #Error
What exactly is [System]? What Datatype?
Welcome to TheScripts!
Linq ;0)> | | Expert | | Join Date: Mar 2008 Location: Firestone, CO
Posts: 112
| | | re: Getting Rid of #Error
I'm trying to duplicate this error to see if I can fix it, what data type is your [System] Column?
| | Member | | Join Date: Mar 2008
Posts: 41
| | | re: Getting Rid of #Error
Quote:
Originally Posted by janders468 I'm trying to duplicate this error to see if I can fix it, what data type is your [System] Column? Sorry about the slow response but two are of text and one is Date/Time.
It looks like this
="of " & [StudentCompany]
="" & [System]
=" on " & [GraduationDate]
Let me know what you come up with.
Thanks
| | Member | | Join Date: Mar 2008
Posts: 41
| | | re: Getting Rid of #Error
Quote:
Originally Posted by missinglinq What exactly is [System]? What Datatype?
Welcome to TheScripts!
Linq ;0)> I'm sorry Linq, but System is one of the fields from table1 used on the form and DataType is text.
Thanks for the fast response sorry couldnt say the same but step out a bit, didnt think anyone would respond back so quick.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Getting Rid of #Error
David,
I've tried various approaches but was unable to duplicate your problem in any of them I'm afraid.
It appears there's some quite fundamental information that you haven't included in the question. I can't tell you what it may be, but this isn't standard behaviour in a form to report #Error simply because a field has no data.
| | Member | | Join Date: Mar 2008
Posts: 41
| | | re: Getting Rid of #Error
Quote:
Originally Posted by NeoPa David,
I've tried various approaches but was unable to duplicate your problem in any of them I'm afraid.
It appears there's some quite fundamental information that you haven't included in the question. I can't tell you what it may be, but this isn't standard behaviour in a form to report #Error simply because a field has no data.
NeoPa,
I miss quoted before in saying it was a form, in fact it is a report. I am missing something but cant figure it out.
David
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Getting Rid of #Error
I think the matching answer is : - =IIf(IsError([System]),'',[System])
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Getting Rid of #Error
Should the question rather be though :
How do I stop a report from running if there are no matching records?
If so, the answer is that there is an On No Data event. - Private Sub Report_NoData(Cancel As Integer)
-
-
End Sub
This allows code to run informing the operator of the situation as well as allowing the report to be cancelled entirely (Set Cancel to True before exiting procedure).
| | Member | | Join Date: Mar 2008
Posts: 41
| | | re: Getting Rid of #Error
Quote:
Originally Posted by NeoPa Should the question rather be though :
How do I stop a report from running if there are no matching records?
If so, the answer is that there is an On No Data event.
- Private Sub Report_NoData(Cancel As Integer)
-
-
End Sub
This allows code to run informing the operator of the situation as well as allowing the report to be cancelled entirely (Set Cancel to True before exiting procedure). Thanks NeoPa,
Good solution but how do you set it to true.
David
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Getting Rid of #Error
At its simplest : - Private Sub Report_NoData(Cancel As Integer)
-
Cancel = True
-
End Sub
| | Member | | Join Date: Mar 2008
Posts: 41
| | | re: Getting Rid of #Error
Thanks for the information.
David
| | Member | | Join Date: Mar 2008
Posts: 41
| | | re: Getting Rid of #Error
Quote:
Originally Posted by NeoPa At its simplest :
- Private Sub Report_NoData(Cancel As Integer)
-
Cancel = True
-
End Sub
NeoPa,
Thanks, that worked perfectly I have just one more question. How can you make Microsoft Access close the same time the database closes.
David
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Getting Rid of #Error
There's no really direct way, but if you cause an invisible form to open when you open the database (Tools / Startup...) then you can leave it running in the background while the database is open.
In the Form_Close() event procedure you can then call a close of Access itself : - Private Sub Form_Close()
-
Call Application.Quit
-
End Sub
| | Member | | Join Date: Mar 2008
Posts: 41
| | | re: Getting Rid of #Error
NeoPa,
I can definitely say you are the expert. Everything work like you said and I appreciate your help. I am just about done. Can you throw me another bone on another stump I hit. ...
** Edit **
Question asked in new thread - see next post.
David.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 16,173
| | | re: Getting Rid of #Error
Thank you for your comments David.
I have split off the extra question into its own thread ( Select Report to Open by CheckBox) as explained in the thread.
|  |
Similar Microsoft Access / VBA bytes | | |  Forums
Visit our community forums for general discussions and latest on Bytes
/bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 229,155 network members.
|
