Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581

    Module to add attachments


    I have a database that I need to add TIF files to. I want to build a module for it so that the user can save a file to a folder and have that file named in a certain format, and then have it linked to the form so that it can be seen when needed. I'm guessing that this will be a lot of work. If anyone knows of an article that might already be written, that would probably be easiest.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    no, not a lot of work at all.
    i have a form with a field that stores the path to the document.
    dbl-click event opens the file explorer for the user to pick a file.
    the file path is stored.

    the user can click a View button that opens the file.

    Paste this code into a module, and it will open ANY file in its native application.
    In a form put the field and a button to open it.

    if the file is myFile.pdf, will open it in acrobat
    if the file is myFile.doc, it will open the doc in Word
    if its just a file path, it will open in file explorer.
    etc..


    Code:
    'Attribute VB_Name = "modNativeApp"
    'Option Compare Database
    Option Explicit
    
    Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hwnd As Long, ByVal lpszOp As String, ByVal lpszFile As String, ByVal lpszParams As String, ByVal lpszDir As String, ByVal FsShowCmd As Long) As Long
    
    Private Declare Function GetDesktopWindow Lib "user32" () As Long
    Const SW_SHOWNORMAL = 1
    Const SE_ERR_FNF = 2&
    Const SE_ERR_PNF = 3&
    Const SE_ERR_ACCESSDENIED = 5&
    Const SE_ERR_OOM = 8&
    Const SE_ERR_DLLNOTFOUND = 32&
    Const SE_ERR_SHARE = 26&
    Const SE_ERR_ASSOCINCOMPLETE = 27&
    Const SE_ERR_DDETIMEOUT = 28&
    Const SE_ERR_DDEFAIL = 29&
    Const SE_ERR_DDEBUSY = 30&
    Const SE_ERR_NOASSOC = 31&
    Const ERROR_BAD_FORMAT = 11&
    
    
    Public Sub OpenNativeApp(ByVal psDocName As String)
    Dim r As Long, msg As String
    
    r = StartDoc(psDocName)
    If r <= 32 Then
        'There was an error
        Select Case r
            Case SE_ERR_FNF
                msg = "File not found"
            Case SE_ERR_PNF
                msg = "Path not found"
            Case SE_ERR_ACCESSDENIED
                msg = "Access denied"
            Case SE_ERR_OOM
                msg = "Out of memory"
            Case SE_ERR_DLLNOTFOUND
                msg = "DLL not found"
            Case SE_ERR_SHARE
                msg = "A sharing violation occurred"
            Case SE_ERR_ASSOCINCOMPLETE
                msg = "Incomplete or invalid file association"
            Case SE_ERR_DDETIMEOUT
                msg = "DDE Time out"
            Case SE_ERR_DDEFAIL
                msg = "DDE transaction failed"
            Case SE_ERR_DDEBUSY
                msg = "DDE busy"
            Case SE_ERR_NOASSOC
                msg = "No association for file extension"
            Case ERROR_BAD_FORMAT
                msg = "Invalid EXE file or error in EXE image"
            Case Else
                msg = "Unknown error"
        End Select
    '    MsgBox msg
    End If
    End Sub
    
    
    Private Function StartDoc(psDocName As String) As Long
    Dim Scr_hDC As Long
    
    Scr_hDC = GetDesktopWindow()
    StartDoc = ShellExecute(Scr_hDC, "Open", psDocName, "", "C:\", SW_SHOWNORMAL)
    End Function

    usage:

    Code:
    sub btnOpenFile_click()
      OpenNativeApp ME.txtBox
    end sub
    put this code into a MODULE for user to pick the file
    usage:
    txtFile = UserPick1File("c:\folder")


    Code:
    Public Function UserPick1File(Optional pvPath)
    Dim strTable As String
    Dim strFilePath As String
    Dim sDialog As String, sDecr  As String, sExt As String
    
    
    '===================
    'YOU MUST ADD REFERENCE : Microsoft Office xx.0 Object Library, in vbe menu, TOOLS, REFERENCES
    '===================
    
    With Application.FileDialog(msoFileDialogFilePicker)     'msoFileDialogSaveAs
        .AllowMultiSelect = False
        .Title = "Locate a file to Import"
        .ButtonName = "Import"
        .Filters.Clear
         '.Filters.Add "CSV Files", "*.csv"
         '.Filters.Add "Excel Files", "*.xls;*.xlsx"
        .Filters.Add "All Files", "*.*"
        .InitialFileName = pvPath
        .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
        
            If .Show = 0 Then
               'There is a problem
               Exit Function
            End If
        
        'Save the first file selected
        UserPick1File = Trim(.SelectedItems(1))
    End With
    End Function

  3. #3
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Wow. I'm going to get on that as soon as I can. I wish I were more adapt to code, but really want to look it over to see what you did. I will keep you up on my progress. Thanks.

  4. #4
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I was trying to understand what you did. I'm hoping that I'll be able to do stuff like that myself. This is what I did:
    I already have a Global Module that I have public functions. I added the public function there. I added a module called Attach Module which I put: txtFile = UserPick1File("c:\folder")
    I put the button code in the button. What do I do with the first code you put up? Create another module?

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    txtFile = UserPick1File("c:\folder")
    goes in some event code behind a form to save value to record in a field named txtFile, perhaps another button click "Get File"
    Substitute "c:\folder" for whatever path you want to default to.

    Button click is correct in form module.

    Rest of code goes in a general module, new or existing as you prefer, so procedures can be called from 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.

  6. #6
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Still working on it. I got an error.
    Click image for larger version. 

Name:	Error.jpg 
Views:	20 
Size:	109.4 KB 
ID:	32129

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What is the name of your textbox? Use your actual textbox name in place of txtBox.
    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.

  8. #8
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I do not have a text box. What is the text box for?

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    One procedure locates the file path/name that needs to be saved to record.

    Other procedure opens the file associated with the path/name saved in record.

    Buttons on form have their own procedures that call the above referenced procedures.

    Textbox on form would be bound to the field where the file path/name is saved.

    How would you expect to open the file if you cannot somehow retrieve the path/name from record?
    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.

  10. #10
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I thought about that after I sent the message. I added a text box and named it [txtAttachment]. I got this.
    Click image for larger version. 

Name:	error.jpg 
Views:	17 
Size:	86.6 KB 
ID:	32166

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    What is the error message?

    Is the form bound to table?

    Is the textbox bound to field of that table?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    Last edited by June7; 01-16-2018 at 02:16 PM.
    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
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    Yes. The text box is bound to the same table as the rest of the form. I made the text box short text and also hyperlink. Both gave the same error.
    Click image for larger version. 

Name:	Error.jpg 
Views:	13 
Size:	24.4 KB 
ID:	32180

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There is no value in the textbox. The function cannot handle Null.
    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.

  14. #14
    UT227 is offline Expert
    Windows 7 32bit Access 2013 32bit
    Join Date
    Feb 2016
    Posts
    581
    I thought the module would fill in the text box?

  15. #15
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Have to first run the procedure that finds the file and saves it into record.

    That would be a button with code like:

    txtFile = UserPick1File("c:\folder")


    Then later btnOpenFile can open the file.
    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.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 01-29-2014, 03:19 PM
  2. attachments
    By slimjen in forum Forms
    Replies: 4
    Last Post: 04-05-2013, 10:00 AM
  3. class module vs regular module
    By Madmax in forum Modules
    Replies: 1
    Last Post: 05-01-2012, 03:44 PM
  4. attachments
    By chiefmsb in forum Forms
    Replies: 3
    Last Post: 08-08-2011, 11:25 AM
  5. Replies: 4
    Last Post: 05-16-2011, 04:58 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