locked
Using VBA Functions in Access Attachment Control RRS feed

  • Question

  • I used VBA Function in an Attachment's control source, but it dosn't work. Why can't I use VBA Function?

    Code:
    Function FindImg(ID As Integer) As Variant
        Dim mdb As Database
        Set mdb = CurrentDb
        Dim rec As Recordset
        Set rec = mdb.OpenRecordset("SELECT [Attachment] AS a FROM Images WHERE PersonID=" & ID, _
        dbOpenForwardOnly, dbDenyWrite)
        FindImg = rec.Fields("a").Value
        rec.Close
    End Function

    Wednesday, February 2, 2011 3:21 AM

Answers

  • You could use code like this:

    Dim rs As DAO.Recordset
    Dim rsFiles As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Customers", dbOpenSnapshot)
    Set rsFiles = rs.Fields("Attachments").Value
    While Not rsFiles.EOF
        Debug.Print rsFiles.Fields("FileName").Value
        rsFiles.MoveNext
    Wend
    rsFiles.Close
    Set rsFiles = Nothing
    rs.Close
    Set rs = Nothing

    See also the Help topic: How to: Work With Attachments In DAO


    -Tom. Microsoft Access MVP
    • Marked as answer by Bruce Song Wednesday, February 9, 2011 9:52 AM
    Wednesday, February 2, 2011 4:39 AM
  • Hello,

    Attachment field is a complex field. It accepts file, not Variant as in your code.

    Nadia

    • Marked as answer by Bruce Song Wednesday, February 9, 2011 9:52 AM
    Wednesday, February 2, 2011 4:40 AM

All replies

  • You could use code like this:

    Dim rs As DAO.Recordset
    Dim rsFiles As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("Customers", dbOpenSnapshot)
    Set rsFiles = rs.Fields("Attachments").Value
    While Not rsFiles.EOF
        Debug.Print rsFiles.Fields("FileName").Value
        rsFiles.MoveNext
    Wend
    rsFiles.Close
    Set rsFiles = Nothing
    rs.Close
    Set rs = Nothing

    See also the Help topic: How to: Work With Attachments In DAO


    -Tom. Microsoft Access MVP
    • Marked as answer by Bruce Song Wednesday, February 9, 2011 9:52 AM
    Wednesday, February 2, 2011 4:39 AM
  • Hello,

    Attachment field is a complex field. It accepts file, not Variant as in your code.

    Nadia

    • Marked as answer by Bruce Song Wednesday, February 9, 2011 9:52 AM
    Wednesday, February 2, 2011 4:40 AM
  • Anyway i can't use Variant in complex field. I'll find other ways. Thanks.
    Monday, May 9, 2011 4:02 PM
  • Hi everyone!

     

    This is very useful but my problem is still not solved...please help me with semiliar thing here...

     

    I want to get the attachments in my form1 control1 to go to table1 attachment field....and the next time i run the code i want it to keep going next row and save texbox1 value in field1, texbox2 value in field2 in table1  and so on...

    Thanks all in advance for helping.

     

     


    Pedie
    Thursday, August 4, 2011 9:07 PM
  • Thanks! This works fine. But it provides only the bare filename. What about if I want the full filename, including the pathname to the directory where the file is stored?
    Tuesday, March 13, 2012 7:49 PM