looping through text boxes

[Oliver1]

Hi,

I'm hoping somebody may be able to help.
I have written the bit of code below, but I have about 40 text boxes that need to be populated, is there anyway I can loop through them. Rather than copying the below code 40 times and changing the text box name to Me.txtname2 , me.txtname3 ect. Hope somebody can help. Thanks in advance

Set rs = db.OpenRecordset("olcalldata")
lngRed = RGB(255, 0, 0)
lngyellow = RGB(255, 255, 0)
Me.txtname1 = rs!pa
If rs!Status = "Busy" Then
Me.txtname1.BackColor = lngRed
Else
Me.txtname1.BackColor = lngyellow
End If

 

[Mile-O]

Why do you have 40 textboxes?

Anyway,

Dim ctl As Control
For Each ctl In Me
    If ctl.ControlType = acTextBox Then
        ctl.BackColor = vbRed
    End If
Next
Set ctl = Nothing

 

[Oliver1]

Clarification

Sorry I wasn't being very clear.
I have written a query which will return about 40 records. I wish to view these records on a form and for certain records where one of the fields has a certain value I wish it to highlight itself in red.
So everytime I rerun this query the highlighted text boxes will change. There might be a much easier way of doing this.
I originally intented to use my above code 40 times with a rs.movenext to go to each one of my 40 records, but I was thinking that there must be a way to do some sort of loop.

 

[Mile-O]

I was thinking that there must be a way to do some sort of loop.

And I've already given you an example of a loop.

And you can use the colour constants vbRed and vbYellow rather than waste memory defining Long Integers.

 

[Oliver1]

hopefully this makes more sense

Sorry again and thanks for all your help so far.
I understand looping but my big problem is refering to the text boxes on my form.
Say I have text boxes
Me.txtname1
Me.txtname2
Me.txtname3
Me.txtname4
ect up to 40.
How do I loop through them
eg

dim int as interger
Do While int < 40
int = int +1
me.txtname & int = rs!name
loop

Obviously my me.txtname & int doesn't work. Hope this makes sense.

 

[Kevin_S]

Oliver -

Did you try the suggestion made by Mile/SJ McAbney? I wonder because he has given you the correct way to do this. You do not need the individual names of the textboxes as you are using the control object to go through all of the textbox on the form. Basically the code says that for each control on the form if it is a textbox then do something to it else skip it... is this not want you wanted?

HTH,
Kev

 

[Mile-O]

You could have used the method above - it works.

Also,

Dim intCounter As Integer
For intCounter = 1 To 40
    Me("MyTextBox" & intCounter).BackColor = vbRed
Next intCounter

 

[Oliver1]

Thanks loads, have been scratching my head all afternoon

The second piece of code works brilliantly, the problem with the first piece of code was that it set all my text boxes to red, when I wanted some to be yellow depending on there values.
Have just put an if statement in your second piece of code and it works, thanks you very much.

Perhaps I just didn't quite understand your first piece of code, sorry for wasting your time if that's the case but thanks again for you second piece.

 

[Mile-O]

Yeah, you could have put the IF..ELSE..END IF test in the first piece of code too.

 

[Kevin_S]

Dim ctl As Control
For Each ctl In Me
    If ctl.ControlType = acTextBox Then
        If rs!Status = "Busy" Then
             ctl.BackColor = vbRed
        Else
             ctl.BackColor = vbYellow
        End If
   End If
Next
Set ctl = Nothing

 

[Mile-O]

Dim ctl As Control
For Each ctl In Me
    If ctl.ControlType = acTextBox Then
        If rs!Status = "Busy" Then
             ctl.BackColor = vbRed
        Else
             ctl.BackColor = vbYellow
        End If
   End If
Next
Set ctl = Nothing

Dim ctl As Control
For Each ctl In Me
    If ctl.ControlType = acTextBox Then
        If rs("Status") = "Busy" Then
             ctl.BackColor = vbRed
        Else
             ctl.BackColor = vbYellow
        End If
   End If
Next
Set ctl = Nothing