CODEWITHSUNDEEP
vbams-access
mightymax
mightymax

Reputation: 431

Highlight field background in continuous form

I have an Access continuous form. I would like to change the forecolor of a specific record's field.

I have the field to highlight from the FieldModified field. So for example FieldModified = "Converted". Converted being a field on my form.

I would like to change the color of the "Converted" field, and do this for each record in the form.

I thought this code would work, but I get an error on Me.[FieldModified].ForeColor. And I need to do this for each record in the form.

Code:

    Private Sub Form_Load()
    Dim fldName As String
    fldName = Me.FieldModified.value
    If (Not IsNull(fldName)) Then
     Me.[fldName].ForeColor = vbRed '<--doesn't recognize fldName value
    End If
End Sub

Updated code but it gives me an error 438 saying object doesn't support this property or method. But the form does highlight fields on the form but it highlights more then the one field "fldName"

Private Sub Form_Load()
Dim rstForm As String
Dim fldName As String
Set rstForm = Me.ChangedData.Form.Recordset

Do While Not rstForm.EOF
fldName = Me.FieldModified.value    
If (Not IsNull(fldName)) Then
    Me.Controls(fldName).ForeColor = vbRed '<--doesn't recognize fldName value
End If

rstForm.MoveNext
Loop
End Sub

Upvotes: 1

Views: 1213

Answers (2)

ComputerVersteher
ComputerVersteher

Reputation: 2696

You set the default format for the control. Every copy of the control in the continuous form uses this format. To format by a condition (fldName = Me.FieldModified.value) you need Condtional Formatting as Andre told you or use the detail-sections paint event (see update on bottom)

In conditional format wizard, you can create a condtion withExpression Isand[Converted].Name = [FieldModified]for each control of the form that should be highlighted, if its name matchesFiledModified. In Ms Access expressions you can't useMe, just omit it .

You can use VBA to format all controls with FormatConditions by code. If you want to modify an existing condition use.Modifyinstead of.Add

Private Sub Form_Load()
  Dim ctl As Access.Control
  For Each ctl In Me.Controls ' loop through all controls of form
      On Error Resume Next ' Not all controls can have conditional format (e.g. labels). To save the check of control type, we ignore errors here
      ctl.FormatConditions.Add(acExpression, , ctl.Name & ".Name=[FieldModified]").BackColor = vbRed 'add o format condition to control if possible, else an error is raised but ignored
      If Err.Number Then 'show errors
          Debug.Print "Error: " & Err.Number & " - " & Err.description & " in Control: " & ctl.Name & " Type is " & TypeName(ctl)
          Err.Clear 'reset error to catch next
      Else
          Debug.Print "FormatCondition added to Control: " & ctl.Name & " Type is " & TypeName(ctl)
      End If
  Next ctl
  On Error GoTo 0 ' turn on errors again, maybe add an error handler (On Error Goto MyErrHandler)
End Sub

Update:
You can use theDetails_Paintevent of the form to format same control different per record. This enables conditional format for controls withoutFormatConditionsproperty like labels, buttons.

Private Sub Detail_Paint()
Dim c As Access.Control

For Each c In Me.Detail.Controls
    If c.Name = Me.FieldModified.Value Then
        c.ForeColor = vbRed
    Else
        c.ForeColor = vbBlack
    End If
Next
End Sub

Upvotes: 3

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

You can't use a String variable like this, fldName is an identifier holding a String value.. not an identifier - in Me.ControlName, both Me and ControlName are identifiers.

But not all hope is lost! You can use a String to pull a Control object from a form!

All form controls should exist in the form's Controls collection, keyed by name:

Me.Controls(fldName).ForeColor = vbRed

Upvotes: 1

Related Questions