Photos.FileNames Attachments

[FrustratedAlso]

When you query Photo.name, you get a line for each photo name attached to a record



1 aunt sally a.jpg
1 aunt sally b.jpg
1 aunt sally c.jpg
2 Uncle Joe x.jpg
3 Cousin Bob l.jpg

What I would like is to have it appear like this

1 aunt sally a.jpg; b.jpg; c.jpg
2 Uncle Joe x.jpg
3 Cousin Bob L.jpg

Suggestions or Solutions ?

[June7]

That is an attachment field type?

Not sure if can be applied but review http://allenbrowne.com/func-concat.html

[FrustratedAlso]

Yes it is an attachment field type. The stumbling block might be the record type? I tested and the function is passing the right key to look up.

Here is what I came up with:

Public Function ConcatRelated(rNbr As String) As Variant
   On Error GoTo Err_Handler
   'Purpose: Generate a concatenated string of related records.
   'Return: String variant, or Null if no matches.
   'Arguments: strField = name of field to get results from and concatenate.
   ' strTable = name of a table or query.
   ' strWhere = WHERE clause to choose the right values.
   ' strOrderBy = ORDER BY clause, for sorting the values.
   ' strSeparator = characters to use between the concatenated values.
   'Notes: 1. Use square brackets around field/table names with spaces or odd characters.
   ' 2. strField can be a Multi-valued field (A2007 and later), but strOrderBy cannot.
   ' 3. Nulls are omitted, zero-length strings (ZLSs) are returned as ZLSs.
   ' 4. Returning more than 255 characters to a recordset triggers this Access bug:
   ' http://allenbrowne.com/bug-16.html
   Dim rs As DAO.Recordset 'Related records
   Dim rsMV As DAO.Recordset 'Multi-valued field recordset
   Dim strSql As String 'SQL statement
   Dim strOut As String 'Output string to concatenate to.
   Dim lngLen As Long 'Length of string.
   Dim bIsMultiValue As Boolean 'Flag if strField is a multi-valued field.
   strSeparator = "; "
   'Initialize to Null
   ConcatRelated = Null

   'Build SQL string, and get the records.
   strSql = "SELECT Inventory.[Photos].[FileName] From Inventory where Inventory.[Number] = " & rNbr
   Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)
   'Determine if the requested field is multi-valued (Type is above 100.)
   bIsMultiValue = (rs(0).Type > 100)
    'Loop through the matching records
    Do While Not rs.EOF
        If bIsMultiValue Then
            'For multi-valued field, loop through the values
            Set rsMV = rs(0).Value
            Do While Not rsMV.EOF
               If Not IsNull(rsMV(0)) Then
                  strOut = strOut & rsMV(0) & strSeparator
               End If
               rsMV.MoveNext
            Loop
            Set rsMV = Nothing
        ElseIf Not IsNull(rs(0)) Then
           strOut = strOut & rs(0) & strSeparator
        End If
        rs.MoveNext
    Loop
    rs.Close
    'Return the string without the trailing separator.
    lngLen = Len(strOut) - Len(strSeparator)
    If lngLen > 0 Then
       ConcatRelated = Left(strOut, lngLen)
    Else
       ConcatRelated = rNbr ' put this in to see what I get
    End If
Exit_Handler:
    'Clean up
    Set rsMV = Nothing
    Set rs = Nothing
    Exit Function
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbExclamation, "ConcatRelated()"
    Resume Exit_Handler
End Function

[June7]

Use code tags when copy/paste code (I edited your post). If you use indentation in code it will be retained and makes it more readable.

What else happens? Does the data concatenate?

[FrustratedAlso]

No, I returns nulls, I don't think is goes through the records. Maybe the record type isn't ".> 100?

[June7]

I got this to work. Didn't matter if I pulled data from the table or query like you describe in first post. That it works with recordset based on table instead of query does surprise me.

Maybe doesn't matter but you declare rNbr as a string. Is it really a number?

Show the expression calling the function. Are you calling from a query or textbox?

[FrustratedAlso]

It is a string in the format of nnnnn-nn. Calling from a query on the Inventory table which also is the table containing the jpg's Expr1: ConcatRelated([Number])
Attachment 7037

[June7]

I am surprised no error message.

Value for text datatype field needs apostrophe delimiters:

... Inventory.[Number] = '" & rNbr & "'"

Date values use # delimiter.

The function call is correct.

[FrustratedAlso]

AWESOME!!!! Thanks sooooo Much