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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")

"Lisa B." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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

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,
--
Cheryl Fischer
Law/Sys Associates
Houston, TX

"Lisa B." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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
>
>

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" <(E-Mail Removed)> wrote in message
news:%23QBw%(E-Mail Removed)...
> 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
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to the newsgroup. (Email address has spurious "_SpamTrap")
>
> "Lisa B." <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 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
>
>

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to the newsgroup. (Email address has spurious "_SpamTrap")
"Lisa B." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:%23QBw%(E-Mail Removed)...
> > 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
> >
> > --
> > Allen Browne - Microsoft MVP. Perth, Western Australia.
> > Tips for Access users - http://allenbrowne.com/tips.html
> > Reply to the newsgroup. (Email address has spurious "_SpamTrap")
> >
> > "Lisa B." <(E-Mail Removed)> wrote in message
> > news:(E-Mail Removed)...
> > > 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
> >
> >
>
>

"Lisa B." <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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, MS Access MVP
www.datagnostics.com

(remove NOSPAM from address if replying by email)

"Lisa B." <(E-Mail Removed)> wrote in message
news:u2EQg#(E-Mail Removed)...
> 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.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(remove NOSPAM from address if replying by email)


>
> I was trying another approach, but there is obviously a flaw here too
> -------------------------------------------------------------------------
> Dim frm As Form
> Dim incnt As Integer
> Dim CtlName As String
>
> Set frm = Me
> intCnt = frm.Count
>
> For i = 0 To intCnt - 1
> CtlName = frm(i).Name
> Select Case frm(i).ControlType
> Case acCheckBox '"Check box"
> If IsNull(CtlName) Then
> frm(i).Name.BackColor = vbRed
> End If
> Case acTextBox '"Text Box"
> If IsNull(CtlName) Then
> frm(i).Name.BackColor = vbRed
> End If
> Case acListBox '"List box"
> If IsNull(CtlName) Then
> frm(i).Name.BackColor = vbRed
> End If
> Case acComboBox '"Combo box"
> If IsNull(CtlName) Then
> frm(i).Name.BackColor = vbRed
> End If
> End Select
> Next i