Change color on textbox in a Continuous Forms

[barby.pava]

Hi i have a Continuous Forms I need the vba code for change the color of the textbox.( I have already used all 3 condition in the conditional formatting ).

i need if Runreserve start with "G" you need to color the text box in Green.



I can I do this?

[CJ_London]

these links provide example vba code you require for access 2002

https://support.microsoft.com/en-us/kb/304104
https://blogs.msdn.microsoft.com/fri...cess-with-vba/

[Micron]

If you use the change event as the second link suggests, it will fire upon each character entered when based on the post, it need fire only once. Maybe the OP would like to validate when this coloring has to take place. Only after something is entered and the user chooses another control (or the first control otherwise loses the focus or the record is updated)? Or perhaps only when a button was clicked. Or maybe some of the first 3 formats need to be applied on form opening too (likely not this 'starting with G thing). Looks like a lot of code there - especially how the colors are defined. Ajax, are you referring OP to that page because you're saying, for example, that the vb color constants post date version 2002? I might be fooling myself, but I suspect it could be simpler than what's showing there. Maybe I just think much of the current properties/methods available have been around longer than what they have been.

[CJ_London]

If you use the change event as the second link suggests

Think you need to reread the link

the OP asked for vba to add format conditions - in the second link, the code is adding conditions to a control called txtresult. To do this, a button called cmdChange is clicked (as opposed to say the form open event) - I presume so you can see the before and after.

I was referring to both links because the OP is using 2002, I wasn't sure if the code changed in 2007 and later.

Until the OP has used and understands the code, the start with 'G' condition is irrelevant, that is a condition to be applied in the code.

[Micron]

ya, I focused on the change part of Private Sub cmdChange_Click()
Must've been the wobbly pops at work again. I don't see this as being too difficult (applying all formatting conditions in code so they're in one place). Then again, sometimes I don't see very well.

[barby.pava]

sorry but i do not understand!!!

[CJ_London]

you asked

I need the vba code

read the links provided - they provide the vba code

[barby.pava]

ok but how can I tell if (runselected) start with "g/" then color the runselected green or if start with "y" color yellow?

this is the part that i do not understand. I can not add a buttom because the form that i have is from a crosstab query

[CJ_London]

in the same way you would with the conditional formatting screen

expression is.... left(myfield,1)="g" and in the second post substitute as indicated by the colored text in the example
Set objFrc = Me![txtResult].FormatConditions. _
Add(acExpression, , (Format(Now(), “ddd”) = “Sat”))

the second link I provided uses a button to set up the format conditions - as suggested in post #4 I would expect you would use something like the form open or load event instead

[barby.pava]

Is this correct?

Private Sub Detail_Format()
‘ This subroutine demonstrates the use of FormatCondition objects
‘ to add formatting to a text box. It also illustrates ways you can
‘ extend the functionality of the three conditional-format limit
‘ allowed in the FormatConditions collection.
‘
‘
Dim objFrc As FormatCondition
Dim lngOrange As Long
Dim lngBlue As Long
Dim lngGreen As Long
Dim lngYellow As Long
Dim lngPink As Long
Dim lngPurple As Long

‘ Set up background and foreground colors.
lngOrange = RGB(255, 127, 0)
lngBlue = RGB(72, 209, 204)
lngGreen = RGB(0, 205, 0)
lngYellow = RGB(255, 215, 0)
lngPink = RGB(255, 110, 180)
lngPurple = RGB(139, 58, 98)

‘ Remove any existing format conditions.
Me![RUNRESERVED].FormatConditions.Delete

‘ Create three format objects and add them to the FormatConditions
‘ collection.
Set objFrc = Me![RUNRESERVED].FormatConditions.Add(acExpression, _
Left(RUNRESERVED, 1) = "g")
Set objFrc = Me![RUNRESERVED].FormatConditions.Add(acExpression, _
Left(RUNRESERVED, 1) = "y")
Set objFrc = Me![RUNRESERVED].FormatConditions.Add(acExpression, _
Left(RUNRESERVED, 1) = "b")
Set objFrc = Me![RUNRESERVED].FormatConditions.Add(acExpression, _
Left(RUNRESERVED, 1) = "o")
Set objFrc = Me![RUNRESERVED].FormatConditions.Add(acExpression, _
Left(RUNRESERVED, 1) = "as")
Set objFrc = Me![RUNRESERVED].FormatConditions.Add(acExpression, _
Left(RUNRESERVED, 1) = "ac")
‘ Depending on the user’s option selection, format the txtResult
‘ box.
Select Case optgrpChoice.Value
Case 1
‘ Refer to each format condition by its index.

Case 2

With Me![RUNRESERVED].FormatConditions(0)
.BackColor = lngGreen
.FontBold = True
.ForeColor = lngBlack
End With

With Me![RUNRESERVED].FormatConditions(1)
.BackColor = lngYellow
.FontBold = True
.ForeColor = lngBlack
End With

With Me![RUNRESERVED].FormatConditions(2)
.BackColor = lngBlue
.FontBold = True
.ForeColor = lngBlack
End With
With Me![RUNRESERVED].FormatConditions(3)
.BackColor = lngOrange
.FontBold = True
.ForeColor = lngBlack
End With
With Me![RUNRESERVED].FormatConditions(4)
.BackColor = lngPink
.FontBold = True
.ForeColor = lngBlack
End With
With Me![RUNRESERVED].FormatConditions(5)
.BackColor = lngPurple
.FontBold = True
.ForeColor = lngBlack
End With
End Select
End Sub

[CJ_London]

the real test is 'does it work?' I haven't used 2003 or earlier for 10 years or so and have no means of testing your code. All I can do is point you in the right direction.

[CJ_London]

with regards your private message - I do not keep a list of the thousands of error codes, so a number on it's own is insufficient. what is the error description?

[barby.pava]

runtime Error 7966:
"Format condition number you specified is greater then the number of format conditions"

[barby.pava]

and i just saw that I am using access 2007. I dont know if this change.

from reading online the error 7966 highlight my 4th condition and it looks like that i have more then 3 conditions so the vba code can not be run for this reason

[CJ_London]

and on which line is the error occurring?

It may be that 2002 has a limit of 3 conditions, in which case it is time to upgrade to something more current

Just revisited the links and it does say

However, each control is limited to a maximum of three conditional formats at any one time


and

The three-condition limit can be overcome by using VBA code to program these conditions to have different values under different circumstances

Which I suspect won't meet your needs.

Later versions of access allow a greater number of conditional formats

this link

https://blogs.office.com/2011/04/06/...a-bit-of-code/

states 2010 can have up to 50 conditional formats and provides a link to some 'tricks' you might be able to use for your situation

Looks like it might be time to upgrade to a more current version of Access. 2002 is no longer supported and leave it much longer and you won't be able to upgrade easily - see this link

http://www.consultdmw.com/converting...s-database.htm