Report Shows #Error When Recordset Empty

[Darknight850]

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.

[NeoPa]

The thread that this was split from is Invalid Use Of Null In Query.

[NeoPa]

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.

[Darknight850]

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

[NeoPa]

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.

[Darknight850]

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.

[NeoPa]

Don't worry about that. It's sorted.
Are you happy that you have an answer to this question now though?

[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.

[NeoPa]

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.

[Darknight850]

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

[NeoPa]

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.

[NeoPa]

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

[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.

[NeoPa]

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 :(

[NeoPa]

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.

[Darknight850]

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.

[NeoPa]

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.

[Darknight850]

Thank you very much for all of your help