I was recently answering someone’s question on how to get the count of the number of attachments in a field.

Below are 2 simple functions to get the count:

'---------------------------------------------------------------------------------------
' Procedure : GetAttachmentCount
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Get a count of the number of attachments within an attachment field
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTable    : Table Name containing the attachment field
' sField    : Field Name of the attachment field
' sWHERE    : Criteria to filter on
'
' Usage:
' ~~~~~~
' GetAttachmentCount("tbl_Contacts", "ContactPics", "[ContactId]=127")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Feb-11                 Initial Release
'---------------------------------------------------------------------------------------
Function GetAttachmentCount(sTable As String, sField As String, sWHERE As String) As Long
    On Error GoTo Error_Handler
    'Should add validation to ensure the field is an attachment field
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim rsAtt           As DAO.Recordset
    Dim sSQL            As String

    Set db = DBEngine(0)(0)
    sSQL = "SELECT [" & sField & "] FROM [" & sTable & "] WHERE (" & sWHERE & ");"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    If rs.RecordCount <> 0 Then
        Set rsAtt = rs(sField).Value
        If rsAtt.RecordCount <> 0 Then
            rsAtt.MoveLast
            GetAttachmentCount = rsAtt.RecordCount
        End If
    End If

Error_Handler_Exit:
    On Error Resume Next
    Set rsAtt = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetAttachmentCount" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

'---------------------------------------------------------------------------------------
' Procedure : GetAttachmentCount2
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Get a count of the number of attachments within an attachment field
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sTable    : Table Name containing the attachment field
' sField    : Field Name of the attachment field
' sWHERE    : Criteria to filter on
'
' Usage:
' ~~~~~~
' GetAttachmentCount2("tbl_Contacts", "ContactPics", "[ContactId]=127")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2014-Feb-11                 Initial Release
'---------------------------------------------------------------------------------------
Function GetAttachmentCount2(sTable As String, sField As String, sWHERE As String) As Long
    On Error GoTo Error_Handler
    'Should add validation to ensure the field is an attachment field
    Dim db              As DAO.Database
    Dim rs              As DAO.Recordset
    Dim sSQL            As String

    Set db = DBEngine(0)(0)
    sSQL = "SELECT Count([" & sField & "].FileName) AS FileNameCount" & vbCrLf & _
           " FROM [" & sTable & "]" & vbCrLf & _
           " WHERE (" & sWHERE & ");"
    Set rs = db.OpenRecordset(sSQL, dbOpenSnapshot)
    If rs.RecordCount <> 0 Then
        rs.MoveLast
        GetAttachmentCount2 = rs![FileNameCount]
    End If

Error_Handler_Exit:
    On Error Resume Next
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Function

Error_Handler:
    MsgBox "The following error has occurred." & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: GetAttachmentCount2" & vbCrLf & _
           "Error Description: " & Err.Description, _
           vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Function

As you can see based on the GetAttachmentCount2 procedure, you can extract a count using a simple Count() function in a query. So actually no need for VBA in this case. So you can easily add a count to a query based on the AttachmentFieldName.FileName

NumberOfAttachments: Count(AttachmentFieldName.FileName)