Copying a file to a specified folder and updating records with the new location?

[Hiro]

I'm currently working on a database to track and inventory documents in my organization similar to a library catalog, but with a digital archive as well. I'll need the ability to "attach" documents to records in the main inventory (though using links instead of storing it in the db) and have the documents live in a centrally accessible location on our shared server.

I will need to have this function work through a few different tables and in a few different iterations, but I'm trying to work on the simplest part first, which is attaching a thumbnail/preview of a given document to the record for that document.

I've got some VBA working well which opens the file picker and copies the file to a directory relative to the DB. It also creates a folder with each employee's ID number if it doesn't exist already (which it gets from the Windows log in) and adds "Copied_" to the front of the file.

Now I'm trying to get the VBA code to interact with the record. Presumably I need to tell the form button I'm using to launch the VBA code to pass the primary key of the record being displayed to VBA. Then, at the end of the VBA code I need to take that value and find the record again from VBA so I can update the MediaThumbnailLink in the original record.

Does this order of operations make sense? If so, how should I go about passing these values back and forth and writing the new link value?

Here's the VBA I'm using so far...

Option Compare Database

Public Sub GoCopyThumbnail()

    
    Dim fDialog As FileDialog
    Set fd = Application.FileDialog(msoFileDialogFilePicker)
    Dim varFile As Variant
    Dim LUser As String
    Dim LUPathname As String
    Dim sPath As String
    
    
    
    LUser = Environ("Username")
    LUPathname = Application.CurrentProject.path & "\Images\Thumbnails\" & LUser


        
    'Set up the file dialog'
    Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
    fd.InitialFileName = Application.CurrentProject.path
    With fDialog
    
        'Allow user to make mult selections'
        .AllowMultiSelect = False
        
        'Set title of dialog'
        .Title = "Please select an image"
        
        'Clear current filters, add our own'
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
        
        'Show dialog box'
        If .Show = True Then
        
        'Creates directory for LUser'
        If Len(Dir(LUPathname, vbDirectory)) = 0 Then
            MkDir LUPathname
        End If
        
        'Takes the image and copies it to generated path'
        FileCopy .SelectedItems(1), LUPathname & "\" & "Copied_" & Dir(Trim(.SelectedItems.Item(1)))
    
        sPath = LUPathname & "\" & "Copied_" & Dir(Trim(.SelectedItems.Item(1)))
        
        Else
        
        End If
    End With
End Sub

Any help is much appreciated! Thanks!

 

[gemma-the-husky]

the code that actually does the transfer looks ok

given this line

sPath = LUPathname & "\" & "Copied_" & Dir(Trim(.SelectedItems.Item(1)))

if the path is bound on the current form, then you can simply assign it.

myboundfield = spath

if not, then you need an update statement

currentdb.execute "update mytable set filepath = " & chr(34) & spath & chr(34) & " where mytableid = " & whatever

you need the chr(34) characters to manage the string correctly.you can update multiple fields, so you could also store the date/yime, and the person who did the update in the same statement

update table set newpath = whatever, datedone = whatever, doneby = whatever etc
note that dates, strings and numbers have slightly different formatting requirements


If you are not aware, given the filepath vaue in your table/form, you can directly open the linked document by an appropriate button click

application.followhyperlink filepath

 

[Hiro]

Alright, so that lets me write the path successfully (and it updates the image and everything woohoo!!!). Currently it's writing it to all records, though. So for the [Event Procedure] of my form's button, how do I tell it to pass MediaID (as let's say ToMediaID) from the form record being viewed into the VBA so I can specify to update the link where MediaID = ToMediaID?

I'm using this so far but obviously need a way to define and pass ToMediaID...

        CurrentDb.Execute "update Media_Inventory set MediaThumbnailLink = " & Chr(34) & sPath & Chr(34) & " where MediaID " & ToMediaID

Thanks for the help!