Changing a fields background color on a subform

[RevJeff]

Hi,

I have done this in the past, but for the life of me, I can't get the code right.

Main form: frmBC2ndParent
Subform: frmBC2ndChild

    If Me.Form.frmBC2ndChild!Combo173 = "Company Will Pay" Then
        Me.Form.frmBC2ndChild!SUPC1.BackColor = vbYellow
        Me.Form.frmBC2ndChild!Pack1.BackColor = vbYellow
        Me.Form.frmBC2ndChild!Brand1.BackColor = vbYellow
        Me.Form.frmBC2ndChild!Manufac1.BackColor = vbYellow
        Me.Form.frmBC2ndChild!Broker1.BackColor = vbYellow
        Me.Form.frmBC2ndChild!Desc1.BackColor = vbYellow
    Else
        If Me.Form.frmBC2ndChild!Combo173 = "Vendor Will Bring" Then
            Me.Form.frmBC2ndChild!SUPC1.BackColor = RGB(77, 77, 77)
            Me.Form.frmBC2ndChild!Pack1.BackColor = RGB(77, 77, 77)
            Me.Form.frmBC2ndChild!Brand1.BackColor = RGB(77, 77, 77)
            Me.Form.frmBC2ndChild!Manufac1.BackColor = RGB(77, 77, 77)
            Me.Form.frmBC2ndChild!Broker1.BackColor = RGB(77, 77, 77)
            Me.Form.frmBC2ndChild!Desc1.BackColor = RGB(77, 77, 77)
        Else
            Me.Form.frmBC2ndChild!SUPC1.BackColor = vbWhite
            Me.Form.frmBC2ndChild!Pack1.BackColor = vbWhite
            Me.Form.frmBC2ndChild!Brand1.BackColor = vbWhite
            Me.Form.frmBC2ndChild!Manufac1.BackColor = vbWhite
            Me.Form.frmBC2ndChild!Broker1.BackColor = vbWhite
            Me.Form.frmBC2ndChild!Desc1.BackColor = vbWhite
        End if
   End if

When the user clicks a button the the main form, it loads data into the subform through a query. Then it is supposed to check the Combo Box and adjust the background based on the value. Can someone please help me figure out what I am doing wrong?

Thank you!

 

[jdraw]

Can you tell us where this code exists?
You show code with subform controls, but where is this code?

Is this info helpful http://access.mvps.org/access/forms/frm0031.htm

More info please.

 

[RevJeff]

Sorry, the code resides in a button on the main form.

 

[missinglinq]

And where does the Combobox Combo173 reside?

From a Main Form, referring to a Control on a Subform, the correct syntax would be

Me!SubformName.Form!SubformControlName

Linq ;0)>

 

[jdraw]

I just checked one of my databases and found this

Private Sub Form_Current()
If IsNull(Me.tbl_Spl_LCL_Std_EstAllianceEngsubform.Form.Txt) Then
   Me.Box13.BackColor = vbBlack
Else
   Me.Box13.BackColor = vbGreen
End If
If IsNull(Me.tbl_Spl_LCL_Std_EstProfileFre_subform.Form.Txt) Then
  Me.Box15.BackColor = vbBlack
Else
  Me.Box15.BackColor = vbGreen
End If

End Sub

This is part of the main form On Current event code.
It checks if the subform txt field is Null or not

If Null, it sets the backcolor of a small box to Black
If not Null, it sets the backcolor of a small box to Green.

I think you have to move the .Form from where it is, to
after the frmBC2ndChild



OOooops: I see missinglinq has responded while I was looking, and testing.

Good luck with your project.

 

[RevJeff]

The Combobox resides on the Subform. I made the changes to the syntax and now I am getting the error:

Microsoft Access can't find the field 'frmBC2ndChild' referred to in your expression.

 

[missinglinq]

Copy and post your code here.

 

[RevJeff]

Here is the code behind the button. The Button is on the Mainform (frmBC2ndParent). Combo173 is on the Subform (frmBC2ndChild). When the button is clicked, it refreshed the data from the query which feeds the Subform, then the fields listed below are supposed to change background colors based on what is in Combo173. This is the error message that I am getting.

'Run-time error '2465':

Microsoft Access can't find the field 'frmBC2ndChild' referred to in your expression.'

Private Sub Command232_Click()

Me.Refresh

If Me!frmBC2ndChild.Form!Combo173 = "Company Will Pay" Then
Me!frmBC2ndChild.Form!SUPC1.BackColor = vbYellow
Me!frmBC2ndChild.Form!Pack1.BackColor = vbYellow
Me!frmBC2ndChild.Form!Brand1.BackColor = vbYellow
Me!frmBC2ndChild.Form!Manufac1.BackColor = vbYellow
Me!frmBC2ndChild.Form!Broker1.BackColor = vbYellow
Me!frmBC2ndChild.Form!Desc1.BackColor = vbYellow
Else
If Me!frmBC2ndChild.Form!Combo173 = "Vendor Will Bring" Then
Me!frmBC2ndChild.Form!SUPC1.BackColor = RGB(77, 77, 77)
Me!frmBC2ndChild.Form!Pack1.BackColor = RGB(77, 77, 77)
Me!frmBC2ndChild.Form!Brand1.BackColor = RGB(77, 77, 77)
Me!frmBC2ndChild.Form!Manufac1.BackColor = RGB(77, 77, 77)
Me!frmBC2ndChild.Form!Broker1.BackColor = RGB(77, 77, 77)
Me!frmBC2ndChild.Form!Desc1.BackColor = RGB(77, 77, 77)
Else
Me!frmBC2ndChild.Form!SUPC1.BackColor = vbWhite
Me!frmBC2ndChild.Form!Pack1.BackColor = vbWhite
Me!frmBC2ndChild.Form!Brand1.BackColor = vbWhite
Me!frmBC2ndChild.Form!Manufac1.BackColor = vbWhite
Me!frmBC2ndChild.Form!Broker1.BackColor = vbWhite
Me!frmBC2ndChild.Form!Desc1.BackColor = vbWhite
End If
End If

End Sub