Results 1 to 12 of 12
  1. #1
    ibcormac is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Central Cali
    Posts
    8

    Filepath for attachments


    Hope this is the correct subforum. New to this site. I have some limited experience with programming in various languages including MSAccess (VBA) but have never brought a project to fruition. I am running Windows 7 and MS Access 2010. I am creating a fairly simple contact management database for my Church which includes pictures in an attachment field. When I double-click on the attachment field it opens a Windows Explorer box where I can navigate to the file location and select the correct picture to attach. It defaults to the "My Documents" folder and the picutes are stored like maybe a thousand miles away. I would like to find a way to specify the default file folder location so the user does not have to navigate there each time. Searched the forums but unable to find anyh relevant threads. I appreciate any help you can provide.

  2. #2
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    Use .InitialFileName method of dialog object, for example fDialog.InitialFileName = CurrentProject.Path & "\Pictures"

  3. #3
    ibcormac is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Central Cali
    Posts
    8
    Thanks a lot. Will give it a try.

  4. #4
    ibcormac is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Central Cali
    Posts
    8
    Cyanidem
    Thanks a lot! That worked! Took a while to figure out. Kept getting errors until I referenced the MS Office 14 Object Library. The file picker dialog box now opens in the correct folder. Yeah! But I have to select the .jpg file twice in order for it to attach. The first time thru I get a msg box stating the filepath including the file name. After I select ok it opens the attachment dialog box and I have to go thru the attachment process. Do you know a way to get the selected file to attach without having to go thru the second part of that process?

  5. #5
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    It should work at first time, you've got probably something strange in your code. Can you post it here?

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Easier to store files externally. Also, embedded files use up Access file size limit. But if the database will always be small enough regardless of attachments, should be okay. Is this a split database?

    Attachment field is a type of multi-value field. Can programmatically insert and extract data (in this case, files).

    Review
    https://www.accessforums.net/program...vba-21259.html
    https://www.accessforums.net/forms/a...98/index2.html
    https://www.accessforums.net/program...ord-43913.html

    Put code behind a button instead of clicking on the attachment control.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ibcormac is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Central Cali
    Posts
    8
    Here is my code - totally plagarised by the way. It is attached to the OnDblClick event of the attachment field that holds the jpg. Thanks a lot for the advice. I sure appreciate it. Nice to finally bring a project to fruition.
    Code:
    ====================================
    Private Sub attFamilyPhoto_DblClick(Cancel As Integer)
        'Declare a variable as a FileDialog object
        Dim fd As FileDialog
    
    
        'Create a FileDialog object as a File Picker dialog box.
        Set fd = Application.FileDialog(msoFileDialogFilePicker)
    
    
        'Declare a variable to contain the path
        'of each selected item. Even though the path is a String,
        'the variable must be a Variant because For Each...Next
        'routines only work with Variants and Objects.
        Dim vrtSelectedItem As Variant
    
    
        'Use a With...End With block to reference the FileDialog object.
        With fd
    
    
            'Set the initial path to the C:\ drive.
            .InitialFileName = "C:\Users\Cormac\Desktop\Church\Database\Pix_Family"
    
    
            'Use the Show method to display the File Picker dialog box and return the user's action.
            'If the user presses the action button...
            If .Show = -1 Then
    
    
                'Step through each string in the FileDialogSelectedItems collection.
                For Each vrtSelectedItem In .SelectedItems
    
    
                    'vrtSelectedItem is a String that contains the path of each selected item.
                    'You can use any file I/O functions that you want to work with this path.
                    'This example simply displays the path in a message box.
                    MsgBox "Selected item's path: " & vrtSelectedItem
    
    
                Next vrtSelectedItem
            'If the user presses Cancel...
            Else
            End If
        End With
    
    
        'Set the object variable to Nothing.
        Set fd = Nothing
    
    
    End Sub
    ===========================
    Last edited by June7; 01-05-2016 at 04:46 PM. Reason: add CODE tags

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    All the code does is open MsgBox displaying the selected item text. It doesn't save anything anywhere.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    ibcormac is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Central Cali
    Posts
    8
    June7. Thanx for the help. This is not a split database. Also, not concerned about Access file size limit. So I am embedding the pictures as attachments in the table containing data for each individual. Don't really know what other options I have at this point. There will probaby never be more than 500 or so records for individuals and estimating only about 75-80% of those will have pictures. Total number of records from all other tables will probably never exceed 3,000-4,000. So, pretty small database.

    I am still somewhat confused about your last response. I was thinking the code was just to drill down to the folder location where the pictures are stored. From there the user would select the picture they want thus embedding the picture in the record and would be saved after it was selected. Did not envision the code saving anything since the code can not determine what needs to be saved, the user needs to select the file first from the filepicker dialog box. Sounds like you are suggesting to pass the users file selection to the code and save it from there. If that's what your suggesting, I think that might be beyond my capabilities at this point.

  10. #10
    cyanidem's Avatar
    cyanidem is offline Competent Performer
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2015
    Location
    Consett, UK
    Posts
    270
    I missed the part saying it's attachment field. As June7 suggests it's better to store pictures externally and have only paths to them stored in table.
    Anyway - this code you pasted does almost nothing. It opens file picker dialog and after you select desired file it shows its path in MsgBox and that's it. That's why probably you need to do this twice, that second dialog is native to your attachment field. Idea is to use text field to store path, use this code you have already but instead of displaying it with msgbox - save the path to selected file in record.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Instead of clicking on attachment field, click on button that has code to open file picker dialog. This selected file path would be passed to another routine that programmatically saves that file as embedded object in attachment field. This is the only way you can avoid the attachment field dialog.

    Otherwise, the file dialog code accomplishes nothing.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    ibcormac is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2016
    Location
    Central Cali
    Posts
    8
    Thanks for the advice. I will try to figure out how to store files externally and refer to them as you suggested. I will let you know if I am successful. Thanks a lot.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 03-02-2015, 09:40 AM
  2. Replies: 2
    Last Post: 01-29-2014, 03:19 PM
  3. Image Control with Filepath as Source
    By tennislvr7 in forum Forms
    Replies: 1
    Last Post: 01-22-2014, 10:59 PM
  4. variable filepath for output file
    By Jaron in forum Programming
    Replies: 6
    Last Post: 07-31-2013, 06:26 PM
  5. Saving Multiple Images to Form using a filepath
    By Jinxedcookie in forum Forms
    Replies: 3
    Last Post: 09-26-2011, 12:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums