MailMergeDataSource Find Record function leads to Execute Merge Failure

I am trying to use Visual Basic in Word to automate a MailMergeDataSource document search followed by an executed MailMerge. I have both steps working independently. However, if I call the SearchForDocument() function first, the ExecuteMerge() fails. I receive a Run-time error '5631': Word could not merge the main document with the data source because the data records were empty or no data records matched your query options.

I can run the SearchForDocument() function and note the record number. Then if I close Word and reopen without saving, I can call the ExecuteMerge() on that record number with success.

I have tried resetting the .ActiveRecord to 1 with no luck.

This VB function searches a data source for a matching doc_id, and returns the record number from the dataset. It works with no apparent issues. Returns an accurate record number from the dataset.

Function SearchForDocument(doc_id As String)
    ' Searches datasource for given record id number.
    
    Dim dsMain As MailMergeDataSource
    Dim numRecord As Integer
     
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = False
    Set dsMain = ActiveDocument.MailMerge.DataSource
    'Initializes at first record because .ActiveRecord method only searches for first match in descending records from current record
    dsMain.ActiveRecord = 1
 
    If dsMain.FindRecord(FindText:=doc_id, Field:="SAMPLE") = True Then
        numRecord = dsMain.ActiveRecord
    Else
        MsgBox "Record " & doc_id & " was not found."
        numRecord = 0
    End If
    
    SearchForDocument = numRecord
  
End Function

This function executes a MailMerge from a given record number from a dataset. It works fine unless I run the SearchForDocument function first. When it fails I get run-time error '5631'.

Function ExecuteMerge(ByVal TargetRecord As Integer)    
    Set myMerge = ActiveDocument.MailMerge
    If myMerge.State = wdMainAndSourceAndHeader Or _
     myMerge.State = wdMainAndDataSource Then
     With myMerge.DataSource
     .FirstRecord = TargetRecord
     .LastRecord = TargetRecord
     End With
    End If
    With myMerge
     .Destination = wdSendToNewDocument
     .Execute
     
    End With
   
End Function

Solution

I have not been able to find a way to make the VBA word .FindRecord() method work with the inbuilt .Execute() method. Replacing .FindRecord() with a loop that iterates over the data source was slow over large data sets. I have not yet gotten an ADO db connection to work, but I intend to continue to explore that option as time allows. Instead of replacing the .FindRecord() method, the following code replaces the mail merge process with an equivalent outcome that does not cause errors. This allows the code to execute VERY FAST.

With ActiveDocument.MailMerge
 If .DataSource.FindRecord(FindText:="look for this", _
    Field:="look_in_here") = True Then
 End If
End With

'Now replicate the Mail Merge .execute() on the active record.

ActiveDocument.MailMerge.ShowWizard InitialState:=5, ShowPreviewStep:=True

Selection.WholeStory
Selection.Copy
Documents.Add DocumentType:=wdNewBlankDocument
Selection.PasteAndFormat(wdFormatOriginalFormatting)

Note: this code is the simplest case of combining .FindRecord() with a merge. This method only searches records below whatever record is active in the dataset. By default it always skips checking the first record. If you intend to perform multiple searches / merges, make sure to also reset:

 .DataSource.ActiveRecord = wdFirstDataSourceRecord