Problem with using subreport values in main report calculations

[Swiftraven]

Hi all,
I got my previous problem fixed but now have a different one..go figure.

When I open my report, I get a divide by zero/null value error stemming from the 2 subreports that I requery for every record in the record set to get values that I interpolate and then use in the rest of the calculations for the current record on the report.

If I try and open the report like I normally would (like the user would) I get the null value error, where the subreports return a null to my function and have to close the report.

Now, If I open the report in design view, then run it, I still get the error. But since I had it open in design view, when I end the error (hit end or debug then stop execution) Access returns me to the design view of the report. If I run the report again from design view, it works....

I assume it has to be some error in my logic or in how Access runs reports as opposed to forms (I use the same logic and order in my form with subforms and it works fine).

Anyone have any ideas on how to fix this?
Here is the part of the Form_Detail that is having the problem.
'Requery qrybottomlz form
Me.rptsubBottomLz.Requery

'Requery qryTopLz form
Me.rptsubTopLz.Requery

'Set txtBottomlz's value
Me.txtBottomLz = [rptsubBottomLz].Report![L(z)]

'Set txtToplz's value
Me.txtTopLz = [rptsubTopLz].Report![L(z)]

'Set txtBottomZ's value
Me.txtBottomz = [rptsubBottomLz].Report![z]

'Set txtTopz's value
Me.txtTopZ = [rptsubTopLz].Report![z]

'calculate Z
'if they are not equal, perform the calculation
If (txtTopZ.Value <> txtBottomz.Value) Then
sngz = txtTopZ - ((((sngLz - txtTopLz) / (txtTopLz - txtBottomLz)) * (txtBottomz - txtTopZ)))
Else
'if the top and bottom Z figures are equal, set Z to that value
sngz = txtTopZ
End If


Thanks
Jason

 

[Swiftraven]

Anyone have any ideas? This problem is still stumping me as to why it will work if I have the report open in design mode and then run it again immediatly after it crashes. I am under the assumption that it works the 2nd time because the original running of the report &quot;primes&quot; the subreport queries and they return the correct value the 2nd time through.

Jason

 

[scriverb]

During the OnLoad event a form queries all subforms, comboboxes, and record sources of a form. I take it that upon the initial open the SubForm queries bring back zero records of null values for fields that you are trying to execute a function divide. Thus the Divide by Zero/Null.

In your code you have state the following:
'calculate Z
'if they are not equal, perform the calculation
If (txtTopZ.Value <> txtBottomz.Value) Then
sngz = txtTopZ - ((((sngLz - txtTopLz) / (txtTopLz - txtBottomLz)) * (txtBottomz - txtTopZ)))
Else
'if the top and bottom Z figures are equal, set Z to that value
sngz = txtTopZ
End If

The red code has the potential of a divide by zero/null which generates the error that you are talking about. As I am not familiar as to what the data looks like or exactly where it is coming from I can only speculate that you referenced data in (txtTopLz - txtBottomLz) that is generating the 0/null value.

This is where I would start to figure it out.

Digest this an get back if you need more help.

Bob Scriver