How do I send multiple attachments from attachment field to separate attachment controls on a form?

[rollinshultz]

I am trying to accomplish something using the attachment controls on a form and an attachment field in a table. Each row in the attachment field will have a picture file and a pdf file with a more detailed product description. I have set up a product form to search products and display a list, but I also included a more info button to open another form with two unbound attachment controls which have as their row source the attachment field from the product table. The code in my button simply searches for the productID of the listbox selection and then opens the second form with the listbox selection in the where clause of the doCmd.openForm. When the second form opens both controls display the first attachment picture file of the current row. I have been trying to assign the second attachment control to the pdf file at the on_load event of the second form, but I can't seem to access the individual members of the attachment control. Those being attachmentField.FileData(1) and (2)


I have tried many different approaches to access that second file in the field and place it into the second control to no avail. I haven't tried a querydef and sql qry yet, but I have tried everything else days of research have revealed. Should I try a queryDef? Here is my code for the more info button on the product catalog form.

Private Sub btnMoreInfo_Click()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim row As Long
    Set db = CurrentDb
    Set rs = db.OpenRecordset("tblProducts", dbOpenSnapshot)


        If Me.lstSearchResults.ItemsSelected.Count > 0 Then
            
            ' This gets the product ID of the current selection of the listbox at column zero
            ' The value is used to set the current record and that is passed to the open form cmd
            row = Me.lstSearchResults.Column(0)
            rs.MoveFirst
            rs.FindFirst "ProductID=" & row
            ' This opens the new form at the desired record as read only as intended for an informational form
            DoCmd.OpenForm "frmCatalogDetails", acNormal, , "ProductID=" & Me.lstSearchResults.Column(0, Me.lstSearchResults.ItemsSelected), acFormReadOnly, acWindowNormal
            
        Else
            ' just to make sure a listbox item is selected if not end sub returns the user to the form
            MsgBox "You must select a product"
            Exit Sub
        End If
End Sub

The bit of code in my second form on_load event is this:

Private Sub Form_Load()
    
    Set db = CurrentDb
    
    Dim doc As DAO.Document
    ' Set doc = rs.Fields("image") ' image is the name of the attachment field/column
    
    ' Debug.Print ctlFeatures.CurrentAttachment
    
End Sub

Screen shots of forms: I would like the picture in control 1 and pdf in control 2

[June7]

Store the documents in separate fields or records or store them externally.

An attachment type field is a type of multi-value field. Multi-value fields are more difficult to work with. To treat each item in the field separately requires a query that expands the multi-value field to multiple records.

Lots of large embedded files will quickly use up Access 2GB size limit.

[rollinshultz]

I can understand the size limit although simple product images can be sized fairly small and a pdf describing extra info shouldn't be a problem for a small company or even a large company with a small product line. I'm only doing this to learn how it's done and that is why I am looking for the best possible solution. i want to develop a small but very complete Db from scratch to get the experience of creating a top of the line Template to use for my code toolbox and learning VBA more completely. I guess I am a little spoiled by the functionality of C# and SQL server.

If Access is going to provide a control of this type where there are members such as FileData, FileType and FileName then they should be available to VBA for manipulation. I am trying without success to iterate through the current attachment field by type and I can't understand why that would not be doable if I could only find the correct calls.

What is the attachment field? Is it an array, collection, mini recordset? How would one iterate through the filetypes looking for a particular ext. and then call the value for the position of that filetype. Why can't I use a call like attachmentControl.FileData(2) to access the second file in the field?

This is my latest attempt to put the data into the control and if I can't get this working, then I'll create a query and use qrydefs("query").sql to get what I want. I just think this should be doable in five lines of code or less directly.

    Set db = CurrentDb
    
    Set rsP = Me.Recordset
    Set rsC = rsP.Fields("Image").Value
    
    While Not rsC.EOF
        If rsC.Type = "pdf" Then ' This line throws a type mismatch error and when I hover over rsc,type it reads a value of 2
            Me.ctlFeatures.ControlSource = rsC.Fields("image").Value ' The second half of this line shows item not found in this collection
        rsC.MoveNext
        End If
    Wend

[June7]

They are available to VBA in a recordset object that expands the multi-value field to multiple records. This actually involves two recordsets. The first is the parent and the second is a child recordset that is built from the first.

Access is the only database software that has multi-value type fields.

Multi-value fields discussed in numerous threads. Search on "attachment multi value", and my username to narrow the results to 27 threads.