Reference a Bound Combo Box In Report Through VBA

[icenemesis]

Hello,

I am using Access 2000.

I have a combo box that is bound to a query that lists the status' from the following table.

Status Table
---------------------------------
id name
---------------------------------
1 Identified
2 Development
3 Owner Review
4 Approver Review
5 Signed Off

the combo box shows the name and is bound by the id.

In the report I am creating I want to set a rectangle backcolor to a specific color depending on what status the certain record is.

So if status is 1, backcolor = yellow, etc

I can set the back color easily enough, but I cannot figure out how to check which status is in the combo for the life of me. The Text or Value property aren't there, etc. I am fairly knowledgable with Access and VBA but I cannot wrap my head around this. Any ideas?

[JConsulting]

Hello,

I am using Access 2000.

I have a combo box that is bound to a query that lists the status' from the following table.

Status Table
---------------------------------
id name
---------------------------------
1 Identified
2 Development
3 Owner Review
4 Approver Review
5 Signed Off

the combo box shows the name and is bound by the id.

In the report I am creating I want to set a rectangle backcolor to a specific color depending on what status the certain record is.

So if status is 1, backcolor = yellow, etc

I can set the back color easily enough, but I cannot figure out how to check which status is in the combo for the life of me. The Text or Value property aren't there, etc. I am fairly knowledgable with Access and VBA but I cannot wrap my head around this. Any ideas?

I do apologise, but your question is kind of misleading. You're building a report...and we have to assume that it's data comes from the same source as some form? It appears as if you're storing the Numeric value from the combo box into a field in the record. And can we assume that this field is available to your report?
The rest is simple conditional formatting, and can be done by selecting the textbox that you want to change colors, and going to the menu, etc...

Can you clarify a little for us about what the source for the report is...and how it ties in with the value you select in the combo box?

[icenemesis]

Sorry about that I read it again and I see exactly where your coming from.

I have a report and the source is a query. In this query I have id, name, status. The status in the query is looking up values from another table. This is like I said before from the Status table. - Forget about this part, see next post for reason why.

Anyways. In the report, when it gets opened for viewing, I want a rectangle to be a certain color depending what the value of status is. When I drag the status field onto the report, it shows up as a combo box with the drop down arrow, but only while in design view. When viewing the report is just shows the value of the field which in this case is either Identified, Development, etc.

This is proving to be harder to explain than I though. I know what I'm trying to say but putting it work words is difficult.

Unfortunately you cannot do conditional formatting on a rectangle. But I may have an idea now that you mentioned that. I'll give that a shot while you guys try to figure out my horrible attempt at explaining a problem.

Hope this helped clarify a bit. If not.. I can give it another try. Lol. Thanks though!

[icenemesis]

My idea didn't work , but it wouldnt matter if it did because conditional formatting only allows 3 formats when I need upwards of 5.

Anyways what I was going to try to do is put the status field in behind the id and name and kind of turn it into the rectangle in a sense. Change its text color and background to be the same color with conditional formatting but that proved not to work. For what reason.. I'm not sure, but like I said the 3 formats would prove to be a problem.

Alright, well see how I can do at trying to explain this again.

Each id and name has a status associated with it. I want a rectangle behind the id and name that will change its color on report load depending on what the status associated with that id and name is.

-----------------------------
| 100-100-100 Name |
-----------------------------

It would look something like that but would be say... Yellow if status is identified, blue if status is development, etc.

I appologize. I lied in that last reply. The report is based off of a table which has id name and status. And status in that table is a lookup to the status table which included the id and name for each status which is:

1 Identified
2 Development
3 Owner Review
4 Approver Review
5 Signed Off

It's been a long day and my brain is some what mush...

Anyways, so what I was thinking of doing was going into VBA and using the On Open event. and in there somehow check to see what status is for each row that would be shown in the report and set teh rectangle backcolor according to that.

Private Sub Report_Open(Cancel As Integer)

' do the check in here.

End Sub

I might be thinking about that all wrong and there might be an easier way to do this. ANyways, thats my explaination hope this one helped out a bit more than the last two.

[JConsulting]

My idea didn't work , but it wouldnt matter if it did because conditional formatting only allows 3 formats when I need upwards of 5.

Anyways what I was going to try to do is put the status field in behind the id and name and kind of turn it into the rectangle in a sense. Change its text color and background to be the same color with conditional formatting but that proved not to work. For what reason.. I'm not sure, but like I said the 3 formats would prove to be a problem.

Alright, well see how I can do at trying to explain this again.

Each id and name has a status associated with it. I want a rectangle behind the id and name that will change its color on report load depending on what the status associated with that id and name is.

-----------------------------
| 100-100-100 Name |
-----------------------------

It would look something like that but would be say... Yellow if status is identified, blue if status is development, etc.

I appologize. I lied in that last reply. The report is based off of a table which has id name and status. And status in that table is a lookup to the status table which included the id and name for each status which is:

1 Identified
2 Development
3 Owner Review
4 Approver Review
5 Signed Off

It's been a long day and my brain is some what mush...

Anyways, so what I was thinking of doing was going into VBA and using the On Open event. and in there somehow check to see what status is for each row that would be shown in the report and set teh rectangle backcolor according to that.

Private Sub Report_Open(Cancel As Integer)

' do the check in here.

End Sub

I might be thinking about that all wrong and there might be an easier way to do this. ANyways, thats my explaination hope this one helped out a bit more than the last two.

I think I've grasped most of this now...so I'll hopefully point you in the right direction...I won't insult you by developing the solution as you seem to know what's what already.

Use the on_format event of your report.

create a Case statement based on the value of the field on the box.
Select Case me.SomeField
case x
me.box1.backcolor = vbyellow
case y
me.box1.backcolor = vbGreen
case z
me.box1.backcolor = vbRed
case else
me.box1.backcolor = vbWhite
end select

hopefully that will point you in the right direction AND I hope I'm not misreading again :)
J

[icenemesis]

That looks to be exactly what I need. And it worked perfectly! Thanks a ton for your help!

So how does that on format work?
Does it get called for every line that gets put into the report?

[JConsulting]

That looks to be exactly what I need. And it worked perfectly! Thanks a ton for your help!

So how does that on format work?
Does it get called for every line that gets put into the report?

Yes...along with a lot of other events that are record specific.

Here is a good place to start learning about the Order of events that occur with specific objects in your database.

http://office.microsoft.com/en-us/access/HP051867611033.aspx

J

[icenemesis]

Awesome! Thanks again!

I knew about all the rest but the reports and the order stuff is called was new to me! Appreciate it!