Linking files to a record

[Navy Ken]

This may simply be me not knowing exactly what search term to use here on the forum. I want to link a PDF file to each of my records. To be clear I want to link the file I do not want to use the OLE object or the Attachment object. So far that is easy enough, the part I can't find any information on is, I don't want the user manually keying in the file path, I would much prefer them to click a button and get the normal Windows file selection GUI and use that to populate the file path field (in fact I don't even want the user to see the file path in the record). Is this something that Access can do or is that why I am having difficulty finding anything.

 

[mike60smart]

Hi

Yes Access can do this.

See the attached example.

 

[Gasman]

Bit hard hiding the path, if you want them to select it, I would have thought?
Use FileSystemObject to select the file, then put that path into a field. You could make the control for that field a hyperlink, so if the user clicks on it, it will open the file.

FileSystemObject object

Office VBA reference topic

learn.microsoft.com

 

[Navy Ken]

There will be a button to view the file if, for some reason, someone wanted to, but the file is simply there for historic and evidentiary reasons. (I personally don't feel it is needed and they are just eating up space on the share drive, but its not my database I'm just building it.)

 

[Gasman]

Space is cheap these days.
I probably would not waste a button, but just use a click if it was a hyperlink field, or a double click with some code if it was not.
You probably could store the path and a description, and lookup the description for the path to the file, if you really wanted to hide it after it was entered?

 

[theDBguy]

It's your project, but by not showing the path, how would the users verify they didn't accidentally select the wrong file to link, without opening it and viewing its contents?

 

[Navy Ken]

Because of the network drives the file paths are rather long and clunky and I REALLY don't want this already very full record row to wrap around to two lines. Talking with the users the chances of them wanting or needing to use this link are bordering on zero but management wants what management wants. If they want to click a button I'll give them a button. I would be done with this portion already if it weren't for this.

 

[moke123]

How are the files organized? Is there a naming convention? Are they all in one folder or are they organized by folder?

I don't save any paths. For instance, I create an empty Client Folder for each client. Any files for that client are stored there.
On the clients form I use FSO to iterate through that folder and list any files in a listbox showing the filename and hiding the path in column(1).
Double click the listbox to open the file.

 

[The_Doc_Man]

If all you want is to select a file, try this:

Application.FileDialog property (Excel)

Office VBA reference topic

learn.microsoft.com

It tells you which option to use for whichever action you wanted. If you pick a file, you get a string returned with the fully qualified file spec. That becomes easy to store as a hyperlink, which in turn is pretty easy to have a button refer to the link and open it. All without actually showing anyone the full specification.

The link above says "excel.application" but in fact this works with Access too. My search string was "file dialog vba" and this was the first find.

 

[Navy Ken]

My search string was "file dialog vba" and this was the first find.

LOL file dialog. wow. You should have seen the word salad I was trying to use. Thanks Doc.

 

[Gasman]

Because of the network drives the file paths are rather long and clunky and I REALLY don't want this already very full record row to wrap around to two lines. Talking with the users the chances of them wanting or needing to use this link are bordering on zero but management wants what management wants. If they want to click a button I'll give them a button. I would be done with this portion already if it weren't for this.

So perhaps have a filepath field and a filename field, then concatenate them when you want to view the file, and split it when selecting it?

 

[gemma-the-husky]

Because of the network drives the file paths are rather long and clunky and I REALLY don't want this already very full record row to wrap around to two lines. Talking with the users the chances of them wanting or needing to use this link are bordering on zero but management wants what management wants. If they want to click a button I'll give them a button. I would be done with this portion already if it weren't for this.

The space for a form control doesn't have to be wide enough to show the whole path. It will still work the same however wide. You can also press shift+F2 to zoom any control (The current control) to see the entire contents.

Might there be multiple disk files per record?