First Published 23 Nov 2022


Many years ago, I created code to align complex text lists as part of my SDA Link application for schools: https://isladogs.co.uk/sda-link/


The screenshot below shows the list more clearly:


I have just watched an excellent presentation by ex-MVP, Duane Hookom, to the Access Lunchtime User Group where he did something very similar.
You can watch the session video on YouTube by clicking this link: Table of Contents in Access Report

This has prompted me to show how I achieved this effect.

First of all, a little bit of background information may be useful.

The SDA Link application is used to update school, student and staff data from an external schools information management system (SIMS)
It can be run manually if required but is normally run unattended in Automatic mode to update data at a scheduled time each night.

The update process contains many lengthy procedures.
It can typically take 30-45 minutes to complete dependng on the size of the school and the amount of data being updated.

The text list is automatically updated after each procedure has completed indicating the time taken and whether it ran successfully (or not)


The text is entered in a textbox control called txtErrors. The form is called frmMain

The procedure is called WriteTextEntry which is saved in a standard module.
It is called at the end of each update procedure to add a new line for that procedure.

This continues until the update has completed



CODE:

Sub WriteTextEntry(strEntry As String)

On Error GoTo Err_Handler

Dim L As Integer, S As Integer, T As Integer, icount As Integer

L = Len(strEntry)      'length of procedure text entry
S = Len(strSuccess)      'length of OK/Fail text entry
T = Len(Format((ShowTickCount / 1000), "0.00") & "s")      'show tick count measures total time in milliseconds

If Left(strEntry, 1) = "=" Then
      'for header / footer section . . . omit timing
      strEntry = strEntry
Else
      'procedures section - show OK/Fail & timing
      'adjust the numbers as necessary to match the width of the textbox used and font size
      If Len(strEntry) < 105 Then
            If 98 - L > 0 Then
                  strEntry = strEntry & String(83 - L, ".") & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & " " & strSuccess
            Else
                  strEntry = strEntry & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & " " & strSuccess
            End If
      Else
            strEntry = Left(strEntry, 100) & String(2, ".") & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & " " & strSuccess
      End If
End If

If IsOpen("frmMain") Then
      strErrMsg = strErrMsg & ";" & strEntry
      Forms!frmMain.txtErrors.RowSource = strErrMsg
      Forms!frmMain.txtErrors.Requery
      icount = Forms!frmMain.txtErrors.ListCount - 1
      Forms!frmMain.txtErrors = Forms!frmMain.txtErrors.ItemData(icount)
      Forms!frmMain.Repaint
End If

Exit_Handler:
      Exit Sub

Err_Handler:
      'do NOT show error messages when run unattended in Automatic mode
      If Not AutoFlag Then
            MsgBox "Error " & err & ": " err.Description & " in WriteTextEntry procedure"
      End If

      Resume Exit_Handler

End Sub

NOTE:
1.   A monospaced font such as Courier New should be used. Each character occupies the same width and ensures each part of the text is aligned in each row

2.   As the process is normally run unattended overnight, the update summary is also saved to a text file.
      This is automatically emailed to the program administrator when the update has completed.
      He/she can easily check for issues on arrival at work the next morning.





Feedback

Please use the contact form below to let me know whether you found this article / code useful or if you have any questions.



Colin Riddington           Mendip Data Systems                 Last Updated 23 Nov 2022

Return to Code Samples Page

Return to Top