Open pdf with two criteria to search

[ebbsamsung]

Good day Experts,

Here i am again for a revision of my simple db based of what my boss said. Is it possible to modify this piece of code with the following modification?

Sample:

I have two criteria:
If Not qualify to this criteria
filetosearch = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"
Then this criteria will be used
filetosearch = Me.No & "_" & Me.DocumentNo & ".pdf"
Then find the pdf file


How can i modify this code using a conditional statement?
I dont know where to put exactly the conditional statement.
I can understand only basic or simple vba a novice like me.

Could somebody please help me for this.

Private Sub cmdOpenPDF_Click()
Dim colFiles As New Collection
Dim direc As String
Dim filetosearch As String
filetosearch = Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo & ".pdf"

'you may need to add & ".pdf" onto the filetosearch line if the combobox doesn't have .pdf in it


direc = Nz(DLookup("PDFsFolder", "tblPDF", "PdfId = 1"), "")
RecursiveDir colFiles, direc, filetosearch, True
Dim vFile As Variant
For Each vFile In colFiles
OpenAnyFile (vFile)
Next vFile
'keep as false for a few tests so it only looks in your current folder
End Sub


Function OpenAnyFile(strPath As String)
If FileThere(strPath) Then
MsgBox ("Open PDF file for " & No & "_" & DocumentNo & "_" & RevisionNo & "?")
FollowHyperlink strPath
Else
MsgBox ("File not found")
End If
End Function
Function FileThere(fileName As String) As Boolean
If (Dir(fileName) = "") Then
FileThere = False
Else
FileThere = True
End If
End Function

 

[arnelgp]

you can just test if the file exists andnot to
recurse to the folder which is lenghtly process:

Private Sub cmdOpenPDF_Click()
Dim colFiles As New Collection
Dim direc As String
Dim filetosearch1 As String
Dim filetosearch2 As String
filetosearch2=Me.No & "_" & Me.DocumentNo 
filetosearch1 = filetosearch2 & "_" & Me.RevisionNo

'you may need to add & ".pdf" onto the filetosearch line if the combobox doesn't have .pdf in it
direc = Nz(DLookup("PDFsFolder", "tblPDF", "PdfId = 1"), "")
direc = Replace(direc & "\", "\\", "\")
If Dir(direc & filetosearch1 & ".pdf")<>"" Then
    Application.FollowHyperlink direc & filetosearch1 & ".pdf"
else if Dir(direc & filetosearch2 & ".pdf")<>"" Then
    Application.FollowHyperlink direc & filetosearch2 & ".pdf"
else
    Msgbox "File not found"
end if
end sub

 

[ebbsamsung]

Sir Arnelgp,

Thank you very much for the revision of the code sir arnelgp. Its working fine if i supply the full path of the file which is inside the subfolders.

This is the full path:
S:\Final_Dossier_Files\Part 3. Construction and Commissioning Manual\Section 1_General\Subsection 1-1_General for Construction\1-1-1_Site Quality Assurance Manual with Quality Procedure

But, what i want in the path which listed in the table is up to the:
S:\Final_Dossier_Files\Part 3. Construction and Commissioning Manual\Section 1_General\Subsection 1-1_General for Construction

only and then code will find in each folder where the file is located and view the pdf file if there is and if not a message file not found will appear.

I do hope ill make you understand my explanation sir arnelgp.

Thank you once again!

 

[ebbsamsung]

Sir Arnelgp,

What i mean to say is recursive folder search-find files in sub-directories.
Is is possible for this code?

 

[arnelgp]

Private Sub cmdOpenPDF_Click()
Dim colFiles As New Collection
Dim direc As String
Dim filetosearch1 As String
Dim filetosearch2 As String
filetosearch2 = Me.No & "_" & Me.DocumentNo 
filetosearch1= Me.No & "_" & Me.DocumentNo & "_" & Me.RevisionNo

'you may need to add & ".pdf" onto the filetosearch line if the combobox doesn't have .pdf in it


direc = Nz(DLookup("PDFsFolder", "tblPDF", "PdfId = 1"), "")
RecursiveDir colFiles, direc, filetosearch2 & "*.pdf", True
Dim vFile As Variant
For Each vFile In colFiles
If instr(vFile & "", filetosearch1 & ".pdf")<>0 then
OpenAnyFile (vFile)
elseif instr(vFile & "", filetosearch2 & ".pdf")<>0 then
OpenAnyFile (vFile)
End If
Next vFile
'keep as false for a few tests so it only looks in your current folder
End Sub


Function OpenAnyFile(strPath As String)
If FileThere(strPath) Then
MsgBox ("Open PDF file for " & strPath & "?")
FollowHyperlink strPath
Else
MsgBox ("File not found")
End If
End Function
Function FileThere(fileName As String) As Boolean
If (Dir(fileName) = "") Then
FileThere = False
Else
FileThere = True
End If
End Function

 

[ebbsamsung]

Sir Arnelgp,

Thank you very much for that brilliant modification. It really fits the revision needed for my simple db.

Have a nice day!