Search subdirectories from Access Form button


I have a button on an Access 2013 form that will open a pdf file from a directory on a server using a wild card character in the file name as below.  I want to search all subdirectories rather than just the "imaging" subdirectory.  Is there a simple way to do this using wildcards in the path?

I have limited skills in VBA.  Any help would be appreciated.

Private Sub Command0_Click()

Call Shell("c:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe \\winr2\imaging\*16896.pdf").  

End Sub
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
see this link for the codes to search files recursively
What if there is more than one PDF file that matches the wildcard character?  Should all of them be opened?

Do you need to specify two wildcard patterns?  (one for the path and one for the file name)
dur2348Author Commented:
The file number is located in a MS Sql database table.  I have linked to the table in an Access database.  Once I locate the file number in the table of the file I want to open I want to put that number into an Access form text field.  That number then should be used in the code of the button and when I click the button there should be a search for that file number and when found the file should be opened in Adobe.  The file numbers begin with a varying number of zeros however if I begin with "*" on the file number, a unique file number and therefore unique pdf is found.  However, the pdf files are stored in multiple subfolders under the "imaging" folder. So one wildcard character seems to enough for the file number and when I enter the file number into the form the file number will always begin with "*".  If possible the path designated in the button should force search of all subfolders of "imaging".  Thanks for everyone's help.  I hope this clarifies.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

So, the PDF files have unique names within the entire tree structure?
dur2348Author Commented:
1. Do you need to process a lot of files or just a single file?  That is, from your user interface and expected use, will the user make several requests/selections in a session or just one?

2. Are there other files in the directory tree or just PDFs?
dur2348Author Commented:
Single file.  
There are other files than PDFs.
Since you have unique file names, you can let the system do the searching and launching:
Call Shell("cmd /c for /R \\winr2\imaging %F in (*16896.pdf) Do ""c:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe"" %F", vbHide)

Open in new window

If you have reader, you might use "C:\Program Files\Adobe\Reader 10.0\Reader\acrord32.exe".  It is a lighter-weight application.
dur2348Author Commented:
Thank you for your help.  Have a problem if I put in wrong file number say *1.pdf then all files with 1 in file name are returned--when you close the first one that comes up in Adobe the next one comes up etc.  Need some type of error handling if person puts in wrong number so they can stop process and insert correct number.  Also, I know this is extension of question but would appreciate advice on how to reference form text field where user enters file number to replace "(*16896.pdf) if text field is "frmEnterFile!txtFileNum".  Thank you again for your help.
That is why I asked you so many questions about the files and the user interface.

Solution: Don't allow the user to type a number.  The number/PDFname should be selected from a list.
Try this.  It should stop after the first one.
Call Shell("cmd /c for /R \\winr2\imaging %F in (*16896.pdf) Do ""c:\Program Files (x86)\Adobe\Acrobat 9.0\Acrobat\Acrobat.exe"" %F & Exit", vbHide)

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dur2348Author Commented:
Works.  Thanks again for your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.