More than 3 conditional formatting according to the value

[Joka]

Hi there!
I would be very pleased if anyone could help me with a VBA code for a MDB that i am creating.
I need that the back colour of a certain form field changes according to the respective value selected on its combo box.

A colour for each value:

Amarelo = Yellow
Laranja = Orange
Verde = Green
Azul = Blue
Vermelho = Red
Castanho = Brown
Preto = Black

I don't have any experience programming in VBA...

Looking forward for your help.

 

[vbaInet]

* What version of Access are you using?
* What is the Default View of your form?

 

[Joka]

I use Access 2007
how do i know the default view? sorry for my question but i know few about access.

 

[vbaInet]

Look in the Property Sheet of the form for the Default View. It should be the first item in the Format tab.

 

[Joka]

It´s "continuous Forms" ???

Excuse my lack of knowledge...

 

[boblarson]

You should use the Conditional Formatting (in Access 2007 it can handle up to 50 formats for a particular control) so you may want to convert it to 2007 file format (ACCDB). It will work much better than trying to do VBA, especially with continuous forms.

If you do convert, do it by importing everything into a new, blank ACCDB file. Don't just do the SAVE AS.

 

[boblarson]

You should use the Conditional Formatting (in Access 2007 it can handle up to 50 formats for a particular control) so you may want to convert it to 2007 file format (ACCDB). It will work much better than trying to do VBA, especially with continuous forms.

If you do convert, do it by importing everything into a new, blank ACCDB file. Don't just do the SAVE AS.

My Bad - I think it is 2010 which gives us 50. 2007 I think is still stuck in the 3 mode. So IGNORE MY LAST POST.

 

[vbaInet]

Have a look here:

http://support.microsoft.com/kb/304104

The idea is you use the FormatConditions collection of the control to add/remove format conditions for that control. In its simplest form:

With Me.txtID
        .Add acFieldValue, acEqual, 1
        .Item(0).BackColor = vbRed
        .Add acFieldValue, acEqual, 2
        .Item(1).BackColor = vbBlue
        .Add acFieldValue, acEqual, 3
        .Item(2).BackColor = vbGreen
End With

From the above I've added conditions to the txtID textbox to change it's BackColor to Red when the value is 1, blue when it's 2 and Green when it's 3.

 

[Joka]

boblarson, I converted the database to Access 2007 (accdb) and it stills with 3 conditional formatting conditions...

vbaInet, I apologize but i don't know what to do with the code you wrote i dont know VBA... Where do i insert it?

Many thanks

 

[vbaInet]

I wouldn't normally write code for someone if there's no little bit of knowledge of VBA. Simple reason being, you won't ever be able to manage the code.

Let's see how we can help you out anyway. What's the name of the control which you want to apply Conditional Formatting to?

 

[Joka]

you are very kind for helping me,
The control name is: Próxima Graduação

I need that the back colour of this form field changes according to the respective value selected on its combo box.

A colour for each value:

Option: Amarelo = colour: Yellow
Option: Laranja = colour: Orange
Option: Verde = colour: Green
Option: Azul = colour: Blue
Option: Vermelho = colour: Red
Option: Castanho = colour: Brown
Option: Preto = colour: Black

Many thanks

 

[vbaInet]

Put this code in the Open event of your form:

    With Me.[Próxima Graduação].FormatConditions
        .Delete
        .Add acFieldValue, acEqual, "Amarelo"
        .Item(0).BackColor = vbYellow
        .Add acFieldValue, acEqual, "Laranja"
        .Item(1).BackColor = RGB(245, 157, 86)
        .Add acFieldValue, acEqual, "Verde"
        .Item(2).BackColor = vbGreen
        .Add acFieldValue, acEqual, "Azul"
        .Item(3).BackColor = vbBlue
        .Add acFieldValue, acEqual, "Vermelho"
        .Item(4).BackColor = vbRed
        .Add acFieldValue, acEqual, "Castanho"
        .Item(5).BackColor = RGB(255, 194, 14)
        .Add acFieldValue, acEqual, "Preto"
        .Item(6).BackColor = vbBlack
    End With

 

[Joka]

I copied the code you gave me but it appears an error:

"Compile error: Sub or function not defined"

I send you a copy of all the code of the form, maybe it help... I thought it was easyer, sorry again for my insistance...


Private Sub Command30_Click()
On Error GoTo Err_Command30_Click


DoCmd.Close

Exit_Command30_Click:
Exit Sub

Err_Command30_Click:
MsgBox Err.Description
Resume Exit_Command30_Click

End Sub






Private Sub Próxima_Graduação_Enter()

With Me.[Próxima Graduação]
FormatConditions.Delete
Add acFieldValue, acEqual, "Amarelo"
Item(0).BackColor = vbYellow
Add acFieldValue, acEqual, "Laranja"
Item(1).BackColor = RGB(245, 157, 86)
Add acFieldValue, acEqual, "Verde"
Item(2).BackColor = vbGreen
Add acFieldValue, acEqual, "Azul"
Item(3).BackColor = vbBlue
Add acFieldValue, acEqual, "Vermelho"
Item(4).BackColor = vbRed
Add acFieldValue, acEqual, "Castanho"
Item(5).BackColor = RGB(255, 194, 14)
Add acFieldValue, acEqual, "Preto"
Item(6).BackColor = vbBlack
End With

End Sub

 

[vbaInet]

I mentioned in my last post that you should copy and paste the code into the OPEN event of your form.