Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-07-2018, 07:20 PM   #1
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Attach Files to Access Form (Shared Path)

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.

helmerr is offline   Reply With Quote
Old 01-07-2018, 07:56 PM   #2
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,768
Thanks: 93
Thanked 1,726 Times in 1,597 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Attach Files to Access Form (Shared Path)

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
The Following User Says Thank You to The_Doc_Man For This Useful Post:
helmerr (01-09-2018)
Old 01-07-2018, 10:59 PM   #3
moke123
Me.Dirty=True
 
moke123's Avatar
 
Join Date: Jan 2013
Location: Massachusetts
Posts: 795
Thanks: 3
Thanked 238 Times in 224 Posts
moke123 will become famous soon enough
Re: Attach Files to Access Form (Shared Path)

Quote:
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.

moke123 is offline   Reply With Quote
Old 01-07-2018, 11:07 PM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 14,768
Thanks: 93
Thanked 1,726 Times in 1,597 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Attach Files to Access Form (Shared Path)

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.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 01-08-2018, 01:58 PM   #5
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Attach Files to Access Form (Shared Path)

Ok, so I'm stuck on the first part
Quote:
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?
helmerr is offline   Reply With Quote
Old 01-08-2018, 02:40 PM   #6
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,368
Thanks: 115
Thanked 3,111 Times in 2,829 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Attach Files to Access Form (Shared Path)

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
Attached Images
File Type: jpg ImageDisplayReport.jpg (102.5 KB, 272 views)
Attached Files
File Type: zip FolderImages v3.zip (136.2 KB, 52 views)
File Type: zip Display large image of Thumbnail in a form.zip (665.3 KB, 56 views)
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Common sense and a sense of humour are the same thing, moving at different speeds. (Clive James - RIP)

Last edited by isladogs; 01-08-2018 at 03:13 PM.
isladogs is offline   Reply With Quote
The Following User Says Thank You to isladogs For This Useful Post:
helmerr (01-09-2018)
Old 01-09-2018, 08:09 PM   #7
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Attach Files to Access Form (Shared Path)

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

helmerr is offline   Reply With Quote
Old 01-09-2018, 09:26 PM   #8
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,082 Times in 2,037 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Attach Files to Access Form (Shared Path)

You don't use "Set" when you assign a value to a variable.
Code:
destDir = "C:\apblotter\attachments\"
__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
The Following User Says Thank You to JHB For This Useful Post:
helmerr (01-14-2018)
Old 01-14-2018, 01:39 PM   #9
helmerr
Newly Registered User
 
Join Date: Nov 2013
Posts: 140
Thanks: 33
Thanked 0 Times in 0 Posts
helmerr is on a distinguished road
Re: Attach Files to Access Form (Shared Path)

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
helmerr is offline   Reply With Quote
Old 01-15-2018, 02:15 PM   #10
HiTechCoach
Newly Registered User
 
HiTechCoach's Avatar
 
Join Date: Mar 2006
Location: Oklahoma City, OK
Posts: 4,254
Thanks: 8
Thanked 156 Times in 144 Posts
HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough HiTechCoach is a jewel in the rough
Re: Attach Files to Access Form (Shared Path)

Something to think about ...

Quote:
Originally Posted by The_Doc_Man View Post
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.

__________________
Boyd Trimmell
Specializing in Accounting, CRM, and Business solutions
Microsoft Access MVP 2010-2015 , aka
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
HiTechCoach is offline   Reply With Quote
Reply

Tags
attachment , form , link , photos , server

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Browsing and selecting files to attach in email form stevekos07 Forms 4 03-05-2017 05:11 AM
Need text box in form for changing Linked files path aganesan99 Forms 2 09-03-2016 09:25 AM
Search and pull data from db stored in a shared path aaromic2000 Modules & VBA 5 12-30-2014 08:44 AM
Correct path to attach pdf files Groundrush Modules & VBA 7 09-02-2008 11:33 PM
Attach linked files DBL General 2 12-21-2004 02:10 PM




All times are GMT -8. The time now is 02:22 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World