how to open a specific file using a search function in access 2003 (1 Viewer)

[awade]

Good Afternoon,
I have a access 2003 database, and i am wondering if i can't setup a search function in a form that will open a file in windows explorer?

What i would like to do is have a test box that i enter in a number for example: 1234 then have a button named "Search" hit that button and it opens a corresponding file in windows explorer named 1234, or just opens that folder directly..

Is this possible as it will make my job so much easier.

I am very new to this so hopefully it won't be too involved in VBA code...

Thanks in advance

Andrew

 

[JHB]

Do you mean - if a file with the name typed in the textbox is found in a directory the open explore within that directory or ????
If not found what then ??
Could you explain a little more what you are trying to do!

 

[awade]

Hi JBH,

I would like to have a text box which i would type in a number for example 1234.
I would then hit a command button named "search"
That would then open a specific folder in my computer named 1234.

The folders are located on the "C" drive "C:\Program Files\eDNA\Engine Core Folders"

Each folder in that directory has its own number, i would like to be able to type any one of those numbers in the search text box in the database to open up its corresponding folder in that directory.

If they type a number in that is not in that directory, a message box would pop up saying "Serial Number does not exist, please try again"

Thanks

 

[JHB]

Create a form, put in a textbox, call it SearchBox and a button call it Search.
Place the below code in the click event for the button, remember to change the foldername to fit yours.

Private Sub Search_Click()
  Dim Foldername As String
    
  Foldername = "C:\Program Files\eDNA\Engine Core Folders\" & Me.SearchBox
  If Len(Dir(Foldername, vbDirectory)) = 0 Then
    MsgBox ("Serial Number does not exist, please try again")
  Else
    Shell "explorer.exe """ & Foldername & "", vbNormalFocus
  End If
End Sub

 

[awade]

Thanks JHB,

I have tried the code you have provided, and i keep getting the message box " serial number does not exist, please try again"

I know the folder has that serial number folder inside of it but i can not get the serial number to open the file corresponding to the serial number.

Please help....

Regards

awade

 

[JHB]

By me it runs okay!
Try by making a new folder direct in the C drive.
Something like "C:\Access programmer" and in there create a folder with the name "123"
Then open the form input 123 in the textbox and remember to change the path in the code to C:\Access programmer\"

 

[awade]

Thanks JHB,
I finally got it to work, but it only opens the folder with all the folders in it not the one i have typed the serial number in for.

The Engine Core Folders opens up, but inside that is 58 separate folders all labeled with individual serial numbers.

What i am after is when i type the serial number in and hit search, that it opens up the specific serial number folder within that folder.

 

[JHB]

Like I said before, it runs okay by me.
I'm searching for a folder "20010" in "C:\Access programmer"
When I click the search button it open the folder 20010, see below picture, (Show your code).

 

[awade]

If you had multiple folders in Access Programmer will your search only open 20010?
This is the problem I'm having.
I have multiple folders in Engine Core Folders, and the code opens the folder contains the serial number, but not the specific serial numbered folder..
Thanks for all your help so far.
Awade

 

[JHB]

If you had multiple folders in Access Programmer will your search only open 20010?

Yes it will - you didn't show your code!
I suppose you change the code I gave you, because you wrote in post #5 "I have tried the code you have provided, and i keep getting the message box serial number does not exist, please try again ..." and in post #7 "I finally got it to work, but it ..." so you must have change something to get it to work.

 

[smig]

why not creating a list of existing folders and populating it to a comboBox to open the correct folder ?

 

[awade]

smig,

I have numerous folders each containing different information i would like to create a search function for the user to type in the serial number they are after and that folder automatically opens.

 

[awade]

JHB,
I have included a test database so you can see the code that I am running.
I have tried different variations of the code to try and open the specific folder with no success. All I can get to open is the folder containing the serial number, not the specific serial number.
I will upload a screen shot of the folder In a separate post.

Cheers

awade

 

[awade]

JHB,
I have included screen shots of the code and the folder so you can see what i am doing. I have tried different things with the code, but i can not get the specific serial number folder to open, just the folder containing the serial number to open.
Thanks in advance

awade

 

[JHB]

JHB,
I have included screen shots of the code and the folder so you can see what i am doing. I have tried different things with the code, but i can not get the specific serial number folder to open, just the folder containing the serial number to open.
Thanks in advance

awade

Your code isn't exactly as the code I showed you.Your code isn't using the value in the searchbox.

Private Sub Search_Click()
Dim Foldername As String
[B]
'Your code.[/B]
Foldername = "C:\Program Files\eDNA\Engine Core Folders Borescope Photos\" & Me.Searchbox
If Len(Dir("[B][COLOR=Red]C:\Program Files\eDNA\Engine Core Folders Borescope Photos\[/COLOR][/B]", vbDirectory)) = 0 Then
    MsgBox ("Serial Number does not exist, please try again")
Else
    Shell "explorer.exe """ & [B][COLOR=Red]"C:\Program Files\eDNA\Engine Core Folders Borescope Photos\"[/COLOR][/B] & "", vbNormalFocus
End If
 [B] 
  'My code[/B]
  If Len(Dir([B][COLOR=Red]Foldername[/COLOR][/B], vbDirectory)) = 0 Then
    MsgBox ("Serial Number does not exist, please try again")
  Else
    Shell "explorer.exe """ & [B][COLOR=Red]Foldername[/COLOR][/B] & "", vbNormalFocus
  End If
    
End Sub

 

[awade]

JHB,
Thank you very much.
As i am very new to all of this, i thought that where ever "folder name" was written i would have to put in the directory name.
I have changed the code and it works perfectly.
Thank you for your patience and persistence.

One very happy access user...

awade

 

[JHB]

You're welcome, good luck.