Report Shows #Error When Recordset Empty | Member | | Join Date: May 2007
Posts: 32
| | |
well i got it to work, with the Is Not Null fuction, I put it in the Weekstart string and it worked. So thank you very much for all the help. Saddly i have another small problem that doesn't make sence.
The new problem I have is that this was my last big thing, so I updated all of the data in my table and so all the reports do not have any records in them yet. So all of my total counts are 0 in my reports but instead of 0 they are Errors for some wierd reason, and i am not sure why at all, but my macros and some delete querys stoped working becouse it is returning an Error insted of 0. I have got caught way off gaurd with that.
|  | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
The thread that this was split from is Invalid Use Of Null In Query.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
This happens when the bound recordset is empty.
You can handle this by checking the status in the OnOpen event procedure.
Me.Recordset.EOF is what you need to check.
| | Member | | Join Date: May 2007
Posts: 32
| | | re: Report Shows #Error When Recordset Empty
I do not have anything in the on open even procedure, i found a formula that sets null to zero but it doesn't work. - Public Function NullToZero( TheValue as Variant)
-
-
'This function converts Null to Zero
-
-
'It also converts Non Existing Data to Zero
-
-
On Error Goto NullToZero_Err
-
-
-
-
If ISNull(TheValue) then
-
-
NullToZero = 0
-
-
Else
-
-
NullToZero = TheValue
-
-
End if
-
-
Exit Function
-
-
-
-
NullToZero_Err:
-
-
'This function would only generate an error
-
-
'if the data in TheValue doesn't exist at all.
-
-
NullToZero = 0
-
-
Exit Function
-
-
End Function
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
Are you talking about my post earlier or the other thread related to the IsNull question?
This thread is about the problem when a report is empty.
| | Member | | Join Date: May 2007
Posts: 32
| | | re: Report Shows #Error When Recordset Empty
I got the ISNull function to finally start working. but then i came up with another problem, i shouldn't have posted it there, i just didn't have the time to start a new thread.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
Don't worry about that. It's sorted.
Are you happy that you have an answer to this question now though?
| | Member | | Join Date: May 2007
Posts: 32
| | | re: Report Shows #Error When Recordset Empty
Yes, i couldn't put my database on the server untill that was fixed lol. Thank you very much, it helped alot. Now i just need to find out how to fix this #Error problem. Access should know and be able to auto correct this but it doesn't.
Thank you again for helping.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
Quote:
Originally Posted by Darknight850 Yes, i couldn't put my database on the server untill that was fixed lol. Thank you very much, it helped alot. Now i just need to find out how to fix this #Error problem. Access should know and be able to auto correct this but it doesn't.
Thank you again for helping. I'm pleased the other problem is good, but I'm asking about the #Error problem (see title of thread). From your comment I assume that this is still outstanding.
Go back to my post #3, and tell me what, if anything, you have difficulty with. We can then lead you to a solution where the report will return an error message (MsgBox()) rather than opening, when there is no data to display. This will get rid of the #Error problem.
| | Member | | Join Date: May 2007
Posts: 32
| | | re: Report Shows #Error When Recordset Empty
My mistake,
I put Me.Recordset.EOF in the on open procedure, but i have a feeling that is not what you ment. The problem with the msg box poping up when i open a report is that i have these reports opening up when my username signs on, this way it can run the querys, and the macros that i have, this way it all auto updates at the same time. I have a feeling that if a msg box pops up for a report that it will still send back an error in my startup code
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
I don't know what automatic processes you have which kick off this report, but in general, if it's kicked off automatically, you will probably not want the MsgBox() call. In that case, I'd simply close the report from inside when the code detects that it has no data with which to run.
I don't remember the precise details of how to detect this within the code, but I would have thought Me.Recordset.EOF within the OnOpen event procedure would be along the right lines.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
I thought I might just save you some time digging up the right syntax.
What I found was that you could have some code a bit like the following to do what you need : - Private Sub Report_Open(Cancel As Integer)
-
Cancel = (Me.Recordset.RecordCount = 0)
-
End Sub
| | Member | | Join Date: May 2007
Posts: 32
| | | re: Report Shows #Error When Recordset Empty
Thank you for hunting down the code. I put it in and it gave me error that code could not be run in MDE. I tried to put an if statement just to close if recordset = 0 but it didn't seem to work. I am not sure if that would work since it comes out in an #error not a 0, but maybe Null would work if possible.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
Quote:
Originally Posted by Darknight850 Thank you for hunting down the code. I put it in and it gave me error that code could not be run in MDE. I tried to put an if statement just to close if recordset = 0 but it didn't seem to work. I am not sure if that would work since it comes out in an #error not a 0, but maybe Null would work if possible. I don't use MDEs myself, but I looked up 'Recordset.RecordCount' under both the DAO library and the ADODB one, and they were both there as specified. I found nothing in comments to indicate that they could not be used in MDEs.
I'm sorry, I'm not sure why you've got that error :(
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
Yes I am :(
The Report object doesn't contain a Recordset object. I'm sorry.
I'm sure there must be another way of doing this, but I can't think of it just now.
| | Member | | Join Date: May 2007
Posts: 32
| | | re: Report Shows #Error When Recordset Empty
Thats not a problem, thank you for helping me. i think that formula would work that i posted above, at least it is supposed to, i had found it on a access web site. i just can not recall it off the top of my head.
| | Administrator | | Join Date: Oct 2006 Location: London - UK
Posts: 15,714
| | | re: Report Shows #Error When Recordset Empty
I too am frustrated :(
I'm sure I've dealt with this before, if only theoretically for a member here. If anything comes to me I'll try to remember this thread and post it.
| | Member | | Join Date: May 2007
Posts: 32
| | | re: Report Shows #Error When Recordset Empty
Thank you very much for all of your help
|  |
Similar Microsoft Access / VBA 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 226,383 network members.
|
