For Each ....next loop in access

[Chris Freeman]

I have a table named Design_spec. The field is Status_Icon. There are three
images: green, yellow, and red arrows with visible set to false.

I need to create a loop that looks at Status_icon field, and if the field
value says green, then image15.visible = true, and so on for each status
value.

Can someone help. I can't even find for each record loops in the help on
this version of Access.

Thanks in advance

 

[SteveS]

I have a table named Design_spec. The field is Status_Icon. There are three
images: green, yellow, and red arrows with visible set to false.

I need to create a loop that looks at Status_icon field, and if the field
value says green, then image15.visible = true, and so on for each status
value.

Can someone help. I can't even find for each record loops in the help on
this version of Access.

Thanks in advance

Chris,

You don't need to use a For...Next loop. I would use a Select Case or a
If..Then.

Here are examples of each:

Select Case:
'-------
Private Sub Form_Current()

Select Case Me.Status_Icon
Case "Green"
Me.Image15.Visible = True
Me.Image16.Visible = False
Me.Image17.Visible = False
Case "Yellow"
Me.Image15.Visible = False
Me.Image16.Visible = True
Me.Image17.Visible = False
Case "RED"
Me.Image15.Visible = False
Me.Image16.Visible = False
Me.Image17.Visible = True
Case Else
' no color - no Icon
Me.Image15.Visible = False
Me.Image16.Visible = False
Me.Image17.Visible = False
End Select
End Sub
'-------

'-------
If..Then..Else:
'-------
Private Sub Form_Current()

If Me.Status_Icon = "Green" Then
Me.Image15.Visible = True
Me.Image16.Visible = False
Me.Image17.Visible = False
ElseIf Me.Status_Icon = "Yellow" Then
Me.Image15.Visible = False
Me.Image16.Visible = True
Me.Image17.Visible = False
ElseIf Me.Status_Icon = "Red" Then
Me.Image15.Visible = False
Me.Image16.Visible = False
Me.Image17.Visible = True
Else
' no color - no Icon
Me.Image15.Visible = False
Me.Image16.Visible = False
Me.Image17.Visible = False
End If
End Sub

'-------


Use one or the other, but I think the Select...Case is easier to use in this
case.

HTH

 

[Tim Ferguson]

I need to create a loop that looks at Status_icon field, and if the
field value says green, then image15.visible = true, and so on for
each status value.

You can do this in two lines:


' set up the command
jetSQL = "UPDATE Design_spec " & _
"SET Image15Visible = TRUE " & _
"WHERE Status_Icon = ""Green"""

' now run it: this is DAO but you can do the same thing
' in ADO if you prefer
db.Execute jetSQL, dbFailOnError



Hope that helps


Tim F