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
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
'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
strEntry = strEntry & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & " " & strSuccess
strEntry = Left(strEntry, 100) & String(2, ".") & String(15 - T, ".") & Format((ShowTickCount / 1000), "0.00") & "s" & " " & strSuccess
If IsOpen("frmMain") Then
strErrMsg = strErrMsg & ";" & strEntry
Forms!frmMain.txtErrors.RowSource = strErrMsg
icount = Forms!frmMain.txtErrors.ListCount - 1
Forms!frmMain.txtErrors = Forms!frmMain.txtErrors.ItemData(icount)
'do NOT show error messages when run unattended in Automatic mode
If Not AutoFlag Then
MsgBox "Error " & err & ": " err.Description & " in WriteTextEntry procedure"
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.
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