DLookup and Multiple files

[PALman]

The code below sets a top level path and then searches sub-folders for material certs booked against one particular job number in a table called Booking In Log and works very well.
However it only returns the first material cert (M-Cert) found.
Can anyone show me how to loop this code to have DLookup return multiple documents found against one JobNo.
Or perhaps running a query would be best but I need to know how to call the query and code the result to replace line 5…
Document = DLookup("[M-CertNo]", "Booking In Log", "[JobNo]='" & Forms![QP Status]![JobNo] & "'".

'Return required path name from Folder Paths Table
Path = DLookup("[Path]", "[Folder Paths]", "[PathNo] = 'Path02'")

'In this instance find M-Cert/s against JobNo in Booking In Log by searching subfolders of Material Certs Folder
'Check for multiple documents/certs
Document = DLookup("[M-CertNo]", "Booking In Log", "[JobNo]='" & Forms![QP Status]![JobNo] & "'")
'Add filename extension
Document = Document & ".pdf"
'And assign to Filename
Filename = Document

With Application.FileSearch
.NewSearch
.LookIn = Path
.SearchSubFolders = True
.Filename = Document
.MatchTextExactly = True
.FileType = msoFileTypeAllFiles
If .Execute() > 0 Then
MsgBox "There were " & .FoundFiles.Count & " file(s) found."
For i = 1 To .FoundFiles.Count
' MsgBox .FoundFiles(i)
FollowHyperlink .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With
Any help is much appreciated.

 

[PHV]

You may try to play with the FindFirst, FindNext and NoMatch methods/property of a DAO/Recordset object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PALman]

Thanks PHV,
I have never used the Recordset object but shall search for examples and give your suggestion a try,
Thanks