Code to Upload and Save Attachments External to Access 2007 (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
I have been doing a lot on this since I posted this morning... stuck on a small part.

I have a command button on my frm_visualinspectioninput that opens the form frm_visattachments. Here is the code for that form:
Code:
Private Sub cmdBrowseToFile_Click()
    Dim fDialog    As Object
    Dim varFile As Variant
    Dim strPath As String
    Dim partNum As String
 
   ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(1)
 
    With fDialog
      'Set the title of the dialog box. '
        .Title = "Select the File..."
        .InitialFileName = "C:\"
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
        If .Show = True Then
                varFile = .SelectedItems.Item(1)
                Me.txtPath = varFile
                Me.txtDescription = GetFilenameFromPath(Me.txtPath)
                'Me.txtTimestamp = FileDateTime(Me.txtPath)
        Else
                MsgBox "You clicked Cancel in the file dialog box.", vbOKOnly, "Select a File"
        End If
   End With
   
        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual"

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        partNum = DLookup("PartNumber", "tbl_parts", "ID = " & Forms!frm_visualinspectioninput!PartNumber)

        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual" & "\" & partNum

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
   
   Call CopyFile(Me.txtPath, "C:\adBEs\Links\" & GetFilenameFromPath(Me.txtPath))
   
End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
 
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1
    End If
End Function
What that is doing is creating a directory under Attachments based on the value in the form field PONumber in the frm_visualinspectioninput
Then creates a directory under the directory created by the PONumber field value called "Visual".
Then creates a directory under the "Visual" based on the value of the PartNumber field on the form frm_visualinspectioninput

Using fictitious values for posting purposes, it creates the path like this blablabla\P0BD154\77842\images chosen go here. This works... all directories are created correctly.

The part I am having trouble with is this line:
Code:
Call CopyFile(Me.txtPath, "C:\adBEs\Links\" & GetFilenameFromPath(Me.txtPath))
I do not know how to make that path work to put the attachment in that final directory and add the hyperlink to the tblLink.

There is a good reason why I create the directories the way I do which is to avoid all images going into one directory because people may reuse the same image name from time to time.

The end result seems like it needs a submit button to create the directories and then upload the file and create the links in the table.

I also changed my original table relationship from one-to-one between the AuditID of the tbl_auditdata(where the records are stored) and the ILinkID (where the links are stored) to a one-to-many between the AuditID and the IRecordID

After all that is figured out I do not know how your code allows me to pick more than one file at a time. They could have anywhere from 1 to 5 files to upload.
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Looks like you are moving right along! Good to hear. :D

The CopyFile() copies from the existing location, Me.txtPath, to the new location, C:\adBEs\Link\..., so you need to...

Code:
Call CopyFile("Files Existing Location", "Files New Location"))

That said, this code was not designed to grab more than one file at a time. You would need a loop for that.
 

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
Looks like you are moving right along! Good to hear. :D

The CopyFile() copies from the existing location, Me.txtPath, to the new location, C:\adBEs\Link\..., so you need to...

Code:
Call CopyFile("Files Existing Location", "Files New Location"))

That said, this code was not designed to grab more than one file at a time. You would need a loop for that.

The first part seems ok
Code:
Call CopyFile(Me.txtPath,
The next part is what I am having trouble with
Code:
"C:\adBEs\links\"
The last part doesn't come into play due to the second part
Code:
& GetFilenameFromPath(Me.txtPath))

So I am creating a directory under the "Attachments" directory that is using the value in the form field (PONumber) populated from the table (tbl_auditdata) via the query that is the forms recordsource
Code:
         strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
Then I am creating a directory under that using the name of (Visual)
Code:
        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual"

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
Then I am creating a directory under that using the value in the form field (PartNumber) populated from the table (tbl_auditdata) via the query that is the forms recordsource
Code:
        partNum = DLookup("PartNumber", "tbl_parts", "ID = " & Forms!frm_visualinspectioninput!PartNumber)

        strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_visualinspectioninput!PONumber & "\" & "Visual" & "\" & partNum

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

So at this point I do not know how to complete the second part of the CopyFile string of code the "Files new location"

I have tried this which throws a run-time error '-2147024809 (80070057)': sorry something went wrong
Code:
Call CopyFile(Me.txtPath, strPath & GetFilenameFromPath(Me.txtPath))
I have tried putting in the full path from the last strPath string but this tells me a compile error Expected list seperator or ) message
Code:
Call CopyFile(Me.txtPath, "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_labtestinput!PONumber & "\" & "Visual" & "\" & partNum", & GetFilenameFromPath(Me.txtPath))

I do not know how to get that last directory path recognized in the new file location part of the CopyFile code string

If anyone could help me get that code correct I would greatly appreciate it.
 

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
It appears it was the actual CopyFile part... it needed to be FileCopy.

Cleaned up the code a bit more and noticed that it is storing the wrong link in the table. It is storing the link to the original file location rather than the new file location.

Working on that and trying to make it so multiple files can be chosen.
 

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Yep, day job again so came for a quick read and not sure what I am supposed to answering. Can you help me out when I return for my next quick read? I just need to know what parts you are still hung up on.
 

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
Not hung up on the CopyFile part anymore

Actually making good progress on the rest of the stuff I mentioned. I have our in-house code master helping me right now and the code has changed a lot.

I will post the solution when it is done so anyone else looking for this can use your or mine or a combination of to help their project(s)
 

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Happy to hear! Got tied up so happy you had a back-up plan! :D
 

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Oops, will have to fix that! Thanks for the heads up! :D
 

AlphaMike

New member
Local time
Tomorrow, 00:30
Joined
Oct 23, 2018
Messages
9
So... I've got that to work (party!!)

but... how do I "start using" the exported file instead??
 

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Not sure I understand the question, please elaborate...
 

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
Here is the final code for the attachments form. I made a command button on the form (which is in tab control) that opens the attachments form.

remember that my database is used across a WAN so the strPath would be different for you on a LAN

Button code:
Code:
Private Sub cmdLabAttachments_Click()
    DoCmd.OpenForm "frm_labattachments", , , , , acDialog
End Sub

Attachment form code:
Code:
Option Compare Database
Option Explicit
Dim RecordID As Integer

Private Sub cmdBrowseToFile_Click()
    Dim fDialog As Object
    Dim varFile As Variant
    Dim savePath As String
    
 
   ' Set up the File Dialog.
    Set fDialog = Application.FileDialog(1)
 
    With fDialog
      'Set the title of the dialog box. '
        .Title = "Select the File..."
 
      ' Show the dialog box. If the .Show method returns True, the
      ' user picked at least one file. If the .Show method returns
      ' False, the user clicked Cancel.
        If .Show = True Then
            For Each varFile In .SelectedItems
                savePath = GetDirectory() & "\" & GetFilenameFromPath(varFile)
                lstFiles.AddItem 0 & ";" & RecordID & ";" & varFile & ";" & savePath & ";" & GetFilenameFromPath(varFile)
            Next
        Else
            MsgBox "You clicked Cancel in the file dialog box.", vbOKOnly, "Select a File"
            Exit Sub
        End If
   End With
End Sub

Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'
 
    If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
        GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
    End If
End Function

Function GetDirectory() As String
    Dim strPath As String
    Dim partNum As String
    strPath = "\\aa.com\afi-dfs\arcadia\Dept\International\Engineering - JA\QC Lab\Electrical Audit Database BE\Attachments\" & Forms!frm_home.Lab_Test_Input_Form.Form.PONumber

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        strPath = strPath & "\" & "Lab"

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If

        partNum = DLookup("PartNumber", "tbl_parts", "ID = " & Forms!frm_home.Lab_Test_Input_Form.Form.PartNumber)

        strPath = strPath & "\" & partNum

    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
    
    GetDirectory = strPath
End Function

Private Sub cmdSubmit_Click()
    Dim dbs As DAO.Database
    Dim strPath As String
    Dim varFile As Variant
    Dim i As Integer
    
    Set dbs = CurrentDb
    For i = 0 To lstFiles.ListCount - 1
        If lstFiles.Column(0, i) = 0 Then
            FileCopy lstFiles.Column(2, i), lstFiles.Column(3, i)
            dbs.Execute "INSERT INTO tblLinks (IRecordID, IPath, IDescription, Iissue) VALUES (" & lstFiles.Column(1, i) & ", '" & lstFiles.Column(3, i) & "', '" & lstFiles.Column(4, i) & "', '" & txtIssue.Value & "');", dbFailOnError
        End If
    Next i
    
    txtIssue.Value = ""
    
    While Not lstFiles.ListCount = 0
        lstFiles.RemoveItem 0
    Wend
    
End Sub

Private Sub Form_Load()
    RecordID = Forms!frm_home.Lab_Test_Input_Form.Form.AuditID
End Sub


Private Sub lstFiles_DblClick(Cancel As Integer)
    lstFiles.RemoveItem lstFiles.ItemsSelected(0)
End Sub

It pulls information from a couple of fields from the form it was opened from for creating the directories
 

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Great! So it looks like you are all set. And thanks for sharing it's bound to help someone else. :D
 

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
For visual reference...

On the tab on my main form you click the attachments button and the code knows the current record showing on that page


The attachments form opens and you can browse for your file and then add an explanation of the file. Once you choose a file and it is displayed in the top field... you can double click that and it will delete if you so choose.


You can pull a report on the attachments


You can click the preview button and pull up the larger image for review
 

Attachments

  • Capture.JPG
    Capture.JPG
    44.5 KB · Views: 441
  • Capture1.JPG
    Capture1.JPG
    28.1 KB · Views: 430
  • Capture2.JPG
    Capture2.JPG
    69.4 KB · Views: 426
  • Capture3.JPG
    Capture3.JPG
    51.9 KB · Views: 440

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
I forgot to mention that the attachments form will refresh every time you click the submit button and you can continue to add more files one at a time... or.... if you have multiple pictures you want to add with the same explanation you can either keep clicking the browse for file button or select multiple files from windows explorer and it will create multiple entries in the tblLinks
 

AlphaMike

New member
Local time
Tomorrow, 00:30
Joined
Oct 23, 2018
Messages
9
Not sure I understand the question, please elaborate...

At work, each person has their computer access. That means we will each have our files in something like

\\location\users$\username\

There are shared drives for common documents.

My goal is that each person can add a picture from their "My pictures" or "my Downloads" into the database and, after it has been moved to the Shared drive (g:\AllEmployees\DestinationFolder\), the db would "fecth/read" the pictures from there, otherwise there'll be a permission error as we do not have access to other people's files in our sessions.

So, after the "FileCopy" line, I need to be able to have Access read from the new file (copy).


(also, I'd love to have the date/time added to the beginning of the copied file).
 
Last edited:

GinaWhipp

AWF VIP
Local time
Today, 19:30
Joined
Jun 21, 2011
Messages
5,901
Well, if I'm getting this right, what you need is to separate the two, FileCopy() moves the file but then the line that puts the path into the database needs to put the where it is going to be not where is copied it from.
 

psyc0tic1

Access Moron
Local time
Today, 18:30
Joined
Jul 10, 2017
Messages
360
but then the line that puts the path into the database needs to put the where it is going to be not where is copied it from.

That has been worked out in the code I posted above above.

@AlphaMike... The code I posted above will do all of that. You will need to change the directory creation code to meet your needs and change it from a WAN address to a LAN address (g:\AllEmployees\DestinationFolder\). From the code above it is creating directories based off of a destination directory called "Attachments", and then creating other directories based off of information on the form that was open when clicking the button to open the attachments form... you will need to change that information to suit your needs.

It uploads the files... creates the links and you are ready to access the pictures through a report or a form from there using a query.
 

AlphaMike

New member
Local time
Tomorrow, 00:30
Joined
Oct 23, 2018
Messages
9
It uploads the files... creates the links and you are ready to access the pictures through a report or a form from there using a query.

Yeah... I'll have to play with my search queries after that...

Thanks! I'll test it now!
 

Users who are viewing this thread

Top Bottom