Select File from Computer and Save to Shared Drive

[sgrimmer]

I am brand new to MS Access, but I have been so far successful in all that I've had to do through finding similar solutions online and modifying them for my purposes. But in this particular case, I'm having difficulties.





What I'm looking to do is have a command button on a form saying something like "Attach Image". When clicked, the button would launch the Windows file dialog, have the user select a file and click "Open"/"Attach"/"Select"/whatever.

After the user has selected a file, I would prefer to save the file to a new location (shared drive) instead of embedding it inside the database (due to size constraints) or saving the existing path to the user's computer. I would actually even like to automatically rename the file to match the primary key of the form, if possible (to prevent duplicate file names in the shared drive). And then the path to the file in the shared drive would be saved in a new field on the form.

And finally, I could simply insert an image into the form that would link to the field containing the path of the image file.



I've found several things online already that are similar to what I'm trying to do, but none that I can seem to amend enough to get me there. I would've imagined there would be some existing code that would be very similar to this, as this would be a preferred method for attaching and displaying images in a large database. If anyone has something similar that I can look at and work with, I would be very grateful. Or if anyone has suggestions for a more preferred method to accomplishing something similar, I would be open to that as well.

https://www.accessforums.net/import-...form-8192.html
https://www.accessforums.net/program...ess-40203.html
https://www.accessforums.net/program...tml#post196625

[ranman256]

Here is the code for the user to pick a file via file explorer...but
im not sure what you want to do afterwards...

usage:
txtBox = UserPick1File("c:\folder\")

'NEW VERSION
Public Function UserPickFile( pvPath)
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String
dim fd as filedialog


set fd = Application.FileDialog(msoFileDialogFilePicker)  '<---- 'MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
With fd
    .AllowMultiSelect = False
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    .InitialFileName = "c:\"
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    ''.AllowMultiSelect = True
     
        If .show = 0 Then
           'There is a problem
           Exit Function
        End If
    
    'Save the first file selected
    UserPick1File = Trim(.SelectedItems(1))
End With
set fd = nothing
End Function

[sgrimmer]

Thanks for the reply!

As for what I was hoping to do next:

The primary key for the table my form is linked to is called GlassID. I also have another field in that table called GlassPic. After a user selects a file, I'm hoping to save a copy of the picture file to a different location on a shared drive (e.g. S:\Folder\SubFolder) with a new filename that matches the GlassID field (e.g. 12345.jpg). Then, I want to store the path to the image in the GlassPic field (e.g. S:\Folder\SubFolder\12345.jpg).

Once all of that has been done, I can simply insert an image control into the form and set its control source to GlassPic. This would then display the linked image within the form.

Does that make any sense? Or am I talking crazy?

Form is called f_AddComponent
Table is called t_Glass

[June7]

Makes sense to me. Try using FileCopy() function to copy the selected file. However, might run into issues with network permissions.

[sgrimmer]

This project took a backseat with other things I had going on, but I did eventually find a solution and would like to post a solution for anyone that comes across this in the future.

I wanted to be able to attach multiple pictures to a single main table record, so I created a new table to hold the file paths (along with their associated ID from the main table) and established a one-to-many relationship between the main table and new table. I also put the image and its control (the file path) inside of a subform within my main form so that I could scroll through multiple images while keeping my main form consistent.


Step 1: Create a module

For the very new users to Access, this is done by clicking the "Create" ribbon and then the "Module" button.

In the module, copy in the entirety of the code found here: http://access.mvps.org/access/api/api0001.htm

Be sure to name the rename the module to something you can later call. I used "basCommDialog".


Step 2: Call the file picker and record the existing file path

Create a command button to launch the file picker. Within the Click event, copy the code below:

' Call file picker from basCommDialog standard module
Dim strFilter As String
Dim strPath As String


    ' Format file picker
    strFilter = ahtAddFilterItem(strFilter, _
        "All Pictures(*.emf; *.wmf; *.jpg; *.jpeg; *.jfif; *.jpe; *.png; *.bmp; *.dib; *.rle; *.gif; *.emz; *.wmz; *.pcz; *.tif; *.tiff; *.cgm; *.eps; *.pct; *.pict; *.wpg) ", _
        "*.emf; *.wmf; *.jpg; *.jpeg; *.jfif; *.jpe; *.png; *.bmp; *.dib; *.rle; *.gif; *.emz; *.wmz; *.pcz; *.tif; *.tiff; *.cgm; *.eps; *.pct; *.pict; *.wpg")
    strFilter = ahtAddFilterItem(strFilter, _
        "All Files (*.*)", _
        "*.*")


    ' Save existing file path and name
    strPath = ahtCommonFileOpenSave(Filter:=strFilter, _
        OpenFile:=True, _
        DialogTitle:="Select a file", _
        Flags:=ahtOFN_HIDEREADONLY)

There will be more code included before the End Sub; continue reading.

Step 3: Give the file a new name and set the file path

Now, I am very new to Access and programming, and I was looking to find a way to ensure that every file I saved to a shared folder would have a unique name. You can absolutely modify this section so that the file name is different (my code might be clunky; I honestly don't know). I decided to record the current time and date as a numerical string and use that as the new file name. (This is still within the Click event).

' Note current time and date
Dim curTimeDate As String
curTimeDate = Now


' Format time and date as numerical string


    'Start by breaking it into numerical values
    Dim monthTimeDate As String
    Dim dayTimeDate As String
    Dim yearTimeDate As String
    Dim hourTimeDate As String
    Dim minTimeDate As String
    Dim secTimeDate As String


    monthTimeDate = Format(curTimeDate, "mm")
    dayTimeDate = Format(curTimeDate, "dd")
    yearTimeDate = Format(curTimeDate, "yyyy")
    hourTimeDate = Format(curTimeDate, "hh")
    minTimeDate = Format(curTimeDate, "nn")
    secTimeDate = Format(curTimeDate, "ss")
    
    ' Combine numerical values into one long string of numbers
    Dim numTimeDate As String
    numTimeDate = [monthTimeDate] & [dayTimeDate] & [yearTimeDate] & "_" & [hourTimeDate] & [minTimeDate] & [secTimeDate]


' Set new file path
Dim strNewPath As String
strNewPath = "H:\Folder\Subfolder\" & numTimeDate & ".jpg"

The folder used in the file path needs to already exist and be accessible.

Step 4: Copy the existing file to the new file path and name

(This is still within the Click event)

' Copy file to new file path
' FileCopy OLD PATH & NAME, NEW PATH & NAME
FileCopy strPath, strNewPath

Step 5: Create a new record in the table holding the file paths

I had to temporarily turn off the warnings in this code so that I would stop getting a message asking if I was sure I wanted to create a new record in a table (This isn't a requirement, but I thought it aided in the user experience). After the new file path is saved in the table, I launched a form with a simple message that said "Image saved!" This could also be done with a text box or skipped entirely. (This is still within the Click event)

' Create new record in t_ImagePaths table
' and record MainFormID and new file path


' Turn off Warnings
On Error GoTo ErrorHandler
    DoCmd.SetWarnings False


Dim NewPic As String
NewPic = "INSERT INTO t_ImagePaths(MainTableID, ImagePath)" _
    & "VALUES(Forms!f_MainForm.MainFormID, '" & strNewPath & "' );"
DoCmd.RunSQL NewPic


' Launch message box form f_ImageSaved
DoCmd.OpenForm "f_ImageSaved", acNormal, "", "", , acDialog


' Turn warnings back on
ExitHandler:
    DoCmd.SetWarnings True
    Exit Sub
ErrorHandler:
    MsgBox Err.Number & Chr(9) & Err.Description
    Resume ExitHandler

Moving Forward

I am still working on a way to reload the main form to include the now added images. But I hope this helps someone who comes across it in the future! This has been a huge learning experience for me. Good luck!