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

[fire2ice]

I am trying to have certain rectangles become visible or not based on the value of a calculated control in a report. However, it gives me Error 2424 and points to the calculated control Me.calcOverallRating as the problem. The control is available in the list after I type Me. and therefore is both a valid control and correctly typed.I'm using Access 2007 in compatability mode 2002/2003. I've tried running it in the On Current, On Load and On Open events of the report.

I've also put it in the On Format event of the section the control is on. The error goes away, but nothing happens. I put in the code Debug.Print and test text for each case but nothing shows up in the Immediate Window.

Any ideas why or how to fix this? Thanks in advance.

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

 

[MSAccessRookie]

I am trying to have certain rectangles become visible or not based on the value of a calculated control in a report. However, it gives me Error 2424 and points to the calculated control Me.calcOverallRating as the problem. The control is available in the list after I type Me. and therefore is both a valid control and correctly typed.

I'm using Access 2007 in compatability mode 2002/2003. I've tried running it in the On Current, On Load and On Open events. 2007 does not have the On Format event.Any ideas why or how to fix this? Thanks in advance.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

I had a hard time reading the code so I reformatted it. I am interested in the answer too, but since I am still a VBA Novice and not familiar with the proper syntaxes involved, I will pass on any advice except to say that help is much more easily attained when we can read the code that is suspected to have a problem. I am sure that someone here will help you out.

 

[fire2ice]

I had a hard time reading the code so I reformatted it. I am interested in the answer too, but since I am still a VBA Novice and not familiar with the proper syntaxes involved, I will pass on any advice except to say that help is much more easily attained when we can read the code that is suspected to have a problem. I am sure that someone here will help you out.

I didn't preview my post and noticed after I posted that it was messed up. It seems that we both reformatted it at the same time. LOL

 

[MSAccessRookie]

I didn't preview my post and noticed after I posted that it was messed up. It seems that we both reformatted it at the same time. LOL

At least I got the format right. Not bad for a Rookie. Good luck getting a reply.

 

[fire2ice]

At least I got the format right. Not bad for a Rookie. Good luck getting a reply.

Not bad at all.

 

[Banana]

What is the text for Error 2424?

Also, consider using a variable instead of referencing a control directly:

Dim foo as Double

foo = Me.calcOverallRating

Select Case foo
...

 

[gemma-the-husky]

msgbox(accesserror(2424))

= a field/control etc, access cant find

if you have a field/control on the form, you probably need the expression

me!calcoverallrating, rather than me.calcoverallrating in the select case
or just calcoverallrating without the me at all

one further note is that your cases do not cover every possibility, so you may need
a) a case else
and b) to allow for a null value in the field/control

 

[fire2ice]

What is the text for Error 2424?

Also, consider using a variable instead of referencing a control directly:

Dim foo as Double
 
foo = Me.calcOverallRating
 
Select Case foo
...

Runtime Error '2424'
The expression you entered has a field, control, or property that Microsoft Access can't find.

Using the variable didn't do anything.

 

[Banana]

Sounds like that for some reason that control isn't available at the time you ran that code. Since you've already tried several events (I'd use OnFormat event) with same results, I'm worried that this is a issue of faulty referencing.

Check if you have AutoCorrupt enabled. If this didn't help, try creating a blank database and import everything in it then re-run it.

 

[fire2ice]

msgbox(accesserror(2424))

= a field/control etc, access cant find

if you have a field/control on the form, you probably need the expression

me!calcoverallrating, rather than me.calcoverallrating in the select case
or just calcoverallrating without the me at all

one further note is that your cases do not cover every possibility, so you may need
a) a case else
and b) to allow for a null value in the field/control

This is a report, not a form. I've tried all three ways (Me. and Me! and no Me).

There is a Case Else statement which makes all the rectangles not visible.

 

[fire2ice]

Because the code doesn't give me an error when I put it in the On Format event of the section where the calculated control is, I decided to debug the code there. I put a breakpoint in and oddly it doesn't seem to even be firing.

 

[Banana]

Sorry, reports aren't my forte, so I'm stumped about OnFormat not firing at all.

 

[fire2ice]

Sounds like that for some reason that control isn't available at the time you ran that code. Since you've already tried several events (I'd use OnFormat event) with same results, I'm worried that this is a issue of faulty referencing.

Check if you have AutoCorrupt enabled. If this didn't help, try creating a blank database and import everything in it then re-run it.

I created a blank database, turned AutoRename off, and imported everything into the new database. This didn't help. It was worth a shot though.

I think that because this database is only a week old and I never had to change the name of any fields in the tables, that there wasn't much chance of corruption (note I said much, not no chance).

 

[fire2ice]

Sorry, reports aren't my forte, so I'm stumped about OnFormat not firing at all.

Me too. It's odd that it doesn't even execute at all.

 

[Banana]

Hmmm....

A shot in dark, and I know you said it does show in the intellisense, but can you verify that the control you're referring *IS* the control you want; Access has that pesky habit of naming the control exactly same as the controlsource/field list of the form/report. See if renaming the control helps.

Honestly, I'm grasping at straws here.

 

[fire2ice]

Hmmm....

A shot in dark, and I know you said it does show in the intellisense, but can you verify that the control you're referring *IS* the control you want; Access has that pesky habit of naming the control exactly same as the controlsource/field list of the form/report. See if renaming the control helps.

Honestly, I'm grasping at straws here.

I went one step further and created an unbound textbox with a brand new name. It had a calc making it equal to the original calc control. I used this in the reference and it still didn't work.

It feels like I'm trying to breathe through a straw...a very small straw. That feeling is one of impending doom.

 

[fire2ice]

I put the code in the On Click property of the section of the report with the control...and the code works fine there. For some reason, the On Format event just isn't firing when the report is opened.

 

[Banana]

For hell of it...

Make a new blank report, drop in a textbox, then put a breakpoint on the OnFormat event and see if it fires in that report.

 

[fire2ice]

For hell of it...

Make a new blank report, drop in a textbox, then put a breakpoint on the OnFormat event and see if it fires in that report.

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.

 

[Banana]

and to think you were getting help from someone who didn't even have 2007! Talk about bad advices!

Glad you figured it out, though!