First you need a way to determine a 'control' variable that will apply the
appropriate color.
For example: my clients database is based on whether a field contains
Pickup, Delivery, or other data in the TicketStatus field and will be
returned as a certain color. Setting the color is a multiple step process.
Hopefully I will give you all the pieces to do this. There may be a better
way, however this code was already in place and one I understand to some
degree.
ctlBack 'Name of control
=IIf([SelTop]=[ctlCurrentLine],"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ
ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ",Null) Note: This is one
line in the control source section of the unbound textbox
ctlCurrentLine ' Name of Control
=GetLineNumber() ' item in control source section
color examples: unbound text box with properties (Format tab) set to
specified color: green = 65280; red=10092543
The back style is set to transparent.
BackColorRed <-- Name of unbound box
=IIf(([PromiseTime]<=Time() And [PromiseDate]<=Date()) Or [PromiseDate]
<Date(),"ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ
ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ",Null) Note: This is one line in
the control source section of the control
BackColorGreen <-- name of unbound box
=IIf([TicketStatus]="Picked
Up","ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ
ÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛÛ",Null) <--- One line
The special characters help determine how long the color expands across the
entire record not just the TicketStatus field. Also no that BackColorRed and
BackColorGreen are placed behind your regular fields and is stretched to the
width of your entire record data.
'Code behind the form
Function GetLineNumber()
Dim RS As Recordset
Dim CountLines
Dim F As Form
Dim KeyName As String
Dim KeyValue
Set F = Form
KeyName = "AutomaticTicketCtr"
KeyValue = [AutomaticTicketCtr]
On Error GoTo Err_GetLineNumber
Set RS = F.RecordsetClone
' Find the current record.
Select Case RS.Fields(KeyName).Type
' Find using numeric data type key value.
Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, _
DB_DOUBLE, DB_BYTE
RS.FindFirst "[" & KeyName & "] = " & KeyValue
' Find using date data type key value.
Case DB_DATE
RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
' Find using text data type key value.
Case DB_TEXT
RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
Case Else
msgbox "ERROR: Invalid key field data type!"
Exit Function
End Select
' Loop backward, counting the lines.
Do Until RS.BOF
CountLines = CountLines + 1
RS.MovePrevious
Loop
Bye_GetLineNumber: ' Return the result.
GetLineNumber = CountLines
Exit Function
Err_GetLineNumber:
CountLines = 0
Resume Bye_GetLineNumber
End Function
Private Sub Form_Click()
Me!ctlCurrentRecord = Me.SelTop
End Sub
Private Sub Form_Current()
Me!ctlCurrentRecord = Me.SelTop
End Sub
In the code for the form open event
Private Sub Form_Open(Cancel As Integer)
.....
' This determines on whether the control is visible and has the color
showing behind your data.
If Sdt!DispatchColorYN = -1 Then
Me!BackColorRed.Visible = -1
Me!BackColorYellow.Visible = -1
Me!BackGreen.Visible = -1
Else
Me!BackColorRed.Visible = 0
Me!BackColorYellow.Visible = 0
Me!BackGreen.Visible = 0
End If
Note for above: The Sdt!DispatchColorYN is a recordset reference/field name
in a different table that determines if colors are used in the Dispatch
window. This line can be coded different based on your requirement or
completely removed.
