don't understand "expression has no value"

[edward]

I'm getting the "you entered an expression that has no value" error
sometimes but not others, and I don't understand.

I'm trying to generate a report from a form. I'm actually using this to
generate one page at a time due to performance problems with printing
the entire report at once, but that's only relevant as background.

I have a form "pagesel" with an unbound text box "pagesel" and a
command button. The button's On Click event is set to a procedure which
does just

DoCmd.OpenReport "Report1", acViewPreview, ,
"pagenumber = [Forms]![pagesel]![pagesel]"

Report1 detail contains, among other things, an image control
"imageframe" and a text box "imagepath" bound to a field "imagepath" in
a table "imagetable". The OnFormat procedure for the detail section is
set to a procedure coded

Dim ip As String
ip = Me![imagepath]
Me![imageframe].Picture = Me![imagepath]

Originally this was just a simple assignment; I added the variable and
separated it into two assignments in trying to track down my problem.

When I put "1" in the pagesel box, this works fine and the Print
Preview displays when I click the command button.

When I put any larger number in the pagesel box -- such as "2" -- then
when I click the command button, I get an error message dialog

Run-time error '2427'.
You entered an expression that has no value.
The expression may refer to an object that has no value, such as a
form, a report, or a label control.

When I click Debug, it lands me on the ip = Me![imagepath] statement. I
even verified this by inserting MsgBox statements.

If it never worked, then I'd be looking for a gap in my understanding
of how the code should work. (I've been programming for almost 40
years, but have used VB very little and not at all in Access until a
couple of days ago.) But it works sometimes and fails sometimes, and I
don't understand that part.

Why?, I scream, Why?

Edward

 

[Ken Snell [MVP]]

Post the report's Recordsource query. I'm guessing that a value of 2 for
pagenumber is not a valid result for records in that query.

 

[edward]

Ah-ha! Pinpoint accuracy. The Record Source was

SELECT * FROM imagetable WHERE (((imagetable.pagenumber) = 1))

I had done this earlier in testing to verify the basic principle
(selecting records based on imagetable.pagenumber), and forgot about
it. I changed the Record Source to just "imagetable" (the name of the
one table -- seems I neglected to mention this originally), and now it
works perfectly.

I don't know how Access combines the report's Record Source with the
"wherecondition" from the DoCmd.OpenReport call, but clearly
"pagenumber=1 and pagenumber=2" is going to return an empty set.
pagenumber=2 does return three records -- once I remove the conflict.
And indeed, now if I enter a truly invalid value, I get the same error
message.

If you have time, please tell me where I should be reading to
understand why the detail section Format event procedure gets called at
all if there are no records to be formatted. I don't follow that, even
after reading the help for format event procedures. The procedure is
definitely attached to the detail section only.

Thank you very much!

Edward

 

[Ken Snell [MVP]]

The "Where" argument in the OpenReport call causes ACCESS to apply the
criterion just as if it had been in the original recordsource query. So, in
your case, it would indeed have done both tests (=1 and =2).

I have not yet completely figured out how/when/how often/why the Format
events occur in all reports, but it is obvious from your situation that the
report indeed is trying to format an empty report. I know that isn't an
answer, but it's the best that I know... < g >.

What you might do to prevent the problem that you see here is to code the
NoData event for the rport; in that event, you can display your own message
box and cancel the report; note, though, that you'll need to handle the 2501
error that will be returned to the calling form because the report's opening
was cancelled.

 

[edward]

Cool. Thanks, Ken!

Edward