Open an external folder specific by a form field

[AccessNoob16]

OK, I'm pretty new to Access, but I can already see the power it has and I'm trying to convince my boss that it'll be beneficial to our company. I've built a simple database that basically stores information about past and present projects my company is working on. I would like to add a command button to the project form that will open each project's folder that's located on our server. I'd like the button to open the folder based on the record I'm looking at, without having to browse to the folder, etc. I'm pretty sure this is possible since I've seen other threads that address it, but all of them seem addressed to a more experienced user than myself so some of the steps are omitted. If there is a kind soul on this board that can dumb the process down for me or point me to a thread that does so, I'd be very grateful. Thanks in advance.

[TG_W]

I am sure you have already seen the following code, but this might be good start if you don't mind setting it up for users.

Dim X As Variant
X = Shell("Explorer.EXE \\YOURNETWORK\Projects\Main_Folder\Sub_Folder", vbNormalFocus)

[AccessNoob16]

Ok, some of that makes sense to me, but I'm going to need it even more watered down. Sorry, l
ike I said I'm really new and the book I've been using to teach myself this stuff doesn't seem to have any instruction on this topic.

[TG_W]

Ok. To use what I put up there, you would create a button on your form for each project. Personally, I like to use Labels where enter the text, then play with size/shape/coloring/etc. since the buttons are all gray.

Regardless of how you create your buttons, after adding the project name to each button and making sure the button is still selected, go to the Property Sheet then the Event tab and decide if you want the user to single-click or double-click to open the project folder and click the "..." accordingly.

After the VBA screen opens up, copy and paste the code I entered and change the address parameters after "\\..." keeping the " at the end. You can accomplish this easily by opening each folder, copy the address, and paste to replace. I prefer to use the "\\" instead of the network folder letter in case someone has their folders linked differently.

I hope that helps explain how to use the code better.

[AccessNoob16]

OK that makes sense, but do I have to make a button for each individual project (there are hundreds) or can the button open a folder based on the record I'm currently looking at?

[TG_W]

If they are in the same directory, it might be possible. I haven't needed to do that before. Give me a few minutes to try something.

[TG_W]

What I was trying did not work. I was tryng to place some code at the end of the path that didn't work. Sorry I can't help much further.

[AccessNoob16]

That's OK, I appreciate the effort.

[pbaldy]

Is there something in the record that identifies all or part of the path? You can use Shell or FollowHyperlink. Presuming your record only contains part of the path, build a string with it:

Dim strPath As String
strPath = "\\ServerName\ShareName\" & Me.FolderName

Then use that string with either function.

[AccessNoob16]

Again, thanks, but a lot of that is over my head. To answer your question, I have a Job # field and a Project Name Field. Each folder is named "Job #"-"Project Name" and I'd like the button to access the folder based on the project number only, since it's possible that the Project Name in the DB and the Project Name in the folder name won't be typed in exactly the same. I'll need very basic, step-by-step instructions. Sorry, I feel like the annoying guy who walks in half-way through a movie and wants everything explained.

[pbaldy]

But you're only giving us generic info to go on, so you get a generic example. Basically you have to combine the fixed part of the path with a variable part that comes from your record. What does the complete path you want opened look like? What part of it comes from the record?

If the path contains a name that may have been misspelled, how will using the project number help?

[AccessNoob16]

OK, all of the job folders are on a network folder whose path is "P:\Projects - In process\03 - Projects" One example project folder is "12.0021-C Hidden Hils PSA" On my record, the "Job#" field reads 12.0021 and the "Project Name" field reads Hidden Hills PSA. So the folder name and the Project Name field don't match exactly, since Hills was only spelled with one L in the folder and with 2 Ls in the DB. Can the button open the folder based solely on the "Job#" field and ignore the rest of the folder name?