Hyperlink To Server Location?

[shoelesscraig]

I've been playing with hyperlinks on an Access Form and know how to do the basics. Webpages are a no-brainer, and so is linking to a spot on a server (network location at my place of work).



For example, I can make a hyperlink that connects me to "\\server\folder1\folder2\1234" with no problem.

On my form, I have a different proposal number for each record. The name of the field is Prop#.

So I have 2 questions:
1) How do I put a variable in my hyperlink? For example, when I'm viewing the record that has proposal #2222, clicking on the link would take me to "\\server\folder1\folder2\2222"?

2) A more complicated question (and I'm not even sure it can be done)...The folders on our server are actually NOT structured as I said above...they actually contain a folder that further divides the proposals into groups of 1000. An example of a location would be "\\server\folder1\folder2\0000-0999\0345" or "\\server\folder1\folder2\1000-1999\1584" and so on. So, is there any way to set up the hyperlink so that if I'm viewing the record with proposal 3141 on it and I click the hyperlink, it would take me to "\\server\folder1\folder2\3000-3999\3141"?

I know that in order to do that it would have to realize that 3141 is between 3000-3999 and use that folder...then once inside that it would open 3141 (which is just equal to the field Prop#).

I believe the variable part for the exact Prop# is probably easy, I'm sure there is a way to put that variable in. But, I'm thinking the second question is much more complicated and can't be done. I just figured I'd ask people smarter than me to make sure before I write it off. Thanks ahead of time...

[RuralGuy]

Do you have any background or aversion to code?

[shoelesscraig]

Not sure that I understand the question...Am I familiar with or have any problems with code? I am what I guess you would call intermediate level...I have written a fair amount of VB code for this database already and I guess some of it is fairly complicated. In a former life, I programmed for a living and used a decent amount of VB code, but honestly I never got SUPER good at it. I'm great at looking at code, figuring out how it works, and then implementing that for other things...but when it comes to just pulling code out of thin air, I'm not that great.

Can this be done with variables in code? If so, I'm more than willing to learn anything you're willing to teach.

[Micron]

0) Special characters (save for underscore _) should never be part of an object name in Access Prop#.
1) You'd concatenate the string path: strPath = "\\server\folder1\folder2" & variableThatContainsYourFileNameAndExtension
2) Should be doable. First, what about lower numbers (1, 2 or 3 digits). Do they exist?

[shoelesscraig]

0) I understand, but it's been that way for years...not changing it now...
1) Where do I input that code?
2) They do not exist...everything is 4 digits. I guess eventually we'll have 5 digits, but that's years away...I'll tackle that when I get there!

[RuralGuy]

If so, I'm more than willing to learn anything you're willing to teach.

There are *many* helpers that can assist here. I see Micron has already jumped in. He's real good. I'll just watch for now.

[shoelesscraig]

Let me edit my above response for "0)"....I'm not changing it now unless I have to! I'm not refusing, I'm just reluctant! Lol!

[RuralGuy]

You can make a procedure (code) that executes when single (or double) clicking on that control. That procedure can than take the value in the bound field and create any string you want and go to it. I should disclose that I personally do not like HyperLink fields as too limiting.

[RuralGuy]

Either one of could write it for you is you would like. Micron's code is actually better than mine.

[Micron]

RG, you are too kind! I will try to not let that swell my head.

Where do I input that code?

What's going to drive the event - button click?
The simplest approach is likely the FileDialogFilePicker to get the complete path to the file in question, then concatenate that path into a string. I'll bet you'll say that user intervention is not desired for some reason (dang those managers!!). In that case, what needs to be known is the button name (or event driver), where the file name info comes from and what that data looks like (2222.txt; i.e. includes the extension?) and where the path parts come from.

My approach would then be like this:
- test the file name for non-numeric characters (2222 OK, a222 fails)
- strip off the extension so that only the number remains unless you say there isn't one
- determine the number of characters, minus 1 (should take care of the future 5 digit name)2222 --> 4-1=3
- append that many zeros to 1 and convert that to a number (e.g.1000 is my Divisor)
- MOD the filename by Divisor (2222 MOD 1000 = 222)
- subtract 222 from filename to get the lower boundary (2000) and add 999 to get the upper boundary (2999)
- concatenate the path: "\\server\folder1\folder2" & upper & "-" & lower & "" & filename (e.g.222) & extension
So you can see that besides the event driver, we'd need to know where the path parts and file name ( as well as extension or not), comes from. If no extension, I don't know how you'd complete the hyperlink. All of this would require at least one variant data type in order to flip between text and numbers to do the math. As long as the file name only looks like a number, it should work.
Easy, right? If anybody sees an easier route, by all means, jump in as I tend to complicate things. You indicated you are somewhat vba savvy. Want to take a crack at it?

[shoelesscraig]

Let me add this bit of info if it helps...

1. I'm the ONLY person who will EVER use this database. So I'm not concerned with intervention by anyone.
2. We have these proposal numbers (4 digits long) each having their own folder (for example "\\server\folder1\folder2\2000-2999\2345"). Inside that 2345 folder are a bunch of files. I just wanted to be able to click in the database and open that top 2345 folder when I was looking at the record for Proposal number 2345. Only reason for this is that I'm lazy! I can always go to My Computer and browse to that location, but I thought a link would be SWEET!

I guess I say all of that to say...there is no file type or extension. I just want to open a folder that has files in it. Each proposal could have different types of files associated, so even if I wanted to open a particular file, I can't. They are not all the same.

3. I can create a button to click, or I can click the already displayed Project Number (or double click it). I'm not picky at all.

[shoelesscraig]

So I'm definitely willing to play with this...however...I need some pointers.

How do I determine the number of digits in the Proposal number using VB code?
What is MOD? How do I command it?
When I create "Upper" and "Lower"...how is that done? Isn't it something along the lines of "Dim strUpper As String"...or something like that? This is something I haven't done from scratch before.


I have figured out how to just make clicking the proposal number take me to the folder I desire:

FollowHyperlink ("\\server\folder1\folder2\3000 - 3999" & Me.ProjectNum) will take me to what I need...assuming the proposal number I just clicked on is inside that 3000-3999 range.

[shoelesscraig]

UPDATE!!

I'm feeling pretty good right now! Haha!

So I've figured out everything...except, how do I determine how many digits are in the Proposal #? I've got it to work with the 4 with no problems, but when we change to 5, it'll stop working!

I also want to do this with our Project Numbers, which are digits and I've got that working great as well! Honestly, by the time those become 7 digits, I'll be old and retired. But, I could see the 4 digit becoming a 5 during my tenure. Plus, I'm just a guy that likes to know how things work...

So:
1. How do I determine the number of digits in a Proposal Number?
2. How do I add zeros to a number. For ex, if I have the number 3, how do I make it 3000?

[Micron]

This is why I thought the last number was a file

if I'm viewing the record with proposal 3141 on it and I click the hyperlink, it would take me to "\\server\folder1\folder2\3000-3999\3141"

Not sure if you realize that this is not what I was suggesting

I can always go to My Computer and browse to that location

I suggested the folder picker: Application.FileDialog(msoFileDialogFolderPicker)
It provides the path to your folder and you create the hyperlink with it. LOOKS like the Windows file dialog, but it's not exactly. You decide how to build it (default file extension if needed) and as I recall, which buttons you need, complete with the ability to caption them. Maybe Google it for more info.


So which way do you want to go?

[shoelesscraig]

I'm confused...I said I already figured out how to do it...

I know that you weren't suggesting My Computer...I was saying it as a joke. I'm looking to be lazy here. While I'm in my database, I just wanted a link to take me where I needed to go.

ALL I need to know know is how to figure out, in the VB code, how many digits are in a Proposal Number AND how to add zeros to the end of a number (how do I make 3 become 3000?).

Once I figure that out, I think I'm good to go.