Looping through form controls

[SpikeyMikey via AccessMonster.com]

I want to loop through text boxes on a continuous form and change the
background color if the date in the text box equals today's date. I have
already seen by searching here that I can do it through conditional
formatting and that works fine but I want to know how to do it using VBA.
I've spent hours trying to get the code to work but it sets all the text
boxes to the background color of the first text box so my looping statement
is incorrect. Below is the VBA I've been using:

Test1 is the name of the form and txtDate the name of the text box

Private Sub Form_Load()

Dim myCntrl As Control
Set myCntrl = Forms![Test1].[txtDate]
'
For Each myCntrl In Me.Controls
If myCntrl.ControlType = acTextBox Then

If Me!txtDate.Value = Date Then
Me!txtDate.BackColor = 8388863
Else: Me!txtDate.BackColor = 33023
End If
End If
Next myCntrl
End Sub

I'm new to VBA and have trouble understanding how to code for loops so would
really appreciate some help as it would help me to understand more about
these structures.

thanks

 

[Marshall Barton]

I want to loop through text boxes on a continuous form and change the
background color if the date in the text box equals today's date. I have
already seen by searching here that I can do it through conditional
formatting and that works fine but I want to know how to do it using VBA.
I've spent hours trying to get the code to work but it sets all the text
boxes to the background color of the first text box so my looping statement
is incorrect. Below is the VBA I've been using:

Test1 is the name of the form and txtDate the name of the text box

Private Sub Form_Load()

Dim myCntrl As Control
Set myCntrl = Forms![Test1].[txtDate]
'
For Each myCntrl In Me.Controls
If myCntrl.ControlType = acTextBox Then

If Me!txtDate.Value = Date Then
Me!txtDate.BackColor = 8388863
Else: Me!txtDate.BackColor = 33023
End If
End If
Next myCntrl
End Sub

I'm new to VBA and have trouble understanding how to code for loops so would
really appreciate some help as it would help me to understand more about
these structures.

You can not manipulate the properties on controls in
individual rows on a continuous or datasheet form. This is
true because there is only one control with one set of
properties and when you set a prperty it is applied to all
the copies of the control (ie all rows).

Conditional Formatting was invented just to allow you to get
the desired effect without having to go to the extremes
required in early versions of Access.

 

[SpikeyMikey via AccessMonster.com]

Thanks very much for your explanation.

SpikeyMikey

I want to loop through text boxes on a continuous form and change the
background color if the date in the text box equals today's date. I have

[quoted text clipped - 25 lines]

really appreciate some help as it would help me to understand more about
these structures.

You can not manipulate the properties on controls in
individual rows on a continuous or datasheet form. This is
true because there is only one control with one set of
properties and when you set a prperty it is applied to all
the copies of the control (ie all rows).

Conditional Formatting was invented just to allow you to get
the desired effect without having to go to the extremes
required in early versions of Access.