Label to change color

[jw5107]

Below is sample code that I have been working with..

What I am trying to do is make all this run faster. Right now I have to run several queries to get the data the way I want it. Then the code looks for a match in the GTWY field (from query qryAllocationStatusLookup) and if the label caption is equal - the label color will change. Works great - just was curious to see if there was a way to do this all in one shot.

Something that looks at the main table, equaling the IID on a form, then the label will change color based on certain criteria in the code.

I guess like -
If MAX >0 and GTWY = ctrl.caption then
ctrl.forecolor = blue
elseIf OH <MIN and GTWY = ctrl.caption then
ctrl.forecolor = green

Or something like that!!!!
Any suggestions???

Thanks in advance!!
jw

Dim ctrl As Control
Dim i As Integer
Dim db As DAO.Database
Dim rstAlloc As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter

Set db = CurrentDb
Set qdf = db.QueryDefs("qryAllocationStatusLookup")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rstAlloc = qdf.OpenRecordset(dbOpenDynaset)

If rstAlloc.EOF Then
Exit Sub
End If

For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
If ctrl.Caption = !Allocated Then
ctrl.ForeColor = 16711680
End If
.MoveNext
Next i
End With
End If
Next

 

[PHV]

You may try this to see if faster:
With rstAlloc
.MoveFirst
For i = 1 To .RecordCount
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
If ctrl.Caption = !Allocated Then
ctrl.ForeColor = 16711680
Exit For ' if only one label should match
End If
End If
Next
.MoveNext
Next i
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886