Indicate when there is no match when comparing Excel data to Access recordset

Question

I'm trying to use noMatch in Excel VBA code.

I have a spreadsheet where a "PackNum" is compared to an Access recordset (query).
When there is a match it inputs the data in Excel.
When however there is no match it grabs the first record.

For i = 2 To lrow
    UpSAddPool.FindFirst ("[Packnum]= '" & Adds.Range("A" & i).Value & "'")
    If Adds.Range("A" & i).Value <> "" Then
        Adds.Range("C" & i).Value = UpSAddPool.Fields("[Year]").Value
        Adds.Range("D" & i).Value = UpSAddPool.Fields("[Catid]").Value
    Else
    End If
Next i

I'd like if there is no match to call out that there is no match.

Code I tried.
The line If UpSAddPool.FindFirst("[Packnum]= '" & Adds.Range("A" & i).Value & "'").NoMatch is going to error.

For i = 2 To lrow
    If UpSAddPool.FindFirst("[Packnum]= '" & Adds.Range("A" & i).Value & "'").NoMatch Then
        Adds.Range("D" & i).Value = "No match found"
        Adds.Range("D" & i).Value = "No match found"
    Else
        UpSAddPool.FindFirst ("[Packnum]= '" & Adds.Range("A" & i).Value & "'")
        If Adds.Range("A" & i).Value <> "" Then
            Adds.Range("C" & i).Value = UpSAddPool.Fields("[Year]").Value
            Adds.Range("D" & i).Value = UpSAddPool.Fields("[Catid]").Value
        Else
        End If
    End If
Next i

Try UpSAddPool.FindFirst(criteria). Then If not (UpSAddPool.NoMatch) then ...action ..End If.ISee doc (learn.microsoft.com/ru-ru/office/client-developer/access/…) — ValNik
– ValNik, Commented Aug 22 at 14:19
Please check the appropriate tag to the question. Why is it Excel? — Black cat
– Black cat, Commented Aug 22 at 14:26
@Blackcat because the macro is through excel, and the Findfirst is between excel and Access where Excel is where everything takes place. Access is just looked at in the background. — Deke
– Deke, Commented Aug 22 at 14:33
Load your recordset into an array and iterate through the array to do your search (faster than it sounds). If it doesn't find any thing, then use the first row in the array. — Frank Ball
– Frank Ball, Commented Aug 22 at 16:02

FunThomas · Accepted Answer · 2025-08-22 14:22:35Z

3

Assuming UpSAddPool is a DAO.Recordset: According to the documentation, when FindFirst doesn't find anything, the property NoMatch is set to True.

If a record matching the criteria isn't located, the current record pointer is unknown, and the NoMatch property is set to True.

But your code uses that wrong. FindFirst is a Method, but it doesn't return anything (it's not a function). Using UpSAddPool.FindFirst(...).NoMatch would work only if the Method would return a RecordSet.

So instead, first issue the FindFirst and after that check the NoMatch-property (untested as I don't have Access available at the moment, but you get the idea)

UpSAddPool.FindFirst("[Packnum]= '" & Adds.Range("A" & i).Value & "'")
If UpSAddPool.NoMatch Then
   (...)
Else
   (...)
End If

answered Aug 22 at 14:22

FunThomas

31.3k4 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Deke Aug 22 at 14:38

Thanks this solved it. Really appreciate your help and the explanation. I don't use FindFirst often so I was fiddling a lot with it.

Collectives™ on Stack Overflow

Indicate when there is no match when comparing Excel data to Access recordset

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related