Step 1. Forget the code I posted with XMLMap, customXML and linked CCs. You have invested your time going down a different path. I thought that was the best way to do it but all the stuff you have since learnt is completely different to that pathway. Your chosen pathway is faster to grab the initial data from Excel but slower and more code-intensive everywhere else.
Step 2. It is easier to search a recordset than it is to find something in an array. You can find something in a recordset in three different ways:
a. Use the SQL query with "WHERE [fieldname] = 'target'" which builds the recordset to do the filtering so only records of interest are in the recordset. Use different SQL queries to grab different records if your connection to the database is still open. You have 4 SQL strings but only use one of them to build one recordset. You can build 4 recordsets with 4 queries if you want to.
b. Use the
.Filter command to go directly to a record in a recordset
c. Loop through all records until a condition is met (this is the only way to do it with an array)
Step 3. Use the Filter command as listed above
Step 4. Yes
Step 5. recSet!person_number is returning the value stored in the current record's person_number field of recordset resSet.
Step 6. person_number is the name of the field (column title in Excel)
Step 7. Your use of the word Position is confusing me. When dealing with recordsets this is the terminology that aligns with an Excel table-speak
Recordset = Table
Record = Row in Table
Field = Column in Table
Fieldname = each cell in first (or title) row of table
Index = the row number (excluding the title row)
Step 8. Agreed, don't even go to array.
I'm getting lost with the large chunks of info you are posting and where your questions are. How about you step back and digest the information provided above and then come back with a focused question that should be easier to answer. Stay working with the Recordset and don't make the additional steps to arrays or concatenated strings unless you are planning on storing the data for use after the macro finishes.