Allow Users to Add Links to Files 1

[dedren]

I am using the Issues DB from Microsoft

http://office.microsoft.com/en-us/templates/issue-database-TC001218693.aspx

I want to give my users the ability to add links to files stored on our network. Each record of an issue in the DB should allow different files to be linked to it.

This is how I imagined it would work:

1. User creates a new issue in the DB (via the form)
2. User clicks "Add File" link which opens up a dialog box that lets them find and select the file (much like the windows browse file box)
3. Once they click "OK/Open", the list of linked files is updated to reflect the changes

-Also, the user would be able to click on any file in the list and it would open it in its default program
-As a bonus the file list would only show the name of the file instead of the entire network path.

In lieu of this I created a table for the files (tblFileLink) with the following fields:
FileID - autonumber primary key
FileName - text where descriptive name could be stored
FilePath - hyperlink where path to file could be stored
IssuesID - Lookup foreign key of Issues table to relate the tables to each other

On the form I tried the following:
-To save space I used an unbound combo box to store the list of files (ctrlFile)
-I have a clickable link called "Add File" that has this code in the OnClick Property

Me.ctrlFile.SetFocus
DoCmd.RunCommand acCmdInsertHyperlink

When I try to click "Add File" I get the error "The command or action 'InsertHyperlink' isn't available now."
-I now understand that you cannot inserthyperlink in form view

Since it can't work this way, is there another way to do it?

 

[kjv1611]

Well, easiest quickest way for you, I imagine, is:
1. Edit the underlying table field where the link should go, or add such a field.
2. Format that field as a hyperlink.
3. Have the users copy/paste the address from an address bar or manually type it.

Best method might be to use a FileDialog object in VBA to capture the click of a button or the selection of that field on the form, so that it prompts them for the values. You'll still need the underlying field to be formatted correctly unless you intend to use VBA elsewhere to activate the link.

Using the FileDialog, you can allow the user to browse to and select the folder or file, depending upon the need. You would use the msoFilePicker or msoFolderPicker in that case.

You can Google for oodles of examples and instructions on the latter.

Post back with further questions and progress.

 

[dedren]

Thanks kjv1611,
1 & 2. The field is called FilePath and it is in the table as a Hyperlink type. Is that what you meant?
3. Most of my users don't know what an address bar is and are far below the knowledge of what an average PC user possesses. I would really like to keep it iPhone simple for them.

Concerning FileDialog:
I found this code which should do what I want (and more!) except it requires Microsoft Office object library. For some reason my IT area managed to restrict the ability to use it in Access and won't let me add it.

http://msdn.microsoft.com/en-us/library/aa195878(v=office.11).aspx

Is it possible to force the InsertHyperlink command to work on a form? Maybe somehow switching the subform into Design View behind the scenes, running the command, and reloading the subform in form view with the updated file links? Although I have no idea how to do that it seemed like a possibility.

Again, thank you for the response.

 

[kjv1611]

Microsoft Office object library. For some reason my IT area managed to restrict the ability to use it in Access and won't let me add it.

Really? Are you able to add other references? I'd highly doubt that they would block one specific reference. Which version of Access are you using? Perhaps it's one that's now already included? Have you tried without bothering with the reference to be sure?

For instance, with Access 2010, you no longer have to set a reference to Microsoft DAO 3.6.

 

[dedren]

I use Access 2003.
The problem is I can't even edit my DB without first consulting IT and the last time I dealt with references I had to get approvals which took way too much time and was not worth the effort.

So if there is any way I can avoid doing that again, I'd jump on it. It is also why I can't just try things out right away. I have to wait on one of them to be available...it is silly, but this is where I work.

 

[dedren]

What I try to do is getting a complete solution together in TextPad and just copy and paste stuff into Access when I can. Additionally, since I am on their time, I have little time to bug fix, so if it doesn't work right away I end up having to wait days before I can do more.

 

[kjv1611]

So, can you create a new database, and just do with it what you want, such as adding references?

If so, you could build out this piece there while awaiting approval, and also have a proof concept in place when asked.

And that does sound annoying, but I suppose if done right, it could also be a helpful thing to have such an approval structure for database changes of any sort.

But if you can build your own database from scratch just for handling/testing the msoFilePicker and folderpicker, then that'd be the way to get started.

There are other methods as well, but I think the FileDialog method is THE best way to go if you want to make it easy on the end users.

 

[dedren]

Well, there is no question it is more secure, but it virtually eliminates innovative changes. I have been working with Access DB for 5 years now and barely know anything because most changes I can't test or see the results myself. Either it works and we keep it or it doesn't and we do without it (or live with the bugs). Extremely limited ability to fix bugs, since I never actually do any of the changes myself, I am telling someone else what to do and they are doing it on my behalf. Worst yet is they refuse to hire someone who DOES have access expertise.

So long story short. I need to write it all down in a word document and give it to them, hoping it works...