Run Time Error '438' - BackColor

[nslemmons]

Hey all.



I'm having a wierd issue with this error. I am referencing a variable for all controls on a form and when this code runs I'm getting the 438 error (Object doesn't support this property or method).

I'm trying to highlight controls that are null and then I want them to turn white after they've been highlighted. Any help is appreciated.

Here's the code I have.

Private Sub cmdTest_Click()
Dim billTo
Dim ctl As Control
billTo = Me.cmbBillTo.Value
For Each ctl In Me.Controls
    If IsNull(ctl) Then
            ctl.BackColor = &HEC2FF
    End If
Next ctl
        MsgBox "Enter all fields.", vbExclamation
        
            For Each ctl In Me.Controls
                ctl.BackColor = vbWhite
            Next ctl
Exit Sub
    If DCount("*", "qryRecordCheck") = 0 Then
        DoCmd.OpenQuery "qryTempPricing"
            MsgBox billTo & " has been successfully added!"
        Else
            If MsgBox("This record already exists. Would you like to overwrite it?", 20, "Record Exists!") = vbYes Then
                DoCmd.OpenQuery "qryTempPricing"
                Else
                    MsgBox "No change made to Pricing Table", 64, "Info"
            End If
    End If
End Sub

Thanks,
Nathan

[pbaldy]

Not weird at all when you realize that not every type of control has a backcolor property. You might want to either use the Tag property to limit the controls tested to those you want or use ctl.ControlType to limit the controls tested to the type of control you want, like text boxes.

[nslemmons]

Thanks Paul.

What I noticed is further up in the code I use that same method with success. ctl.BackColor = &HEC2FF

Why does it not work in the second case?

Also I have both combo boxes and text boxes. How could I accomplish looping through them to set the BackColor?

Thanks again.
Nathan

[pbaldy]

Presumably the controls that would error are excluded by the Null test. You can add this inside your loop:

    Select Case ctl.ControlType
      Case acTextBox, acComboBox
        ctl.BackColor = vbWhite
    End Select

[nslemmons]

Paul,

Thank you sir. That worked great. I'll have to study this more so I have a better understanding of what is happening as it executed this code.

I really appreciate it!

Thank you,
Nathan

[pbaldy]

No problem Nathan, and welcome to the site by the way! Post back if you don't understand anything.

[nslemmons]

Paul,

I do have another issue I discovered with this. My form also has buttons which I assume are controls. So when I run the code I end up with the message box saying "Enter all fields.". I presume this is because it's looping through the buttons as well as the text and combo boxes.

I'm not sure how to work around that.

Private Sub cmdTest_Click()
Dim billTo
Dim ctl As Control
billTo = Me.cmbBillTo.Value
For Each ctl In Me.Controls
    If IsNull(ctl) Then
            ctl.BackColor = &HEC2FF
    End If
Next ctl
        MsgBox "Enter all fields.", vbExclamation
        
            For Each ctl In Me.Controls
                Select Case ctl.ControlType
                Case acTextBox, acComboBox
                ctl.BackColor = vbWhite
                End Select
            Next ctl
Exit Sub
    If DCount("*", "qryRecordCheck") = 0 Then
        DoCmd.OpenQuery "qryTempPricing"
            MsgBox billTo & " has been successfully added!"
        Else
            If MsgBox("This record already exists. Would you like to overwrite it?", 20, "Record Exists!") = vbYes Then
                DoCmd.OpenQuery "qryTempPricing"
                Else
                    MsgBox "No change made to Pricing Table", 64, "Info"
            End If
    End If
End Sub

Thank you,
Nathan

[pbaldy]

Well, based on its placement your message box is going to run no matter what. Can you clarify what you're trying to accomplish? As written, it will change the backcolor of any null control, but then it turns right around and changes all of them to white. What's the purpose of the first bit?

[nslemmons]

Well Paul you are correct. That message will run with it being where it is. I didn't notice that.

so what I'm trying to accomplish is highlighting fields that are not filled out. The first bit is just saying "Hey these are empty!" Then they change back because it's annoying to look at those fields all colored in. And the text is hard to read.

Does that help?

Thank you,
Nathan

[pbaldy]

Well, if the first part is working correctly, add a variable in there that you set if/when it finds a Null control. Then test that variable and only fire off your message box if a null was found.

[nslemmons]

Paul,

Sorry it's been a few days.

I thought that's what I was doign with the If statement inside the For Each statement. I take it I'm not interpreting that correctly?

[pbaldy]

I meant:

Dim booNullFound As Boolean

For Each ctl In Me.Controls   
  If IsNull(ctl) Then     
    ctl.BackColor = &HEC2FF
    booNullFound = True   
  End If 
Next ctl

If (booNullFound) Then
   MsgBox "Enter all fields.", vbExclamation
End If

[nslemmons]

Paul,

Thank you sir. I do believe that will work.

Thanks again,
Nathan

[pbaldy]

Happy to help Nathan.