Listbox Double Click Event

[mayhem11]

I have an unbound listbox that has a few items listed in it from a table. The user can use the filters in the header to narrow down what they want to see displayed within this listbox. Once they see what they want, if there is an attachment, I want them to have the ability to double-click on the line item displayed and the attachment will open. I know that storing attachments in Access is not the best thing but this is all we have right now and we do not have a server. So, this means storing files on everyone's individual computers and they don't want that either. They want the attachments in the database until we migrate to a server environment. So, I am not entirely sure how to code my double click event to open the attachment if one exists on the line row.

 

[MajP]

Here is some code. However, an attachment field is a multivalue field, so there may be more than one attachment per record. This opens the first one only. What it does is that you pass in a recordset that includes the attachment field, and the name of the attachment fields. Then it saves the attachment to disk and then opens the attachment.

Public Sub OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String)
    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String
    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
    Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
    strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
    If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill strFilePath ' delete the file.
     End If
    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.
End Sub

Not sure how your listbox is set up, but this is how it is called from a form. You may have to build a recordset differently

Private Sub cmdOpenAttach_Click()
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    OpenFirstAttachmentAsTempFile rs, "Attachments"
End Sub

What is missing is that everytime I open an attachment it gets saved to disk, and there is no easy way to delete it after you close. So I would write seperate code, when you close the form to delete all files from the temporary directory. If that is the case then I would use a different folder than the environs TEMP folder as my temporary directory.

Using an attachment is not too bad. Attachments do not bloat the database, but take about the same amount of room as the file size. So do not plan for more than 2GB of file data + database data + database objects.

 

[mayhem11]

So there is no way to just open an attachment and view in the screen and then close when done?

 

[MajP]

I have never found a way. You can use the native attachment control by double clicking on the attachment field. That does not require any coding. How that opens the attachment I am not sure if it also has to save to disk.