Attach Files to Access Form (Shared Path) (1 Viewer)

helmerr

Registered User.
Local time
Today, 09:53
Joined
Nov 27, 2013
Messages
140
I'm having some trouble getting started. Here is what I'm trying to accomplish:

  • Allow users to upload multiple attachments (most likely photos) via an Access form.
  • Display a thumbnail on the form (for each attachment if possible).
  • Separately store the attachments in a folder hosted on our server, instead of Access. (The same server hosts the access database). I should note that this database will soon be converted to MSSQL.
The form actually a continuous form based on a query of a single table. Once the user saves the current record, the form is re-queried and displays a listing of all records in the table.
  • I also need thumbnails to be displayed here as well.

Thanks in advance.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:53
Joined
Feb 28, 2001
Messages
17,334
With Access, the most efficient way to store pictures (and for SQL Server as a remote back end, this is even more true) is to store the fully qualified file spec to the picture file, which will of course be expressed as a text string. Let Access worry about the graphics in the front end file.

For each image control on a form or report, there is a .Picture property. To put a picture in the image control, load that device:/path/name.type string (or the UNC version, \\server\path...path\name.type) to .Picture and do a form .Refresh to get it displayed.

For the upload you mentioned, you can create some VBA code that would be launched by a button-click. You can create a File System Object and ask it to trigger a file-picker dialog.

Once you have an FSO declared, you pick the file using the picker and then can use some simple string parsing to split out the path from the file name and type. Some of the methods of the FSO will do part or all of this parsing for you. (Hint: Do a Web lookup for File System Object and check out the stuff from the MSDN site.)

Next you generate the fully qualified file spec for the server folder based on the name you just parsed and the path to that hosted folder. In either case, the .Connect string for the back-end tables will contain the information about the host server.

Having both the original spec (from the picker) and the new spec (formed by some parsing) in string variables, you can easily use the FSO to launch a .FileCopy method to make a copy of the local file to the shared folder on your server.

Finally, you can store the new file spec as text in your DB.

Now, for this to be set up correctly in a form, you need the form to be bound and you can then have the FormCurrent routine load up whatever it needs to load up.

Note that you might have to look into condition formatting issues when using a continuous form. I have not tried something like you are trying so you might need to research that. Search this forum for "conditional formatting" as a way to find discussions on the method you would need to use. If I recall correctly, if you DON'T pay attention to the conditional formatting issues, then the last image you load is the only image you see because all continuous forms will use the last picture. But there are ways around that result, I just have not tried them because I never needed to do so.
 

moke123

AWF VIP
Local time
Today, 09:53
Joined
Jan 11, 2013
Messages
1,594
if you DON'T pay attention to the conditional formatting issues, then the last image you load is the only image you see because all continuous forms will use the last picture. But there are ways around that result, I just have not tried them because I never needed to do so.
I believe thats with unbound controls on a continuous form. As long as the image control is bound to a field in the forms record set it should display correctly.
 

The_Doc_Man

Immoderate Moderator, Former MVP, Retired SysAdmin
Staff member
Local time
Today, 08:53
Joined
Feb 28, 2001
Messages
17,334
As I had never tried this, I gave a caveat on the subject of images in continuous forms. Thanks for clarification, moke! But I also have never tried to bind an image control to an underlying record. I usually took special action in the OnCurrent event.
 

helmerr

Registered User.
Local time
Today, 09:53
Joined
Nov 27, 2013
Messages
140
Ok, so I'm stuck on the first part
For each image control on a form or report, there is a .Picture property. To put a picture in the image control, load that device:/path/name.type string (or the UNC version, \\server\path...path\name.type) to .Picture and do a form .Refresh to get it displayed.

For the upload you mentioned, you can create some VBA code that would be launched by a button-click. You can create a File System Object and ask it to trigger a file-picker dialog.
So far, I've added an image control to the form and set its Control Source as "Attachment1", a field in my table. I've searched for VBA code to allow a user to upload an attachment on click, but I don't believe I'm comprehending which is the correct one. i.e., I don't see the correlation on any that will let me choose the user to upload a file (picture/document) to a specified folder on the server. Furthermore, is there a way to auto-rename this file from the original name to prevent duplicates? Perhaps timestamp or add the record auto-number field?
 

isladogs

CID
Local time
Today, 14:53
Joined
Jan 14, 2017
Messages
13,616
You may find the attached useful

The database contains 3 forms showing different ways of displaying images in a form.
It also contains a report for the same purpose.

To use the database, click the Populate Image List button on the startup form and select a folder containing one or more images

The PDF explains how 2 of the methods work

The screenshot below shows the report



Hope this helps
 

Attachments

Last edited:

helmerr

Registered User.
Local time
Today, 09:53
Joined
Nov 27, 2013
Messages
140
Thanks for the attachments. I actually couldn't figure out how to apply them to my database, but I did find a youtube video that did mostly what I was trying to accomplish at https://youtu.be/pPpdKRUc21M.

At any rate, now I can get the image to populate the thumbnail and the original file path in the textbook beneath. I'm also able to copy this file to a destination path, but this is where I can use you all's help again..

1. I tried to DIM and SET my destination share path, but it errors out. So I just hardcoded it in the code (see code below).
2. When the file is copied, I changed the filename to equals "record ID + File1", but I need a way to append the original file extension.
3. I need to ensure the new file path and file name are copied back to the table for archival/reporting purposes. (Field name is File1 and set to Short Text.)

Here is my code so far.

Code:
Private Sub cmdBrowse1_Click()

    Dim f As Object
    Dim strfile As String
    Dim sourceDir As String
    Dim varItem As Variant
    'Dim destDir As String
    
    'Set destDir = "C:\apblotter\attachments\" '(This did not work)

    Set f = Application.FileDialog(3)
    f.allowMultiSelect = True
    If f.show Then
    For Each varItem In f.selectedItems
    strfile = Dir(varItem)

    sourceDir = Left(varItem, Len(varItem) - Len(strfile))
        '   MsgBox "Folder" & sourceDir & vbCrLf & _
        '   "File: " & strfile
    
    FilePath1 = sourceDir + strfile
    
    FileCopy Me.FilePath1.Value, "C:\apblotter\attachments\ & Me.ID & File1"
    
    Next
    End If
    Set f = Nothing

End Sub
 

JHB

Have been here a while
Local time
Today, 15:53
Joined
Jun 17, 2012
Messages
7,720
You don't use "Set" when you assign a value to a variable.
Code:
destDir = "C:\apblotter\attachments\"
 

helmerr

Registered User.
Local time
Today, 09:53
Joined
Nov 27, 2013
Messages
140
Hey guys - I think I've about got this complete.

I'm now able to copy files from the source to destination on a file share B:\Attachments. Files are renamed successfully and include the file extension.

The remaining items are as follows:
1. I'm trying to store the new location and filename in the table for future reference in a field called "FilePath1". For some reason, the original location and filename are populating in the database table, instead of the new. I'm using "newFile1" to store this variable.
2. When I click save, information for "FilePath1" is populating in the previous record. I had to move my original code above the "Work with Attachments" section to get most working for the current record.
3. I can't seem to get a thumbnail of the image to populate the "cmdBrowse1," having the On Click event for "Browse for File." I'm using an Image Object with a pre-set picture as this button.
4. Lastly, I'm sure there's a better way to code this; in the end, I will have 4 separate cmdBrowse buttons (cmdBrowse1, cmdBrowse2, cmdBrowse3, cmdBrowse4), all doing the same function, allowing for 4 separate attachments with preview. I'm open for any suggestions to clean this up if needed. Thanks!

Here is my code, which contains some other functions based on the the form:
Code:
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Browse for File
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Sub cmdBrowse1_Click()

    Dim f As Object
    Dim strfile1 As String
    Dim sourceDir1 As String
    Dim varItem1 As Variant

    ' Open Folder Picker
    Set f = Application.FileDialog(3)
    f.allowMultiSelect = False
    If f.show Then
    For Each varItem1 In f.selectedItems
    strfile1 = Dir(varItem1)

    ' Get source from selected file and set to sourceDir1
    sourceDir1 = Left(varItem1, Len(varItem1) - Len(strfile1))
    
    ' Set filePath1 to source folder & file name
    FilePath1 = sourceDir1 + strfile1


    Next
    End If
    Set f = Nothing
    
End Sub


''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' Save Button
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Private Sub btnSave_Click()


Dim db As Database
Dim rec As Recordset
Dim copyPath1 As String
Dim dotInpath1 As Integer
Dim fileExt1 As String
Dim extPath1 As String
Dim newFile1 As String
Dim fso1 As New Scripting.FileSystemObject

Set db = CurrentDb
Set rec = db.OpenRecordset("Select * from tblBlotter")

'test required fields
If IsNull(Me.txtEntryDate) _
        Or IsNull(Me.txtEntryTime) _
        Or IsNull(Me.opOType.Value) _
        Or IsNull(Me.cboBadge.Value) _
        Or IsNull(Me.txtIncidentDate.Value) _
        Or IsNull(Me.cboLocation.Value) _
        Or IsNull(Me.txtEmpID.Value) _
        Or IsNull(Me.txtIncidentTime.Value) Then
        

    MsgBox "Please fill in all required fields."
Else

'add records to table
rec.AddNew
rec("EntryDate") = Me.txtEntryDate
rec("EntryTime") = Me.txtEntryTime
rec("BadgeNum") = Me.cboBadge.Value
rec("EmpID") = Me.txtEmpID.Value
rec("IncidentDate") = Me.txtIncidentDate.Value
rec("IncidentTime") = Me.txtIncidentTime.Value
rec("Location") = Me.cboLocation.Value
rec("Response") = Me.txtResponse
rec("OType") = Me.opOType.Value

'Work with Attachments
    ' Set destDir to Attachments File Share
    destDir = "B:\Attachments\"
    
    ' Set copyPath1 to new subfolder record ID variable at destination
    copyPath1 = destDir & Me.ID & "\"

    ' Create Subfolder Record ID (specified above)
    fso1.CreateFolder (destDir & Me.ID)
    
    ' Get file extension and set to "fileExt1"
    extPath1 = Me.FilePath1.Value
    dotInpath1 = InStrRev(extPath1, ".")
    fileExt1 = Right(extPath1, Len(extPath1) - dotInpath1)

    ' Copy file from original file path1 to new subfolder path1 on B:\Attachments\%RecordID%\%FileName%
    FileCopy Me.FilePath1.Value, copyPath1 & Me.ID & "_1." & fileExt1
    
    ' Set new file path and name to "newfile1"
    newFile1 = copyPath1 & Me.ID & "_1." & fileExt1

'add records to table
rec("FilePath1") = newFile1
rec("FilePath2") = newFile2
rec("FilePath3") = newFile3
rec("FilePath4") = newFile4

rec.Update

MsgBox "Record saved."

'Requery
Me.Requery
'DoCmd.SetOrderBy "[ID]"
'Me.OrderByOn = True
'Me.OrderBy = "[EntryDate], [EntryTime] DESC"
'Me.Refresh

' Clear All Controls
   
    Me.txtEntryTime = Now()
    Me.EntryDate = Date
    Me.txtIncidentDate = Null
    Me.txtIncidentTime = Null
    Me.cboBadge.Value = Null
    Me.cboLocation.Value = Null
    Me.opOType = Null
    Me.txtEmpID = ""
    Me.txtResponse = ""
    Me.FilePath1 = ""
    Me.FilePath2 = ""
    Me.FilePath3 = ""
    Me.FilePath4 = ""

End If
End Sub
 

HiTechCoach

Registered User.
Local time
Today, 08:53
Joined
Mar 6, 2006
Messages
4,344
Something to think about ...

With Access, the most efficient way to store pictures (and for SQL Server as a remote back end, this is even more true)
While this is still true for the JET/ACE ( mdb/accdb) database formats, this is no longer true for all SQL and NoSQL database Servers. They are getting very efficient as storing files.

Note: If you are using the free version of some SQL Servers there is a size limit to the database.

I have done some testing with some SQL Servers. I was surprised to see how storing files in an SQL Server actually took less disk space and improved performance.

I have started storing a lot more stuff inside SQL Server databases.
 

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

Top Bottom