By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,137 Members | 1,981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,137 IT Pros & Developers. It's quick & easy.

Reports + If Statement Problem

P: 15
Hi All,

Just a quick question please.

I have a report that feeds off a query relating to all processed jobs. One of the fileds in the query is the current status of the Job. The job can either be "open", "closed" or "cancelled".

The records of the query appear in the detail section of the report.

Now i wanted to add the following to the format event in the detail section of the report, but it doesn't work.. I keep getting errors regarding the statement (invalid reference to the property visible). Any suggestions? The fields are present on the form. The status field is set to invisible as well. I have also tried this int he Open event for the report, and that didn't work either.

If Me.Status = "Cancelled" Then
Me.lbl_cancelled.Application.Visible = True
ElseIf Me.Status = "Closed" Then
Me.lbl_cancelled.Application.Visible = False
End If

' The label cancelled is suppose to appear on a record if the Job is considered cancelled.

Thanks in advance.
Dec 4 '06 #1
Share this Question
Share on Google+
12 Replies


NeoPa
Expert Mod 15k+
P: 31,660
Tell us exactly where the error occurs and exactly what the error message is and we'll see what we can find for you.
Dec 4 '06 #2

P: 15
Hi NeoPa,

So when i place the if statement in the Detail section under the On Format event, i get the error " Run-time error 2455: You entered an espression that has an invalid reference to the property visible."

When i place it in the Open Event for the Report, I get the error " Run-time error 2427: You enetered an expression that has no value."

I have even tried modifying the code to:

'Placed in the Detail section, under the On Format Event

If Me.Status = "Cancelled" Then
Me.lbl_cancelled.Properties.Application.Visible = True
ElseIf Me.Status = "Closed" Then
Me.lbl_cancelled.Properties.Application.Visible = False
End If

Nothing seems to work.

I'm just trying to make the label called cancelled appear for the cancelled records, and not appear for the rest. I have set the property of it invisible for now.

Please advise.

Regards
Dec 4 '06 #3

NeoPa
Expert Mod 15k+
P: 31,660
Hi NeoPa,

So when i place the if statement in the Detail section under the On Format event, i get the error " Run-time error 2455: You entered an espression that has an invalid reference to the property visible."

When i place it in the Open Event for the Report, I get the error " Run-time error 2427: You enetered an expression that has no value."

I have even tried modifying the code to:

'Placed in the Detail section, under the On Format Event

If Me.Status = "Cancelled" Then
Me.lbl_cancelled.Properties.Application.Visible = True
ElseIf Me.Status = "Closed" Then
Me.lbl_cancelled.Properties.Application.Visible = False
End If

Nothing seems to work.

I'm just trying to make the label called cancelled appear for the cancelled records, and not appear for the rest. I have set the property of it invisible for now.

Please advise.

Regards
Now it all makes sense.
Expand|Select|Wrap|Line Numbers
  1. If Me.Status = "Cancelled" Then
  2.     Me.lbl_cancelled.Visible = True
  3. ElseIf Me.Status = "Closed" Then
  4.     Me.lbl_cancelled.Visible = False
  5. End If
Or, another way to do it would be (your code didn't handle "Open") :
Expand|Select|Wrap|Line Numbers
  1. Me.lbl_Cancelled.Visible = (Me.Status = "Cancelled")
Dec 4 '06 #4

P: 15
Now it all makes sense.
Expand|Select|Wrap|Line Numbers
  1. If Me.Status = "Cancelled" Then
  2.     Me.lbl_cancelled.Visible = True
  3. ElseIf Me.Status = "Closed" Then
  4.     Me.lbl_cancelled.Visible = False
  5. End If
Or, another way to do it would be (your code didn't handle "Open") :
Expand|Select|Wrap|Line Numbers
  1. Me.lbl_Cancelled.Visible = (Me.Status = "Cancelled")
Hi NeoPa,

Thanks again for your assistance.

I tried both code drop, neither seemed to work. I placed the first one in the Detail section under the On Format event. And i tried the second in both the On Format event in the Detail Section and the Open event for the Report.

The reference to "visible" is not being picked up in VBA. That's why i had to use "application.properties.visible"

Please advise if there is another way to ge around this.

Regards.
Dec 5 '06 #5

NeoPa
Expert Mod 15k+
P: 31,660
What are the names of the (relevant) controls on your report?
You're referring to them as if they are the record fields, but I suspect the names are different.
Dec 5 '06 #6

P: 15
What are the names of the (relevant) controls on your report?
You're referring to them as if they are the record fields, but I suspect the names are different.
Hi NeoPa,

The relevant controls on my report contain the same names as those of there Control Source. Since the report was created using the Report wizard, i just used the relevant query to feed it.

For example, There is a dropdwon box that's called Status in my Table, That field is also placed in my report and is also called Status with the source control set to Status.

So when i say "Me.Status", i'm referring to the field Status in my Table.

I hope that is what you requested...
Dec 5 '06 #7

Expert 5K+
P: 8,434
As a workaround until you find the real answer, could you try setting the forecolor the same as the backcolor, or something?

Um... here's something which may be of interest...
You can use the Visible property to hide a control on a form or report by including the property in a macro or event procedure that runs when the Current event occurs. For example, you can show or hide a congratulatory message next to a salesperson's monthly sales total in a sales report, depending on the sales total.
So perhaps you can't use it in the On Format?
Dec 5 '06 #8

NeoPa
Expert Mod 15k+
P: 31,660
Hi NeoPa,

The relevant controls on my report contain the same names as those of there Control Source. Since the report was created using the Report wizard, i just used the relevant query to feed it.

For example, There is a dropdwon box that's called Status in my Table, That field is also placed in my report and is also called Status with the source control set to Status.

So when i say "Me.Status", i'm referring to the field Status in my Table.

I hope that is what you requested...
And you have a control called lbl_cancelled?
Which section of the report is it on?
Which event is the code running within?
Dec 5 '06 #9

Expert 5K+
P: 8,434
And you have a control called lbl_cancelled?
Which section of the report is it on?
Which event is the code running within?
Good point. I made the mistake of reading between the lines (generally a bad move when debugging). I thought the OP had said that the control was on the detail section of the report, but on re-reading carefully, it was only implied. From context, I still think it is (and that my prior post may point to the answer), but we don't actually know.
Dec 5 '06 #10

NeoPa
Expert Mod 15k+
P: 31,660
There shouldn't be a problem accessing the .Visible property from the OnFormat event.
I'm pretty sure, from memory, that I've used it before. We'll see how reliable my memory is ;).
Dec 5 '06 #11

Expert 5K+
P: 8,434
There shouldn't be a problem accessing the .Visible property from the OnFormat event.
I'm pretty sure, from memory, that I've used it before. We'll see how reliable my memory is ;).
I was just going by what the help said. I'm rather wary of putting forward an opinion on this Access/form stuff, because from a VB6 viewpoint it seems to work in such weird ways.

Prime example - you can't set the value of a control while it's not visible? C'mon, what's with that?! :(
Dec 5 '06 #12

NeoPa
Expert Mod 15k+
P: 31,660
That's just a bug - NO, a feature ;)
Dec 6 '06 #13

Post your reply

Sign in to post your reply or Sign up for a free account.