Mailmerge WHERE filter

[HealTheWord]

Hello!


I have a mailmerge code that creates and saves automatically word files in a folder.
A part of the code is written below:

Set MainDoc = ThisDocument
With MainDoc.MailMerge

.OpenDataSource Name:=DB_PATH, SQLStatement:="SELECT * FROM[TABLE NAME] WHERE [FIELD01] = 'VALUE OF INTEREST'"

totalRecord = .DataSource.RecordCount

For recordNumber = 1 To totalRecord

With .DataSource
.ActiveRecord = recordNumber
.FirstRecord = recordNumber
.LastRecord = recordNumber
End With


.Destination = wdSendToNewDocument
.Execute False

Set TargetDoc = ActiveDocument

TargetDoc.SaveAs2 DESTINAZIONE & .DataSource.DataFields("file_name").Value & ".docx", wdFormatDocumentDefault

TargetDoc.Close False

Set TargetDoc = Nothing

Next recordNumber

End With

Set MainDoc = Nothing


End Sub

It works enough well, but at the end of the macro execution code there is a debug error message because the function "totalRecord" reads the total number of the record exisisting in the table not the total number of record filtered by WHERE query.


How can i assign to a variable only the numbers of records existing after filtered them by a WHERE query? I read about a VBA "DCount" function but it's only works with ACCESS.


Thanks for your reply!

[HealTheWord]

any idea?