Access VBA NoMatch = True when I think NoMatch should be False, Where am I going wrong?

Question

Through various kind souls @jericho johnson, and others. I have VBA code that appears to be working. Except for one part. The final 'Else' condition, "Do While Not StrSQL1.NoMatch". It always equals true. Even if the value being referenced in the first Else statement "StrSQL1.FindFirst ([PrimaryKey] = qs.Fields("external_nmad_id"))"

When I hover over the 'external_nmad_id' it shows a string value. When I hover over [PrimaryKey] it shows '[PrimaryKey]="" '. Is the empty quote set referencing a recordset of values - or does that indicate that nothing is being referenced (hence why NoMatch is always True). Or am I missing something elsewhere?

Public Sub EditFinalOutput2()

'set variables
Dim i As Long
Dim intCount As Long
Dim qs As DAO.Recordset
Dim ss As DAO.Recordset
Dim StrSQL1 As DAO.Recordset
Dim IRSfileFormatKey As String
Dim external_nmad_id As String
Dim nmad_address_1 As String
Dim nmad_address_2 As String
Dim nmad_address_3 As String
Dim mytestwrite As String
Dim PrimaryKey As String
Dim box13c_Address As String

'open reference set
Set db = CurrentDb
Set qs = db.OpenRecordset("SunstarAccountsInWebir_SarahTest")

'turn popup messages off
'DoCmd.SetWarnings False

With qs.Fields

intCount = qs.RecordCount - 1
For i = 0 To intCount
'===
'=== Condition 1 Test - validate address
'===
If (IsNull(!nmad_address_1) Or (!nmad_address_1 = !nmad_city) Or 
(!nmad_address_1 = !Webir_Country) And IsNull(!nmad_address_2) Or 
(!nmad_address_2 = !nmad_city) Or (!nmad_address_2 = !Webir_Country) And 
IsNull(!nmad_address_3) Or (!nmad_address_3 = !nmad_city) Or 
(!nmad_address_3 = !Webir_Country)) Then
'=== Address Not Valid, insert into Review table
DoCmd.RunSQL "INSERT INTO Addresses_ToBeReviewed SELECT 
SunstarAccountsInWebir_SarahTest.* FROM SunstarAccountsInWebir_SarahTest 
WHERE (((SunstarAccountsInWebir_SarahTest.external_nmad_id)='" & 
qs!external_nmad_id & "'));"

Else
Set StrSQL1 = db.OpenRecordset("SELECT RIGHT(IRSfileFormatKey, 10) As PrimaryKey, box13c_Address FROM 1042s_FinalOutput_7 WHERE 'PrimaryKey' = 'external_nmad_id';", dbOpenDynaset)
StrSQL1.FindFirst ([PrimaryKey] = qs.Fields("external_nmad_id"))
'===
'=== Condition 2 Test
'===
If StrSQL1.NoMatch Then
    '=== ID Not Found, insert into NotUsed table
    DoCmd.RunSQL "INSERT INTO Addresses_NotUsed SELECT SunstarAccountsInWebir_SarahTest.* FROM SunstarAccountsInWebir_SarahTest WHERE (((SunstarAccountsInWebir_SarahTest.external_nmad_id)='" & qs!external_nmad_id & "'));"

Else
    '=== Address Found, update record
    Do While Not StrSQL1.NoMatch
        StrSQL1.Edit
        StrSQL1.Fields("box13c_Address") = (qs.Fields("nmad_address_1") & qs.Fields("nmad_address_2") & qs.Fields("nmad_address_3"))
        StrSQL1.Update
    Loop
End If
....[more code below]

Answer 1

There are several issues, this is probably the main one:

StrSQL1.FindFirst ([PrimaryKey] = qs.Fields("external_nmad_id"))

FindFirst takes a string as argument. What you have is an expression, which probably always evaluates to False or Null, since [PrimaryKey] is an undefined variable because it is outside of the string.

It should be

StrSQL1.FindFirst "[PrimaryKey] = " & CSql(qs.Fields("external_nmad_id").Value)

with CSql() from here: https://stackoverflow.com/a/36494189/3820271
It works for external_nmad_id being number or string.

To catch errors like this, put Option Explicit at the top of each module. It enforces variable declaration and reports undeclared or misspelled variables/constants at compile time.
To have this automatically in new modules, set the Require Variable Declaration option in the VBA Editor. This is really a must have for VBA development.

Other issues:

1) It is quite confusing to name a recordset variable StrSQL1.

2)

Do While Not StrSQL1.NoMatch

makes only sense if you do a .FindNext in the loop. Normally you always need If with .NoMatch.

3)

intCount = qs.RecordCount - 1
For i = 0 To intCount
    ' ...
    qs.MoveNext
Next i

is not a good way to do a recordset loop - .RecordCount is only reliably set after a .MoveLast. Use this instead:

Do While Not qs.EOF
    ' ...
    qs.MoveNext
Loop

It's simpler and reliable.