Change Font color of text string.

[nspader]

Hello All,

I have a DB that shows information generated on a form setup to look like a calendar. I am working with Windows 2000 and Access 2000.

Public Sub PutInData()
On Error GoTo Err_PutIndata
Dim sql As String
Dim f As Form
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsHoliday As DAO.Recordset
Dim sqlHoliday As String
Dim MyDate As Date
Dim i As Integer
Dim strShiftDetails As String
                   
Set f = Forms!frmCalendar
   
For i = 1 To 37
  f("text" & i) = Null
Next i
    
sql = "SELECT * FROM [qryShifts] WHERE ((MONTH(ShiftDetailDate) = " & _
       f!month & " AND YEAR(ShiftDetailDate) = " & f!year & ")) " & _
       "ORDER BY tblShiftDetail.ShiftDetailDate, tblEmployee.EmployeeFirstName;"
sqlHoliday = "SELECT * FROM [tblHoliday] WHERE ((MONTH(ShiftDetailDate) = " & _
              f!month & " AND YEAR(ShiftDetailDate) = " & f!year & ")) " & _
              "ORDER BY [tblHoliday].[ShiftDetailDate];"

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql)
Set rsHoliday = db.OpenRecordset(sqlHoliday)
    
If rs.RecordCount > 0 Then
  For i = 1 To 37
    If IsDate(f("date" & i)) Then
    MyDate = Format((f("date" & i)), "mm/dd/yyyy")
      Do While Not rs.EOF
        If rs![ShiftDetailDate] = MyDate Then
            If rs![ShiftDetailOnCall] = True Then
                strShiftDetails = strShiftDetails & Left(rs!EmployeeFirstName, 1) & Left(rs!EmployeeLastName, 1) & " " & _
                                    rs!ShiftStartTime & "-" & rs!ShiftEndTime & " On Call" & vbCrLf
            Else
                strShiftDetails = strShiftDetails & Left(rs!EmployeeFirstName, 1) & Left(rs!EmployeeLastName, 1) & " " & _
                                    rs!ShiftStartTime & "-" & rs!ShiftEndTime & vbCrLf
            End If
        End If
        rs.MoveNext
      Loop
      If Len(strShiftDetails) > 0 Then
        f("text" & i) = Left$(strShiftDetails, Len(strShiftDetails) - 2)      'Strip vbCrLf
      End If
      strShiftDetails = ""      'Reset
      rs.MoveFirst
      rsHoliday.FindFirst "ShiftDetailDate = #" & MyDate & "#"
        If Not rsHoliday.NoMatch Then
          strShiftDetails = rsHoliday![Description] & " - Help Desk Off!!"
          f("text" & i) = strShiftDetails
          strShiftDetails = ""
        End If
    End If
  Next i
End If

rsHoliday.Close
Set rsHoliday = Nothing
rs.Close
Set rs = Nothing
Set db = Nothing
    
Exit_PutIndata:
  Exit Sub
  
Err_PutIndata:
  MsgBox Err.Description, vbExclamation, "Error in PutIndata()"
  Resume Exit_PutIndata
End Sub

What I want to do is if [shiftdetailonca ll] = True then change the font color to RED for information stored (Rather then putting On Call text in place). However I need to have the rest of the text left Black.

Note: It does not have to be a color change, but I need someway to visually set it appart from the rest.

Information is being viewed in a textbox on the form, then opened in a report.

Can anyone help with achieving this? I am at a complete loss. Can this even be done?

Thank you in advance for any advice on this.

Nick

[missinglinq]

"What I want to do is if [shiftdetailonca ll] = True then change the font color to RED for information stored"

I'm not at all sure what you mean by this. Formatting is never done for information stored but rather forinformation displayed!And your code is way too complex for me to understand remotely.

Any formatting will have to done thru your form. If the form is datasheet or continuous the formatting will have to be done thru Conditional Formatting from the menu, otherwise it may also be done thru code in the Form's OnCurrent event.

Linq ;0)>

[ADezii]

Nick, if I interpret your request correctly, this cannot be done. Your request would entail different formatting (ForeColor, Underline, Italic, etc.) for individual lines in a Standard Text Box Control, depending on whether or not the [ShiftDetailOnCa ll] Field was True. To the best of my knowledge, this cannot be done in a Standard Text Box Control. You can, however, specify unique Text within the strShiftDetails Variable to indicate this condition. This is, however, what you basically have already:
[CODE=vb]
If rs![ShiftDetailOnCa ll] = True Then
'(C) - at end of String indicates On Call Status
strShiftDetails = strShiftDetails & Left(rs!Employe eFirstName, 1) & Left(rs!Employe eLastName, 1) & " " & _
rs!ShiftStartTi me & "-" & rs!ShiftEndTime & " (C)" & vbCrLf
Else
strShiftDetails = strShiftDetails & Left(rs!Employe eFirstName, 1) & Left(rs!Employe eLastName, 1) & " " & _
rs!ShiftStartTi me & "-" & rs!ShiftEndTime & vbCrLf
End If[/CODE]
NOTE: If there was only a Single Shift per day, this could be done since the Formatting would apply to the entire Control.