label color change on each record

[jw5107]

Ok-
I have a form with a bunch of labels on it... The form has images that show a map of the world. The labels are for each airport... The label caption is the aiport code - SDF, ANC, HKG, CGN, etc...
Below is code that I am working with that works fine for just one record... It changes the labels coloring/properties. The color represent different stock status's for an aircraft part at an airport. I want the user to be able to look at several parts at one time. Meaning, using the form, the user can use the navigation buttons to navigate thru the records and the label changes color for each record. I'm pretty sure this can be done, I just think there needs to be a "reset" built in, and this needs to occur "between" each record when the user selects the next record. Otherwise the colors will get mixed up thru-out the form, and the user won't know the true stock status of the part/current record...
Any suggestions examples...??
Thanks in advance..!!
jw5107

Dim rstAlloc As New ADODB.Recordset
Dim ctrl As Control
Dim stLinkCriteria As String
Dim i As Integer
'Dim db As DAO.Database
'Dim rstAlloc As DAO.Recordset
'Dim qdf As DAO.QueryDef
'Dim prm As Parameter

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

rstAlloc.Open "AllocationsStatusHistory", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic

stLinkCriteria = "[WWRID]= " & [WWRID] & ""

If DCount("WWRID", "[AllocationsStatusHistory]", stLinkCriteria) Then
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
'For i = 1 To .RecordCount
Select Case ctrl.Tag
Case Is = !Allocated
ctrl.BackStyle = 1
ctrl.ForeColor = 16711680
ctrl.FontBold = True
Case Is = !OverAllocated
ctrl.BackStyle = 1
ctrl.BackColor = 12058623
ctrl.FontBold = True
Case Is = !OnBackOrder
ctrl.BackStyle = 1
ctrl.BackColor = 8434687
ctrl.FontBold = True
Case Is = !NewAlloc
ctrl.BackStyle = 1
ctrl.BackColor = 16645064
ctrl.FontBold = True
Case Is = !DeAlloc
ctrl.BorderStyle = 1
ctrl.BorderColor = 255
ctrl.FontBold = True
ctrl.BorderWidth = 2
Case Is = !PooledGTWYs
ctrl.BackStyle = 1
ctrl.BackColor = 12582847
ctrl.FontBold = True
Case Is = !Model2kGTWYs
ctrl.BorderStyle = 1
ctrl.BorderColor = 3632896
ctrl.BorderWidth = 2
ctrl.FontBold = True
End Select
.MoveNext
'Next i
End With
End If
Next
End If

 

[easyit]

...have you considered using conditional formatting?

EasyIT

"Do you think that’s air you're breathing?

 

[jw5107]

easyit,

Does Conditional Formatting even work with labels...?
If it does - do you have any or know of any examples I can work with...?
Thanks,
jw5107

 

[easyit]

Hm,

i'm not sure. Also I forgot that CF only applies to three values per column. Give it ba try anyway.


EasyIT

"Do you think that’s air you're breathing?

 

[assets]

yes. Use If value is "A" background colour etc( well you get the idea). There is a FAQ that was used for 2 option eg red for no and green for yes and blank for none. I think it under form or other topics

Never give up never give in.

There are no short cuts to anything worth doing

 

[TheAceMan1]

How are ya jw5107 . . .

I don't remember, but is this a [blue]Single[/blue] or [blue]Continuous[/blue] view form?

See Ya! . . . . . .

 

[jw5107]

The AceMan1,

The form is a single form....
Below is what I am working with right now - with the OnCurrent Event of the form...
The form is bound to a table, and a query is used for the "label color change"....
This appears to be working... still testing... Just runs slow and not so sure this is correct...
Thanks for the help..!!
jw5107


Private Sub Form_Current()
Dim ctrl As Control
Dim I As Integer
Dim db As DAO.Database
Dim rstAlloc As DAO.Recordset
Dim rstAllocHist As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As Parameter

For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
Select Case ctrl.Tag
Case Is = "Clearme"
ctrl.ForeColor = vbBlack
ctrl.BackStyle = 0
ctrl.BorderStyle = 0
ctrl.BackColor = 16777215
ctrl.FontBold = False
End Select
End If
Next ctrl
Me!lblNoStock.Visible = False


For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
Select Case ctrl.Tag
Case Is = "Clearme"
ctrl.Value = ""
End Select
End If
Next ctrl


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

If rstAlloc.EOF = True Then
Me!lblNoStock.ForeColor = 255
Me!lblNoStock.Visible = True
Exit Sub
Else
For Each ctrl In Me.Controls
If TypeOf ctrl Is Label Then
With rstAlloc
.MoveFirst
For I = 1 To .RecordCount
Select Case ctrl.Caption
Case Is = !Allocated
ctrl.BackStyle = 1
ctrl.ForeColor = 16711680
ctrl.FontBold = True
Case Is = !OverAllocated
ctrl.BackStyle = 1
ctrl.BackColor = 12058623
ctrl.FontBold = True
Case Is = !OnBackOrder
ctrl.BackStyle = 1
ctrl.BackColor = 8434687
ctrl.FontBold = True
End Select
.MoveNext
Next I
End With
End If
Next
End If

For Each ctrl In Me.Controls
If TypeOf ctrl Is TextBox Then
With rstAlloc
.MoveFirst
For I = 1 To .RecordCount
Select Case ctrl.Name
Case Is = !Allocated
ctrl.Value = !Allocation
ctrl.BackStyle = 1
Case Is = !OverAllocated
ctrl.Value = !Allocation
ctrl.BackStyle = 1
Case Is = !OnBackOrder
ctrl.Value = !Allocation
ctrl.BackStyle = 1
End Select
.MoveNext
Next I
End With
End If
Next

End Sub