Textbox Color Question

[darude]

Good Day Folks,
I have a data entry form. Required text boxes are highlighted in light green. After the user enters data into the text box I want to change the back color to white. I don't want to write the same code on After_Update for every text box, there's a lot. Can someone point me in the direction of reusing code to do this.

Thank you.

 

[MajP]

1. Use no code and conditional formatting on each field
"where" isNull([fieldName])

2.Another way is to put a tag value in each required field's tag property. I use a "?" (do not include the quotation marks).

Private Sub Form_Current()
 Call changeColors
End Sub

Public Sub changeColors()
 Dim ctl As Access.Control
  For Each ctl In Me.Controls
    If ctl.Tag = "?" Then
      If Trim(ctl.Value & " ") = "" Then
        ctl.BackColor = vbGreen
      Else
        ctl.BackColor = vbWhite
      End If
    End If
  Next ctl
End Sub

for each textboxes after update put something like

Private Sub LastName_AfterUpdate()
 Call changeColors
End Sub

Private Sub FirstName_AfterUpdate()
  Call changeColors
End Sub

 

[darude]

Thank you. I keep getting object doesnt support this method on the ctl.Backcolor - vbGreen statement.

 

[Domino2]

Have you tried Dim ctl As Control

Should work. Regards

 

[darude]

I copied your code exactly. I only want to change the text box the user is working on not all the text boxes. I think this code changes all.

 

[MajP]

not all controls have a backcolor property. Textboxes, listboxes and labels do. By chance did you put a tag in other controls such as a checkbox?

You could put this before to see which control is causing a problem.
...
debug.print ctl.name
ctl.BackColor = vbGreen

Then you could look at the immediate window to see what control is the problem

I have no idea what Domino2 is suggesting.

 

[darude]

I am using textboxes. After the field is updated, I simply want to change the backcolor to white. Now, I could put an after_update statement for each control, but don't want all that code. To re-use a coded statement, my guess is that it needs to know which control to change after a single update. I don't know how to code for that.

Any help would be greatly appreciated.

 

[MajP]

How many controls are you talking about? If it is 20 or so then I would think it would be easy to put an after update event for each control with a single line of code

ex
Private Sub ctlOne_AfterUpdate()
Call changeColors
End Sub

Private Sub ctlTwo_AfterUpdate()
Call changeColors
End Sub
...
Private Sub ctlThree_AfterUpdate()
Call changeColors
End Sub

I do not need to know which control because each time it checks the required fields and turns them white if no text present or possibly back to green if you deleted text out of the required field.

If you have a lot of controls (like 100) even writing a single line event procedures may be a pain. So what you would want is a single event procedure. This can be done and I have described how to do this in Faq702-6904.

 

[PHV]

Have a look at the Screen.ActiveControl property.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MajP]

It should read
"turns the background white if text is present (if it is already white it stays white)"

"I think this code changes all. "
No it only changes all that have text in them.

 

[MajP]

Yes you could do it this way and specify exactly which control to change. If you want longer code requiring more checks. However, I do not see the reason. What I am suggesting is more bullet proof because whenever it fires it checks to see if any required field is empty and it will turn it green or keep it green, it will check any required field that is not empty and turn it white or keep it white. Your thoughts?

 

[darude]

Okay, so it's not giving me any errors. However, my form is unbound so when I select from a combo box it doesn't hold the selection. Other than that it is working. Thank you again.

 

[MajP]

Not sure if I understand what you would want it to do. Maybe if you come back to a record it would remain white even though there is no value in it. Maybe take the ? mark out of the unbound combo and change the code to:

Private Sub cmboOne_AfterUpdate()
  If Not Trim(Me.cmboOne & " ") = "" Then
    Me.cmboOne.BackColor = vbWhite
  End If
End Sub

Private Sub Form_Current()
 Call changeColors
 
 If Me.NewRecord Then
   Me.cmboOne.BackColor = vbGreen
 Else
   Me.cmboOne.BackColor = vbWhite
 End If
End Sub

I will only be green on a new record until you make a selection. Old records will always be white.

 

[darude]

Hello Again,
So I changed my code to look at all the controls and determine if a field is green and has no data, send a msg.
The code below also gives me the name of the control. I need to code it to get the names if there are multiple controls. Any help would be greatly appreciated.

Dim ctl As Control

For Each ctl In Me.Controls
If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox) Then
If IsBlank(ctl.Value) = True And ctl.BackColor = 13434828 Then
strFilled = "N"
MsgBox (ctl.Name)
End If
End If
Next ctl

 

[MajP]

Public Sub makeMsg()
 Dim strMsg As String
 Dim ctl As Access.Control
 For Each ctl In Me.Controls
 If (ctl.ControlType = acTextBox Or ctl.ControlType = acComboBox Or ctl.ControlType = acListBox) Then
   If Trim(ctl & " ") = "" Then
    If strMsg = "" Then
      strMsg = ctl.Name
    Else
      strMsg = strMsg & "," & ctl.Name
   End If
  End If
  End If
Next ctl

If Not strMsg = "" Then
  MsgBox "The following fields are blank: " & strMsg
End If
End Sub

 

[darude]

Perfect. Thanks so much for your help! Much appreciated.