Sure.
Here's the updated code with comments and the requested message box of record counts.
Code:
' ----------------------------------------------------------------
' Procedure Name: GetDistinctStrings
' Purpose: Routine to process the result of query "qryExampleDataKeywordExtraction" and
' populate a table with Distinct string values.
' Show user records counts in and out when finished.
' Procedure Kind: Sub
' Procedure Access: Public
' Author: Jack
' Date: 23-Feb-21
' ----------------------------------------------------------------
Sub GetDistinctStrings()
10 On Error GoTo GetDistinctStrings_Error
Dim TxtArr() As String 'array to hold extracted strings
Dim i As Integer 'utility counter for processing array elements
Dim jCnt As Integer 'counter of input records
Dim db As DAO.Database
Dim rsIN As DAO.Recordset 'input
Dim rsOUT As DAO.Recordset 'output
Dim CreateTableSQL As String 'sql to create table tblTest
Dim CreateIndexSQL As String 'sql to create index no duplicates
20 CreateTableSQL = " create table Tbltest (MyID Counter,FieldRequired text(130) " _
& " CONSTRAINT MyId PRIMARY KEY );"
30 CreateIndexSQL = "CREATE UNIQUE INDEX CustID " _
& "ON TblTest(FieldRequired) ;"
40 Set db = CurrentDb
50 CurrentDb.Execute CreateTableSQL, dbFailOnError 'execute the create Table
60 CurrentDb.Execute CreateIndexSQL, dbFailOnError 'execute the create index
70 Set rsIN = db.OpenRecordset("qryExampleDataKeywordExtraction", dbReadOnly)
80 Set rsOUT = db.OpenRecordset("TblTest")
90 Do While Not rsIN.EOF 'read each input record in loop
100 jCnt = jCnt + 1 'increment the inut record count
110 Debug.Print rsIN!Extraction 'show input record to immediate window
120 TxtArr = Split(rsIN!Extraction, ";") 'split the input based on ";"
130 For i = 0 To UBound(TxtArr) 'loop through the array elements
140 rsOUT.AddNew 'set up to add record to output
150 rsOUT!fieldRequired = Trim(TxtArr(i)) 'trim contents of array element
160 rsOUT.Update 'add the extracted string to output
170 Next i
'Clear the array
180 For i = 0 To UBound(TxtArr) 'reset the array to empty
190 TxtArr(i) = ""
200 Next i
210 rsIN.MoveNext 'move to get the next input record
220 Loop
230 On Error GoTo 0
GetDistinctStrings_Exit:
'Provide user with record counts in and out
240 MsgBox "Number of input records processed: " & jCnt & vbCrLf _
& "Number of unique extracted strings: " & DCount("*", "TblTest")
250 Application.RefreshDatabaseWindow 'refresh the database window
260 rsIN.Close
270 rsOUT.Close
280 db.Close
290 Exit Sub
GetDistinctStrings_Error:
300 If Err.Number = 3022 Then
310 Resume Next
320 Else
330 MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetDistinct, line " & Erl & "."
340 GoTo GetDistinctStrings_Exit
350 End If
End Sub