?Code to cycle through forms controls

[Lisa B.]

Is there a code to cycle through all the controls on a form? I know there
is, I hope.

Does anyone have the code to cycle through all the controls on a form?

I would like to cycle through all the controls on the from to check for null
values before user is allowed to move on to another record or exit the form.

I know I can do this with a lot of IF statements( an IF statement for each
Controls name), however I would like a generic code were you don't need to
know the name of the control.

Your quick response will be greatly appreciated.

Thank You
LisaB

 

[Allen Browne]

Lisa, the simplest way to do this is to open your table in Design view, and
set the Required property (lower pane) to Yes for each field.

It sounds unusual to require all fields though. If this is just because you
are having difficulties handling Nulls, this may help:
http://allenbrowne.com/casu-11.html

If you want to check for Nulls before the record is saved, you must use the
BeforeUpdate event procedure of the form.

You can loop through all controls, but some controls (such as lines and
labels) don't have a value and so cannot be tested for Null. The example
below checks if the control has a Control Source proeprty, and if so, that
it is not an unbound control (no control source used) or a calculated
control (control source starts with equals).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control
Dim strMsg As String

For Each ctl In Me.Controls
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And _
Left(ctl.ControlSource, 1) <> "=" Then
If IsNull(ctl.Name) Then
Cancel = True
strMsg = strMsg & ctl.Name & " is Null." & vbCrLf
End If
End If
End If
Next
End Sub
Public Function HasProperty(obj As Object, strPropName As String) As Boolean
'Purpose: Return true if the object has the property.
Dim varDummy As Variant

On Error Resume Next
varDummy = obj.Properties(strPropName)
HasProperty = (Err.Number = 0)
End Function

 

[Cheryl Fischer]

The following might work for you:

Dim ctl As Control

For Each ctl In Me.Controls
If IsNull(ctl) Then
MsgBox "Control " & ctl.Name & " has no value."
End If
Next ctl

hth,

 

[Lisa B.]

Mr. Browne,

Thank you very much for your quick response.

This was a request given to me by someone else. They would like to make
sure every field on the form is filled in and for the fields that are not
they want the backcolor to be changed to red (so it will stand out)

I tried the following but it doesn't work
How do I make this work
--------------------------------------
Dim ctl As Control

For Each ctl In Me.Controls
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And _
Left(ctl.ControlSource, 1) <> "=" Then
If IsNull(ctl.Name) Then
ctl.Name.BackColor = vbRed
'strMsg = strMsg & ctl.Name & " is Null." & vbCrLf
End If
End If
End If
Next

 

[Allen Browne]

Try:
ctl.BackColor = vbRed
and don't forget to paste the HasProperty function into your module as well.

 

[Dirk Goldgar]

Mr. Browne,

Thank you very much for your quick response.

This was a request given to me by someone else. They would like to make
sure every field on the form is filled in and for the fields that are not
they want the backcolor to be changed to red (so it will stand out)

I tried the following but it doesn't work
How do I make this work
--------------------------------------
Dim ctl As Control

For Each ctl In Me.Controls
If HasProperty(ctl, "ControlSource") Then
If Len(ctl.ControlSource) > 0 And _
Left(ctl.ControlSource, 1) <> "=" Then
If IsNull(ctl.Name) Then
ctl.Name.BackColor = vbRed
'strMsg = strMsg & ctl.Name & " is Null." & vbCrLf
End If
End If
End If
Next

I think there's an error in the above. These lines:

If IsNull(ctl.Name) Then
ctl.Name.BackColor = vbRed

should be:

If IsNull(ctl.Value) Then
ctl.BackColor = vbRed

You're also going to need to reset the BackColor to whatever the
non-highlighted color is supposed to be, if the control *isn't* Null. You
could do that with an Else clause following the above; e.g.,

Else
ctl.BackColor = vbWhite

 

[Dirk Goldgar]

THANK YOU! THANK YOU! THANK YOU!

I hope that means it works now, because the code you posted in the remainder
of your message also has serious problems. I'd go with a corrected version
of Allen Browne's code, rather than trying to patch this up.