How do I create a dictionary from a specific table field?

Question

I'm writing a script that will take data from text reports generated by a legacy system and import it into a more flexible tracking database. I'm trying to figure out how to build up a dictionary from data within a table so that I can quickly filter through the imported data using the dictionary exists method and only import the data belonging to my shop.

I have a table called tblShop that contains a field called [WorkcenterID]. As my script runs, it's going to be extracting data from the text report, looking at the employee's work center, checking if that employee belongs to me (by comparing the extracted work center text to the dictionary), and if true, writes the data to an import table for further processing.

How do I build the dictionary from the table?

For the curious, here's my code:

Private Sub Command5_Click()

Dim crscd, startdt, stopdt, starttm, stoptm, bldstr, rmstr, evtid, empn, empw As String
Dim i, cd, ci, es, ee As Integer
Dim cdb As DAO.Database
Dim imt, sht As DAO.Recordset
Dim wcDict As New Scripting.Dictionary

Set cdb = CurrentDb
Set imt = cdb.OpenRecordset("tblImport", dbOpenTable)
Set sht = cdb.OpenRecordset("tblShop", dbOpenTable)
'--- grab pasted text data from form ---
strText = Me.InData.Value
'--- split text data into array so we can read line by line ---
arrlines = Split(strText, vbCrLf)
'--- reset line counters ---
i = 0
ci = -1
cd = -1
es = -1
ee = -1

For Each strline In arrlines
    '--- find location of course info ---
    If Left(strline, 17) = "COURSE  NARRATIVE" Then
        cd = i + 2
    End If
    '--- find location of course location info & event ID
    If Left(strline, 8) = "BUILDING" Then
        ci = i + 1
    End If
    '--- find where assigned employee data starts
    If Left(strline, 6) = "EMP NR" Then
        es = i + 1
    End If
    '--- find where assigned employee data ends
    If es > 0 And IsNumeric(Left(strline, 5)) = False Then
        ee = i - 1
    End If
    '--- extract course code and start/stop dates/times
    If i = cd Then
        crscd = Left(strline, 6)
        startdt = Left(Right(strline, 28), 7)
        starttm = Left(Right(strline, 20), 4)
        stopdt = Left(Right(strline, 15), 7)
        stoptm = Left(Right(strline, 7), 4)
    End If
    '--- extract building number, room number and event ID
    If i = ci Then
        bldstr = Trim(Left(strline, 13))
        rmstr = Trim(Left(Right(strline, 55), 9))
        evtid = Trim(Left(Right(strline, 46), 9))
    End If
i = i + 1
Next
'--- clear import table
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM tblImport"
DoCmd.SetWarnings True
'--- dump employee data to import table
For n = es To ee
    empn = Left(Left(arrlines(n), 48), 5)
    empw = Left(Right(Left(arrlines(n), 48), 11), 4)
    '--- verfiy employee belongs to us before importing data
    'If wcDict.exists(empw) = True Then
        'imt.AddNew
        'imt!EmpID = empn
        'imt!Workcenter = empw
        'imt.Update
    'End If
Next

wcDict = Nothing
imt.Close
Set imt = Nothing
sht.Close
Set sht = Nothing
cdb.Close
Set cdb = Nothing
End Sub

Answer 1

The solution is to use the following code to loop through the recordset to extract the required data:

sht.MoveFirst
Do While Not sht.EOF
    empw = sht![WorkcenterID]
    wcDict.Add empw, vbNullString
    sht.MoveNext
Loop
empw = ""

Insert that before the pasted text data grab, uncomment the verify if block at the bottom and it'll work.