How to use If in expression builder to make rectangle fill with co

[Guest]

Hi,
I have sub form in form which displays the values. The sub form is setting
to be continuous for and displays employee name, ID# and performance
category. In the performance category there are 3 kinds of categories. The
first one with green color contains the rang from S1 to S4, and the second
category with Yellow color from S5 to S8 and the last category with red
color start from S9 to S10. What I want to do is making a rectangle filled
with suitable color if performance meets with the category. For example if
employee x has performance category S2 then the rectangle is filled with
green color and so on.
Can any body help me?
Thanks!!

 

[Guest]

Jon

You can use conditional formatting for this. Right click the relevant
control on your form and select the Conditional Format option.

In Condition 1 use Field Value is Between "S1" and "S4" and choose your
green back ground colour.

Add an extra condition and use Field Value is Between "S5" and "S8" and
choose your yellow back ground colour.

Add another condition 3 for your last selection with field values between
"S9" and "S10".

Hope this helps.


Andy

 

[Guest]

Hi Jon

You can't use condition formating in this case (between) as your data is
text. You 'could' stipulate the background if the a certain condition is met
but as you are likley to have many of these it would not very well (if at
all).

You can code the back ground colour of a box like this

Private Sub Form_Load()
If Me.txt2 = 1 Then
Me.txt1.BackColor = vbGreen
If Me.txt3 = 2 Then
Me.txt1.BackColor = vbRed
If Me.txt4 = 3 Then
Me.txt1.BackColor = vbYellow
End If
End Sub

Of course I have used if something = 1, 2 or 3 but you can alter this and
the names of the controls (I have used txt1, 2, 3 and 4) will also need
altering but the basics are there.

I have also used OnLoad you may want to think about the AfterUpdate so your
users can "see" the results as soon as they make changes - users like to see
"stuff" happen.

Good luck

 

[Guest]

Wayne

I just tried that conditional formatting and it worked fine. I put ""'s
around the text conditions; i.e. "S1".

I hope you are not in the part of Manchester I am as it's pouring down.


Andy

 

[Guest]

Hi

Yes I am in North Manchester and it is raining very hard - oh well that's my
bit for the English Tourist Board (it "should" be sunny but ....). :-(

With condidtional formating you only have a very limited number of
condidtions that you can specify. I assumed that you results could be
anything from S1 up to S10 and you can not put that many conditions (unless
you use = Or etc...). VBA would be a better method as you can also put error
handeling on so if the user entered S11 then the colour could flash (not a
good idea just an example). As I don't really understadn what Jon is trying
to do my idea of codeing seems to be the best method.

Of course if there will only ever be a set number of S's then conditional
would work but - ..... Databases have a habbit of growing so it's normally
best to put in place the systems that "may" be required even if they are not
needed yet

 

[Guest]

Hi,
Thank you all for your help. I really appreciate your help.
One more question Wayne, what do you meant by txt1……
And box. is the box =rectangle and the txt is the filed of data.
Can you specify more please?
I have there fields in sub form one for category, one for employee ID and
one for Name. What I want to do is making a rectangle beside the category
filed to be filled with appropriate color as mentioned, like traffic sign.
Thanks!!

 

[Guest]

Hi Jon

I used txt1, txt2 etc as an example only. A "box" is an unbound text box
(sorry I should been more clear).

In form design, use the tool box to create the box (which you call
rectangle). Call this new box txt1

Right Click the form outside the detail area (it's the grey bit outside the
area where you have placed all the "controls" (which are boxes, lables, etc)

Select properties box and then select the event column. In this column
select the OnLoad row and click the build option (...). Select code. You
will see this






Your code will look something like this

Private Sub Form_Load()

End Sub


You need to add some code between the lines so your code will now look
something like this.

Private Sub Form_Load()
If Me.category = 1 Then
Me.txt1.BackColor = vbGreen
If Me.category = 2 Then
Me.txt1.BackColor = vbRed
If Me.category= 3 Then
Me.txt1.BackColor = vbYellow
End If
End Sub


Ensure that the control holding the category data is actually called
category. To check this (in design view) right click the control and oprn
the properties box. Select the "Other" column and chack that the "name" of
the control is "category".

When you open the form now the new box (txt1) will be the right clour (you
can change the colours in the code (after the "vb". example vbRed or vbBlack
- leave the vb in place just change the colour).

You can also use the AfterUpdate event of a control on your form to change
the colour of the new box. Search this forum for details on the AfterUpdate
methods.

 

[Guest]

Thanks Wayne very much. What about if I use 3 text boxes, like traffic sign,
how the code will be?

 

[Eddie]

Hello all,

I got a problem much like this one. What I want to do is set up a text box
that refers to a option group with values from 1 to 4. When the value changes
in the option group I want the text in the new text box to change and give a
diffrent message for each Option value.

Any Suggestion? by the way using access 2003 if that makes a diffrence.

 

[Jeff Boyce]

Eddie

One approach would be to add code to the option group's AfterUpdate event,
something like (untested aircode follows):

Select Case grpYourOptionGroup
Case 1
Me.txtYourTextBox = "Message1"
Case 2
...

Case Else
Me.txtYourTextBox = "Your Alternate Message"
End Select

Note -- if you are trying to do this to STORE a value in a table, don't
bother! If you know the value of the option group, your "textbox" value is
"calculated", and doesn't need to be (redundantly) stored.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eddie]

Jeff,

Tried as you suggested. Added the following

Private Sub Operational_Status_AfterUpdate() <- my text box
Select Case Me![Priority] <- Option group
Case Is = 1
Me![Operational_Status].Caption = "Umm not yet"
Me![Operational_Status].FontSize = 28
Case Is = 2
Me![Operational_Status].Caption = "Getting tired"
Me![Operational_Status].FontSize = 28
Case Is = 3
Me![Operational_Status].Caption = "Did it work"
Me![Operational_Status].FontSize = 28
Case Is = 4
Me![Operational_Status].Caption = "Hope this works"
Me![Operational_Status].FontSize = 18
End Select
End Sub

The text box is coming up blank. No error messages so Code should be ok. In
the properties for the text box the control source is marked as Priority to
match Option group.

Its still not functioning as desired. any other suggestions?

 

[Jeff Boyce]

Why are you doing this in the "Operational_Status_AfterUpdate" event, and
not the Priority_AfterUpdate event?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eddie]

Jeff,

This is the full code atm.

Private Sub fraPriority_AfterUpdate() <--- Option group
Select Case Me![fraPriority] <--- database reference
Case Is = 1
Me![Label14568].Caption = "Safety"
Me![Label14568].FontSize = 28
Case Is = 2
Me![Label14568].Caption = "Performance"
Me![Label14568].FontSize = 28
Case Is = 3
Me![Label14568].Caption = "Nuisance"
Me![Label14568].FontSize = 28
Case Is = 4
Me![Label14568].Caption = "Corrective Maintenance"
Me![Label14568].FontSize = 18

End Select

Select Case Me![Text14653] <-- My text box
Case Is = 1
Me![14653] = "Inop"
Me![14653].FontSize 18
Case Is = 2
Me![14653] = "Limited"
Me![14653].FontSize 18
Case Is = 3
Me![14653] = "Good"
Me![14653].FontSize 18
Case Is = 4
Me![14653] = "Needs inspection"
Me![14653].FontSize 18

End Select

End Sub

Now getting run time error 2465

 

[Jeff Boyce]

Eddie

We're not there. We don't know what you mean by "Label14568" or "Text14653"
or "[14653]"...

Are those the names of the controls?

I don't have the "description" of all the possible errors Access might
report -- what description comes with the error you are receiving?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eddie]

Jeff,

Let me first say thank you for trying to help me. By now your probly ready
to shoot me. Access and VBA are not me best areas.

Let me see what i can clear up.
option group - refers to the date with the values that are being input into
the database. (range 1-4)
Work Sheet Header - Is the Section of the work sheet that is currently
Showing the Caption for each of the 4 values.
My text box - Where I would like to add my own status message that changes
with the 4 values from the option group. So if they input 3 it will show
"Message3" and so on.

Private Sub OptionGroup_AfterUpdate()
Select Case Me![Optiongroup]
Case Is = 1 >
Me![WorksheetHeader].Caption = "Safety"
Me![WorksheetHeader].FontSize = 28 >
Case Is = 2 >
Me![WorkSheetheader].Caption = "Performance" >
Me![Worksheetheader].FontSize = 28 >
Case Is = 3 >
Me![Worksheetheader].Caption = "Nuisance" >
Me![Worksheetheader].FontSize = 28 >
Case Is = 4 >
Me![Worksheetheader].Caption = "Corrective Maintenance" >
Me![Worksheetheader].FontSize = 18 > >

End Select

Select Case Me![Mytextbox]
Case Is = 1
Me![Mytextbox] = "Inop"
Me![Mytextbox].FontSize 18
Case Is = 2 >
Me![Mytextbox] = "Limited" >
Me![Mytextbox].FontSize 18 >
Case Is = 3 >
Me![Mytextbox] = "Good" >
Me![Mytextbox].FontSize 18 >
Case Is = 4 >
Me![Mytextbox] = "Needs inspection"
Me![Mytextbox].FontSize 18

Thank you again. About ready to delete this file and hiree someone to redo it.

 

[Jeff Boyce]

Eddie

See comments in-line below...

Jeff,

Let me first say thank you for trying to help me. By now your probly ready
to shoot me. Access and VBA are not me best areas.

Let me see what i can clear up.
option group - refers to the date with the values that are being input
into
the database. (range 1-4)

I don't understand how an option group can refer to a date.

Work Sheet Header - Is the Section of the work sheet that is currently
Showing the Caption for each of the 4 values.

A work sheet header doesn't sound like a table or a form in Access. How are
you using this term?

My text box - Where I would like to add my own status message that changes
with the 4 values from the option group. So if they input 3 it will show
"Message3" and so on.

I will assume this textbox is NOT bound to an underlying data field in a
table, and that you are only using this textbox in a form to display (not
store) a message to the user.

Private Sub OptionGroup_AfterUpdate()
Select Case Me![Optiongroup]
Case Is = 1 >
Me![WorksheetHeader].Caption = "Safety"
Me![WorksheetHeader].FontSize = 28 >
Case Is = 2 >
Me![WorkSheetheader].Caption = "Performance" >
Me![Worksheetheader].FontSize = 28 >
Case Is = 3 >
Me![Worksheetheader].Caption = "Nuisance" >
Me![Worksheetheader].FontSize = 28 >
Case Is = 4 >
Me![Worksheetheader].Caption = "Corrective Maintenance" >
Me![Worksheetheader].FontSize = 18 > >

End Select

Select Case Me![Mytextbox]
Case Is = 1
Me![Mytextbox] = "Inop"
Me![Mytextbox].FontSize 18
Case Is = 2 >
Me![Mytextbox] = "Limited" >
Me![Mytextbox].FontSize 18 >
Case Is = 3 >
Me![Mytextbox] = "Good" >
Me![Mytextbox].FontSize 18 >
Case Is = 4 >
Me![Mytextbox] = "Needs inspection"
Me![Mytextbox].FontSize 18

So, as asked in my previous response, what is the "description" that
accompanies your error number?

Thank you again. About ready to delete this file and hiree someone to redo
it.

Good luck

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Eddie]

Jeff,

Let me say thanks once again for all your help. I actually figured it out.
Its also working very well I must say. The text box was not bound to the
tables and was only a status item used to prompted users when making
selections. the "date" i refered too was a typo. Was supposed to be "Data".
Sorry if i confused you. I did it to myself. (been 10 yrs since I did
programming)

To what cleared up my problem: Changed the text box to a lable.
Used the coding you suggested to reflect the Option boxes active data. It in
turn would prompted my message to users as its supposed to.

If you want when back in the office on monday will post the code.
(Now to begin a whole new jurney)