Full Version: Format Specific Text In Memo Field
UtterAccess Forums > Microsoft® Access > Access Forms
Hello Everyone,
I have a memo field named PBCrg in a table named T_PB. The field is Rich Text. The memo field lists names seperated by a /. For example: Smith, Joe / Johnson, Kelly / Doe, John / Roberts, Billy, etc. There is another table, T_Drive, with two fields, PKEY and Drivers. What I would like to do is format the names of drivers in the memo field. If John Doe is a qualified driver then his name is colored green. The other names would remain black. The form for T_PB is name F_PB. The code that runs when the form opens is:
Private Sub Form_Open(Cancel As Integer)
'PB Drivers
'this first section deletes the records in T_PB - SQL must INSERT INTO to create first record
'subsequent section SQL will UPDATE the records so as to not create additional records
Dim strX, strSQL, strSQL1 As String
Dim xStart, xEnd As String
    xStart = Format(Forms!Switchboard!txtStart.Value, "0000")
    xEnd = Format(Forms!Switchboard!txtEnd.Value, "0000")
    strSQL = "DELETE * FROM T_PB;"
    strSQL1 = "SELECT tblCoss.[Officer], tblCoss.[Loc], Format([Start],""0000"") & ""-"" & Format([End],""0000"") AS Shift " & _
              "FROM tblCoss " & _
              "WHERE (((tblCoss.[Loc]) Like ""PBdr*"") AND ((tblCoss.[Start]) Between " & xStart & " And " & xEnd & ")) " & _
              "ORDER BY tblCoss.[Start];"
Dim db As Database: Set db = CurrentDb()
Dim rs1 As Recordset: Set rs1 = db.OpenRecordset(strSQL1)
DoCmd.RunSQL strSQL
If rs1.RecordCount = 0 Then
    Exit Sub
End If
strX = rs1![Officer]
'Debug.Print strX
With rs1
    Do While Not .EOF
        strX = strX & " / " & "" & rs1![Officer] & ""
        'Debug.Print strX
End With
DoCmd.RunSQL "INSERT INTO T_PB ([PBDc]) VALUES('" & strX & "');"
'add error traps and clean up - db = nothing etc - when debugging finished
End Sub

Just some history: The use of memo fields was chosen because the reports are eventually sent to supervisors' blackberry phones. Having the names listed horizontally is more ideal. The coloring of qualified drivers is to identify employees that have a CDL in case more are needed on a specific day.
Any help is always apreciated.
Thank you,
" If John Doe is a qualified driver..."
o where is tha data that tells you this?
That data is in table "T_Drive" field [Drivers].
OK I know that is literally what I asked but I was hoping for some explanation of how you work out the competency level for any given person.
Ocan't do it by guesswork. I would not have guessed that a field called Drivers contained a competency value - it sounds more like it holds a driver's name.
So the other field is called PKEY. So does this mean 'driver id'?
If so what is the field in tblCoss that holds the 'Driver id'.
And just to add that if there is match between these two tables, it would seem better to modify your SQL string to include a join to "T_Drive" and get the competency directly, rather than look up the value afterwards.
Having said all this, I have just read again your purpose in doing this. I assume you are already getting the data to your blackberry users in a satisfactory way - what format do you use (pdf?)
I'm working on a different approach. Instead of highlighting the name I'm wrapping them in astericks. I'm using DLookup to see if a name is in the table T_Drive then adding * before and after the name. *Doe, John* / next name.
anagement seems ok with this format.
Thank you for your help Peter 46.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.