In a report -Using a calculated field in Select Case causes Error

[Maverick]

I am trying to have certain boxes become visible or not based on the value of
a calculated field in a report. However, it gives me Error 2424 and points to
the calculated field Me.calcOverallRating as the problem. Any ideas why or
how to fix this?

My code is as follows:

Select Case Me.calcOverallRating

Case 0
Me.boxDoesNot.Visible = True

Case 0.5 To 1.4
Me.boxMeets.Visible = True

Case 1.5 To 2.4
Me.boxExceeds.Visible = True

Case 2.5 To 3
Me.boxOutstanding.Visible = True

Case Else
Me.boxDoesNot.Visible = False
Me.boxMeets.Visible = False
Me.boxExceeds.Visible = False
Me.boxOutstanding.Visible = False

End Select

[Alex Dybenko]

Hi,
where do you run this code? You have to run it in corresponding report's
section format event

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Maverick" <Mave...@discussions.microsoft.com> wrote in message
news:100A6A7C-D6A8-453E...@microsoft.com...

[Maverick]

I've tried On Current, On Load and On Open. None work. The control is
available from the list after I type Me. so I know it's a valid control and
not typed incorrectly.

[Alex Dybenko]

Hmm, are we talking about report? not sure it has current event. try Format
even

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

"Maverick" <Mave...@discussions.microsoft.com> wrote in message

news:6B0DB2CE-62E5-4D34...@microsoft.com...

[Klatuu]

What version of Access?
Up through 2003, there is no Load or current event in a report. I think
there is a Load event in 2007, but I don't have it here to check.
What Alex is saying is your post subject says "In a report", but you are
describing events in a form.

If it is, in fact, a report, each report section has 3 events Format, Print,
and Retreat. To do what you are wanting to do, you should put your code in
the Format event of the report section the control you are populating is in.

Now, one other thing. What is appears you are doing is making either a
label or a text box with a value in it visible that will present a specific
message. If that is the case, I would do it differently. You really only
need one control. I would use a label and just change the caption. Also,
notice the difference in the Select Case statement. The Select Case executes
the first True case then jumps to the End Select, so you can use this style:

Select Case Me.calcOverallRating

Case 0

Me.lblRating.Caption = "Does Not Meet"
Case Is < 1.5
Me.lblRating.Caption = "Meets"
Case Is < 2.5
Me.lblRating.Caption = "Exceeds"
Case Is < 3
Me.lblRating.Caption = "OutStanding"
Case Else
Me.lblRating.Caption = vbNullString
End Select

--
Dave Hargis, Microsoft Access MVP

[Maverick]

Thanks for the suggestions. However, none of it applies in this situation.
What you are saying does pose a problem for compatability purposes though.

I have Access 2007, but am running it in compatability mode for 2002/2003
because my users have a mix of 2003 and 2007. The events for a report are
pretty much the same as for a form. The three events you mention do not exist
for me. I'm not sure how the difference is handled when 2003 tries to run the
events if they don't have the same events. However, that's a different bridge.

I can't put the event in On Format as it doesn't exist in 2007. Also, I have
four hidden rectangles that are used to highlight certain text when a
specific criteria is met. Thus, it is not captions with the words that I
need. The words are already there and I want the recangle to be visible
around the words when calcOverallRating is a certain value.

Thanks again for the input. Hopefully with this additional information my
request will be more clear.

[Klatuu]

The events do exist, they always have. You are not looking in the right place.

Let's use the Detail section as an example.
In design view, right click on the bar that says Detail.
Click on Properties.
Select the Events tabl.
In order they are:

On Format
On Print
On Retreat

If you have users in both 2003 and 2007, you should be doing your
development in 2003.

[Maverick]

Okay. I understand now. I thought you meant the events for the report itself.
I put the code in the On Format event and the error went away. However,
nothing happens. I put in Debug.Print and some test text to see if anything
executes for each case and nothing showed up in the Immediate window. This
tells me the code is not executing. The calculated value is .7 and therefore
should fall under Case .5 To 1.4 and I can clearly see it on the header
section I'm running the code from.

[Klatuu]

Is the text box your are testing on in the same section you code is in?
You can tell what it is doing by opening the VB Editor. Put your cursor on
the first executable line of the code in the Sub and press F9. The line will
change color.
Click on the Save icon. Now, run your report. When it gets ot that line of
code, the VB editor will open and you will see the line highlighted in
yellow. The line has not executed, but it will be the next to execute.
Pressing F8 will cause the line to execute. You can use the Debug menu to
see other options. You can hover over a variable name and see its value or
you can set watch values.

When you are done and have stopped the code, be sure to remove the break
point by going back to that line and pressing F9 again to remove the
breakpoint and save the code; otherwise, it will continue to stop there.

[Maverick]

I placed the breakpoint at the first executable line (Select Case), saved,
then ran the report. When I opened the VB Editor, there was no yellow
highlighting. I suppose this means that it didn't even start to execute the
code.

[Klatuu]

It appears it did not execute. You should not have to open the editor, it
should open on its own and suspend execution of the report.

Is calcOverallRating a control in the same section?

[Maverick]

Yes. The control being used in the code is in the same section as the code.

[Klatuu]

Just for testing purposes, put a message box in the event so you can see if
it is executiong. Just something like:

MsgBox "I am in the Detail Format Event"

[Maverick]

Nope. Nothing happened.

[Klatuu]

Does the report have a record source?
That is very strange.

[Maverick]

Yes. It's a query. In addition, it is filtered to find only one record.

I tried creating a blank database, turning AutoRename off, and importing the
old database in. That didn't help.

[Klatuu]

I am really at a lose now. If there are any records being returned at all,
that event should fire.

[Maverick]

I definitely have a result in the report. I too am at a loss. I might give up
my Novice status and demote myself down to Newbie.

[Klatuu]

What version of Access?
What version of Windows?
Don't give up.
There has to be a reason for this.

[Maverick]

I'm using 2007 in compatability mode for 2002/2003 on a Vista machine. Just
for fun, I ran the following code with no result posting in the Immediate
Window:

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)

Debug.Print "Huh"

End Sub

[Maverick]

As another test I put the code in the On Click event and it works perfectly
fine.

[Klatuu]

If your format is 2002/2003 and you are doing your development in 2007, you
may get strange results like that. the general rule is do your development
is the version of the format. In addition, being on Vista, you don't know
what might happen :)

I haven't installed Vista yet. I don't even have a computer that will
support it. My brother bought a lap top that came loaded with Vista and
became very frustrated, so he bought a different disk for it and loaded it
with XP.

[Maverick]

I figured out why the On Format event wasn't triggering. 2007 has an
additional report view than prior versions. I am looking at the report in
this new view. When I open the report in Print Preview, the code fires.

I guess I'm going to have to have the report open in Print Preview instead
of Form View in order to make this work. I really would have liked this code
to trigger when the report opened so that it worked no matter what view.

Oh well. It's always good to stop the bleeding, no matter what kind of wound.

Thanks for sticking with me.