Search for specific .pdf file at server location and list in listbox

[Pommetje77]

Hello,
I am a newbie on ms access forum.net

I would like to have the VBA script for searching for a specific .pdf file on server location and list this (or multiple revisions of this file) in a list box on my form.
The filled list box shall list the .pdf files and the files needs to be opened by double click.

I do have an example script for adding all files from specific location to the list box, however I am struggling with adding search criteria.

So far I have a form with text box 'drawing location' (which specifies the location of all the .pdf drawings on the server), I am having a text box with drawing number, which shall be the search criteria.
The search criteria shall be based on the first 7 digits of the .pdf name.
There is even a list box on the form where the found .pdf files need to be shown.
It is not important it the script shall be activated by button click or form 'on current' event.
It would be the easiest way to have it done with a function call I believe.

I am using MS Access 2010, 64 bit with Windows 7 professional.



Your help is highly appreciated

thanks in advanced
Ruud

[June7]

For code to list files, review http://www.allenbrowne.com/ser-59.html

[Pommetje77]

June7, thanks for your reply!
However listing files in a list box is not a big deal.
My problem is searching files according criteria and list the results of the search in a list box.

[June7]

I expect Allen's code could handle that.

Call ListFiles("drive:\folderpath", Left(Me.Number,7) & "*.pdf")

[Pommetje77]

I will dig into this coming Friday, I am on business trip the next few days!
Will let you know!

[Pommetje77]

Hello June7,
I have enclosed the database with Allen's code. It gives me all the files in the list box.
With your 'call List files' method, it will not give me any information.
It would be very nice if you can have a look and try to get it work in according with the text below.

Again shortly, what the script should do:
Show all the drawings available in the list box, in reference to the drawing number on the form
The search criteria shall be the first 7 digits of the actual files. All the files are named like 6001002 ISS A, 6001003 ISS C, etc.
I don't want to see the path of the location in the list box, only the numbers with Iss A, B etc.
The found files in the list box shall be clickable, so they open with adobe reader or some other program.

[Pommetje77]

Small correction now
I've got it working with the following call function:

Call ListFiles("C:\Documents\Drawings\pdf", Left(Me.PartNumber, 7) & "*.pdf", True, Me.lstFileList)

The only thing is, I don't want to see the complete file path and it is still not clickable!

[June7]

If you don't want the complete file path, modify code in FillDir function:

colDirList.Add strTemp

What do you mean by 'it is still not clickable'? What is not clickable - the listbox? There is no code behind the listbox. What do you want - to open the PDF when item in listbox selected? Need code in the listbox Click or AfterUpdate event. Review: http://allenbrowne.com/func-GoHyperlink.html

[Pommetje77]

Thanks for your help so far!
So far I have drawing numbers only in my list box!

I indeed want to open the selected .pdf file in the list box to open.
I added the Allen Browne code 'GoHyperlink' including the double click event. However it gives me Error Number 490 (cannot open the specified file).
If I modify the code a bit, I can get it to open the drawing directory, but the pdf won't open unfortunately!

Call GoHyperlink(CurDir & "C:\Documents\Drawings\pdf") gives me Error Number 490 (cannot open the file)

[June7]

Well, if you use CurDir then can't specify C:\ drive & path. Also, there is no filename in that string.

Call GoHyperlink("C:\Documents\Drawings\pdf\" & Me.comboboxname)

Refer to link at bottom of my post for debugging guidelines.

[Pommetje77]

June7, Thank you for your support!
Everything is working!