Changing field background color with VBA code (continous form)

[pawjer]

Hi,

I have a form with a field where there might be more than 2 CRs in it, and I need to change formatting for records that contain more than 2 of CRs. Having searched for solution, I came across useful code that counts CRs in a field. Then I added a single line code that changes background color if condition is valid:



Private Sub Lokalizacja_AfterUpdate()

Dim arrLines() As String
Dim intLineCount As Integer
arrLines = Split(Lokalizacja.Text, vbCrLf)
intLineCount = UBound(arrLines) + 1

If intLineCount > 1 Then Me.Lokalizacja.BackColor = vbYellow

End Sub

It works actually but when there is more that 2 CRs in a given field, then all records on my form turn yellow. I need to only have records with 3 and more CRs marked yellow.

Is there solution for that? I have found some discussions saying that it is the nature of Access and one can do hardly anything to get over it. However, built-in conditional formatting when designing a form allows us to turn a single field into a color, so why should't it be possible in VBA?

I would be grateful for some help on the above matter.

Kind regards to All

[isladogs]

If you look at your continuous form in design mode, all your records are actually multiple copies of the same controls.
So the code will affect all records.

Conditional formatting will allow some variation but could be difficult to implement in this case
Try adding your intlinecount as a hidden control, then apply conditional formatting to background colours based on the intlinecount value

[Micron]

Had an idea that I just had to check out. Turns out you can call a function in a cf expression. I was able to pass the control to the function and detect a CRLF (a cr alone isn't really the same AFAIK). Anyway this worked:

Function IsMultiLine(ctl As Control) As Boolean
Dim strTest As String
strTest = ctl
IsMultiLine = Len(ctl) - Len(Replace(ctl, vbCrLf, ""))

End Function

The cf expression is IsMultiLine(Forms!tblLicenses.caseNo)
I found that the function won't get called if you use Me. or only the control name. Only one record had a new line; only one record was highlighted
Forgot to say that the assignment to the function works because, as we've been discussing elsewhere, anything other than 0 is true.

[pawjer]

I solved it by converting "afterupdate" vba code to public function that sends back number of CRs. Then I applied it to regular conditional formatting option as a condition. Works fine.