File Attachement Field (1 Viewer)

[MattBaldry]

Hi all,

I have been looking but cannot find a work thread for what I am after. Nice and simple, be able to display the list of filenames in an attachment field on a form.

I can get it to display the first attachment, but it won't show anything after that. Hoping that it is something simple.

And on a side note, is it possible to have a "drag and drop" feature to an attachment field? Like just dragging an email from outlook onto the attachment field and it uploads?

~Matt

 

[MajP]

You should be able to simply do that by pulling down the file name.

Not sure how you want to have the form look but it shows all file names for every record.

 

[theDBguy]

Don't you get a list by simply double clicking on the attachment field?

Sent from phone...

 

[MattBaldry]

Don't you get a list by simply double clicking on the attachment field?

Sent from phone...

Yes, this does work. But I wanted it just shown on the form itself.

 

[MattBaldry]

You should be able to simply do that by pulling down the file name.
View attachment 121183

Not sure how you want to have the form look but it shows all file names for every record.

Good shout. I shall give this a go.

Update: Tried and this works fine. So simple. I feel dumb for not trying this first.

Now, can I open the file from here now on a double click?

~Matt

 

[theDBguy]

Yes, this does work. But I wanted it just shown on the form itself.

You could probably use a subform for it or use something like this.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

Sent from phone...

 

[MattBaldry]

You could probably use a subform for it or use something like this.

SimpleCSV() v2.0

A few years ago, I wrote a simple function called SimpleCSV() to concatenate records and posted it on my website. One main drawback of thi...

thedbguy.blogspot.com

Sent from phone...

Thanks DBguy. The solution offered by MajP worked and was so simple. Just created a list box and based it on a query of that field showing just that part of the attachment field and it worked perfectly.

~Matt

 

[theDBguy]

Thanks DBguy. The solution offered by MajP worked and was so simple. Just created a list box and based it on a query of that field showing just that part of the attachment field and it worked perfectly.

~Matt

Glad to hear you got it sorted out. Good luck with your project.

 

[MajP]

As for drag and drop, Chatty says it is possible using the attachment control. I did not find that to work. So I extended the code from the linked thread that uses an Active X control (that supports drag and drop). I used a listview, but there are others.

Almost working Drag & Drop

Hi everyone, A have a somewhat working drag and drop solution, just needs some fine tuning. I have a combo box and a table behind it that has a Hyperlink. If its just a combo box, its not working (you need a table with a Hyperlink table field), but since i need other functions, like "Save as"...

www.access-programmers.co.uk

Then wrote some code to save to the attachment field

Option Compare Database
Option Explicit
Private WithEvents LV As ListView
Private Sub Form_Load()
  Set LV = Me.lvFiles.Object
End Sub
Private Sub LV_OLEDragDrop(Data As MSComctlLib.DataObject, Effect As Long, Button As Integer, Shift As Integer, x As Single, y As Single)
  Dim i As Integer
  Dim Path As String
  If Data.Files.Count > 0 Then MsgBox "Processing " & Data.Files.Count & " files"
  For i = 1 To Data.Files.Count
    InsertFileIntoAttachment Me.DocID, Data.Files.Item(i)
  Next i
 
End Sub
Public Sub ProcessFiles(DocPath As String)
  Dim strSql As String
  strSql = "Insert into tblDocuments (docPath) values ('" & DocPath & "')"
  CurrentDb.Execute strSql
End Sub
Sub InsertFileIntoAttachment(DocID As Long, FilePath As String)

    Dim db As DAO.Database
    Dim rsTable As DAO.Recordset
    Dim rsAttachment As DAO.Recordset2
    Dim strFilePath As String
    Dim YourRecordID As Long ' Example for finding a specific record

    ' Set the path to the file you want to attach
    strFilePath = FilePath ' Adjust this path and filename
    YourRecordID = DocID ' Replace with the actual ID of the record you want to modify

    On Error GoTo ErrorHandler

    ' Set DAO objects
    Set db = CurrentDb
    Set rsTable = db.OpenRecordset("tblDocuments", dbOpenDynaset) ' Replace YourTableName

    ' Find the specific record
    rsTable.FindFirst "DocID = " & DocID ' Replace IDFieldName

    If Not rsTable.NoMatch Then
        rsTable.Edit ' Enable editing of the current record

        ' Set the attachment field recordset
        Set rsAttachment = rsTable.Fields("ATT").Value ' Replace YourAttachmentFieldName

        ' Add the new attachment
        rsAttachment.AddNew
        rsAttachment.Fields("FileData").LoadFromFile strFilePath
        rsAttachment.Update

        rsTable.Update ' Save changes to the main record
        MsgBox "Added Attachement " & FilePath
        
    Else
        MsgBox "Record with ID " & YourRecordID & " not found.", vbExclamation
    End If

ExitSub:
    ' Clean up objects
    If Not rsAttachment Is Nothing Then rsAttachment.Close
    If Not rsTable Is Nothing Then rsTable.Close
    Set rsAttachment = Nothing
    Set rsTable = Nothing
    Set db = Nothing
    Exit Sub

ErrorHandler:
    If Not Err.Number = 3420 Then MsgBox "An error occurred: " & Err.Number & " " & Err.Description, vbCritical
   ' Resume ExitSub

End Sub

Works with multiple files at a time.

 

[MajP]

FYI. One form demos saving some data to text fields and the seond form is for the attachment fields. Make sure to open the attachment demo.