Linking files to microsoft access records

[primagic]

What is the best way of linking xls documents, word documents, pdfs and so on to an access 2003 record. I don't want to use the hyperlink option but rather a browse dialog box, select file and save/link to record.

Any help please

Thank you

 

[JBinQLD]

G'day primagic,

What a coincidence, I was asked this exact question yesterday by a coworker - we don't work for the same company do we?!

Here you go:

sub AddAttachment_OnClick()

Dim dlgAttach As FileDialog
Dim strMyFile As String
Dim varChosen As Variant


    Set dlgAttach = Application.FileDialog(msoFileDialogFilePicker)
    
    With dlgAttach
        .ButtonName = "Attach"
        .InitialView = msoFileDialogViewList
        .Title = "Choose file to attach"
        .AllowMultiSelect = False
        

        'Set up file types
        With .Filters
            .Clear
            .Add "Word docs", "*.doc"
            .Add "Excel sheets", "*.xls"
            .Add "PDFs", "*.pdf"
        End With
        
        'set default file type
        .FilterIndex = 1

        'show dlg box
        If .Show = -1 Then
            For Each varChosen In dlgAttach.SelectedItems
                strMyFile = varChosen
            Next varChosen
        End If
        
    End With

'dump the chosen file name into a txt control on our form
forms!myform!MyControl=strMyFile

end sub

Have a great weekend,

JB

 

[primagic]

Works great. Thanks for the code mate.

Can always rely on a fellow aussie. Im from Australia too. I assume you are as well from your username. I live in the UK now. My family live in the Redlands

Whereabouts are you?

By the way: What if the files are moved? Is there an easy way to automatically update the file locations or does it have to be done manually?

Thanks again

 

[JBinQLD]

Too easy pri, thanks for the acknowledgment, a thanks is always encouraging I'm in Brissy.

If the files are moved you're rooted but you can trap for this by doing something funky like:

if nz(dir(strMyfile),"")="" then 'not found
    msgbox("File doesn't exist")
    'have the user browse for file again or set path to empty cos there's no point in storing a knackered value
end if

Good luck fella,

JB

 

[primagic]

Thanks mate

THought of another question,what code would I use to open the file up from access through a command button

 

[jrbarnett]

Either:
Application.FollowHyperlink forms!myform!MyControl

or look at the ShellExecute windows API function.

John

 

[stable]

Hello,

Thanks for this tip. I have saved the browsed file path to a hyperlink field in a table. When I click on this hyperlink field it does not take me to the same file at all. Instead it opens Window explorer to somewhere different on the network with an UNC path.

Am I missing something here?

Cheers
Stable

 

[primagic]

Was there a reason you wanted to save it as a hyperlink field?

Just add an open button in the details section of the form that displays the links to open the document attached using the following code:

Application.FollowHyperlink Forms!YourForm!YourField(that has the location of the document)

 

[stable]

Hi primagic,
Thanks for the tip.
The reason for saving it as a hyperlink field is that the field appears in a datasheet in a subform to a form. I wanted the user to see all records (and hyperlinks) in the subform and click on the corresponding hyperlink for any record.

I worked out that to add the hyperlink I needed to right click the hyperlink field and then "Edit the hyperlink" to browse to the required file. It was sometimes necessary to go up the folder tree several levels to ensure that the whole path appeared in the address text box.

cheers
Stable