Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35

    Attachments

    Forgive me if this is the wrong section.

    I'm looking for some assistance in using an attachment control field on an unbound forum. I've searched far and wide online and can't seem to find a solution. I simply want the control to be able to read which record (ID field) it needs to be going to and when the user uploads an attachment, it goes to that specific record. I do NOT want to use the record <> buttons on the bottom to go through the records as it should be locked down to only the user seeing their specific fields they have access too.

    Is this possible?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    It's not a great idea to use attachements since a lot of them could fill the db to the limit.
    Instead, save the PATH to the file: \\server\folder\myFile.xls

    then you can use the code below to open ANY file in the correct application.

    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..
    usage:
    OpenNativeApp txtBox



    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

  3. #3
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    The problem is we work in a large environment and not everyone will have access to the various path. I'd like a native copy stored within the database that I can extract and move when I am able too. This way, users can upload their documents and move on and I will handle the backend work.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Attachment field is a type of multi-value field. Can use VBA to manage content. Review https://sourcedaddy.com/ms-access/wo...nt-fields.html

    If you will have a lot and Access approaches 2GB limit, might want to have a separate backend just for attachments.
    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.

  5. #5
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    Any way you could help me make that context into a command button? I can't seem to get the function to actually grab content from a file location.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Post your attempted code. Exactly how are you calling function?
    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
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    Quote Originally Posted by June7 View Post
    Post your attempted code. Exactly how are you calling function?
    I was hoping to put the information into an actual command button instead of having it call a function.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Could have the button Click event call the function.

    Otherwise, move the code into button Click event. Instead of passing strPath value in argument, code can reference field in form RecordSource. Probably eliminate the line that increments a count of the attachments.
    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
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    I've set it up within a module (the function) and have a button calling that function but it does nothing at all.

  10. #10
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    Here's my current code for the function:
    Code:
    Public Function LoadAttachments(strPath As String) As Long    Dim dbs As DAO.Database
        Dim rst As DAO.Recordset2
        Dim rsA As DAO.Recordset2
        Dim fld As DAO.Field2
        Dim strFile As String
    
    
        'Get the database, recordset, and attachment field
        Set dbs = CurrentDb
        Set rst = dbs.OpenRecordset("tblAttachments")
        Set fld = rst("Attachments")
    
    
        'Navigate through the table
        Do While Not rst.EOF
    
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
    
    
        'Load all attachments in the specified directory
        
        rst.Edit
        
    
    
            'Add a new attachment that matches the pattern.
            'Pass "" to match all files.
            rsA.AddNew
            rsA("FileData").LoadFromFile strPath
            rsA.Update
    
    
            'Increment the number of files added
            LoadAttachments = LoadAttachments + 1
            
            strFile = Dir
        
        rsA.Close
    
    
       
        'Next record
        
        Loop
    
    
        rst.Close
        dbs.Close
    
    
        Set fld = Nothing
        Set rsA = Nothing
        Set rst = Nothing
        Set dbs = Nothing
    End Function
    How do I have it understand what ID to put the attachment under in the record so it puts the attachment in the right spot, not the first record or all the records?

  11. #11
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    The problem is we work in a large environment and not everyone will have access to the various path. I'd like a native copy stored within the database that I can extract and move when I am able too
    suggest create an 'attachments' folder in the same directory as your back end and copy the files to there, then store the path as previously suggested

  12. #12
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    Well I have the program now saving the attachments to the database. Only issue is, it saves the one same attachment to every single field within the Attachments table

  13. #13
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    Okay, I got the attachment to work and modified it. Now, I have a listbox on a form that I want the user to be able to double click and it load that specific attachment. This listbox has the .name of the attachment field gathered from a query. I gathered the code to show the temp file from the following link: https://www.accessforums.net/showthread.php?t=23686

    The only issue is, if the record has two attachments, it only shows one of the attachments. Is there a way to change this?

  14. #14
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It only shows one of the attachments where? Are both files uploaded to the attachment field? Viewing them on form/report will require an Image control. On a form, the control will offer a little navigation bar. For a report, will probably have to use a query that expands the attachment field so each attachment is on its on row.

    As I said, this is a type of multi-value field. https://support.office.com/en-us/art...C-6DE9BEBBEC31
    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.

  15. #15
    DevState is offline Advanced Beginner
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2018
    Posts
    35
    So, if I have 3 attachments in the attachment field where ID=101, the list box shows the names of all three attachments. This is done by my query to the .filename of the attachment field. When I ran that code I linked too, if the user clicks on a field within the listbox, it pops up an Internet Explorer window and only shows one attachment. So even if I double clicked the third attachment, it still only shows the first attachment. I want them to be able to doublle click and view, in IE, any file they've attached.

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

Similar Threads

  1. Attachments
    By DevState in forum Forms
    Replies: 2
    Last Post: 01-14-2019, 08:06 AM
  2. Replies: 2
    Last Post: 01-29-2014, 03:19 PM
  3. attachments
    By chiefmsb in forum Forms
    Replies: 0
    Last Post: 07-12-2011, 07:32 AM
  4. attachments
    By chiefmsb in forum Access
    Replies: 0
    Last Post: 07-10-2011, 07:38 PM
  5. attachments
    By chiefmsb in forum Access
    Replies: 0
    Last Post: 07-08-2011, 03:56 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