The aim is to ensure that an application form has all the necessary fields filled in. So what I am attempting is to create a basic loop through the form controls where they find a Combo Box, which is then interogated. If no value is found the field is highlighted and the Sub is exited.

So what I have is

For i = 0 To Form.Controls.Count - 1

If Form.Controls(i).type=acComboBox Then
  If IsNull(Form.Controls(i)) Then
  MsgBox "Required Field", vbCritical, "Required"
  Form.Controls(i).SetFocus
  Form.Controls(i).BackColor = 8454143
  Exit Sub
      Else
  End If
End If

It doesn't like the .type property any other suggestions.

This worked for me when enumerating listboxes.

Dim ctl As Control

For Each ctl In Me.Controls
    With ctl
        If .ControlType = acListBox Then
            MsgBox "Worked"
        End If
    End With
Next ctl

Can you use this?


mmcdonal

Look it up at: http://wrox.books24x7.com

Thanks I didn't actually use your advice with this question. But you have back to me with good advice on other topics, so thanks again.

Below is the code i used

You originally used TYPE whereas MMcDonal used CONTROLTYPE. I also have never been able to get that to work so I have had to use tags and compare the TAG properties. So both your solutions will be something I'll try in the future, i.e. ControlType and TypeOf.

Remember to write code to change the backcolor BACK to white when the data is actually entered!

Note, if you use conditional formatting to make any field with the focus change backcolor, there would be no need to actually assign the backcolor at all. It'll just automatically change to yellow and back to white when a field gets/loses focus.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division