Create a Button to Open a Attachment Field in Access 2010

[remeng]

Hi All,

This might be a dumb question but here it is. I created a database in Access 2003 and have converted it to Access 2010. There was no issue with the conversion, but I am new to 2010 and am running into a problem. First off, I can't locate the record macro function. Where is it now? Second, I have an attachment field, and I would like to create a button that will open the file in the attachment field (I created the database so long ago that I can't even remember how I created that. I think it was a field offered in a previous version). Since I don't know how to really use the new, my first macro system, how can I go about creating this simple macro button?

Thank you all,

Mike

 

[remeng]

Here is what I tried:

Package_List is the name of the field that holds the attached file as an attachment field.

Application.FollowHyperlink Me.Packaging_List

I am getting a runtime 438 error (Object does not support this property or method). What can I do to correct this? Is the FollowHyperlink the best way to open the attached file?

 

[dhookom]

I don't recall there ever having been a "record macro function" in Access. Are you sure it's and attachment field since this type of field is new to Access and you make it sound as if your database was created in an older version.

Duane
Hook'D on Access
MS Access MVP

 

[remeng]

When I go into the edit format screen, that is what the header for the field is. It may be possible that I did create this in 2010 but I am not sure

 

[MajP]

Private Sub cmdOpenAttach_Click()
    Dim rs As DAO.Recordset
    Set rs = Me.RecordsetClone
    OpenFirstAttachmentAsTempFile rs, "Attachments"
End Sub

Public Sub OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String)
    Dim rstChild As DAO.Recordset2
    Dim fldAttach As DAO.Field2
    Dim strFilePath As String
    Dim strTempDir As String

    strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.
    If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.
    Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.
    strFilePath = strTempDir & rstChild.Fields("FileName").Value ' Append the name of the first (and only) attached file to temp dir.
    If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.
        VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.
        VBA.Kill strFilePath ' delete the file.
     End If

    Set fldAttach = rstChild.Fields("FileData") ' The binary data of the file.
    fldAttach.SaveToFile strFilePath
    rstChild.Close ' cleanup
    VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.
End Sub

 

[remeng]

MajP,

Thank you for the reply. The code is failing here:

Set rstChild = rstCurrent.Fields(strFieldName).Value ' the .Value for a complex field returns the underlying recordset.

Here are my field names. This may help:

Button Name = OpenPackList

field with attachment file within = Package List

Folder location were .xls files are saved = J:\Lepelstat Crafts (DO NOT DELETE)\ORDERS\Etsy

Update: The database was created in Access 2003

Thank you for the further help.

 

[MajP]

Maybe your terminology is wrong.
Do you really have an attachment field?

http://office.microsoft.com/en-us/a...the-records-in-your-database-HA001213852.aspx

Or do you simpy have a hyperlink field?

http://office.microsoft.com/en-us/access-help/create-a-hyperlink-HP005188821.aspx

These are in no way similar so you need to determine what you really have. I am now assuming you mean a hyperlink field because you are referencing a folder location.

 

[remeng]

Here is a screen shot. If you look at the description at the top right for the Property Sheet under the Selection Type it says: Attachment

I hope this helps.

Mike

 

[MajP]

Folder location were .xls files are saved = J:\Lepelstat Crafts (DO NOT DELETE)\ORDERS\Etsy

Ok if this is an attachment field the above has absolutely no meaning. Attachment fields are saved inside the database not external folders. So what are you saying here?

 

[MajP]

The code is failing here:

The above statement is meaningless. That is like going to a mechanic and saying your car does not work. What is the error message? What is the error number? What is the code to call the function? What is happening or not happening?

 

[remeng]

The create shipping form button generates an Excel file that is save to that folder via a VB script. I didn't know if that was important since when I attach the file I need to navigate to the file to attach it. Since it seems that the field is actually an attachment, can the Open Package List button be directed to open the attachment?

 

[MajP]

Since it seems that the field is actually an attachment, can the Open Package List button be directed to open the attachment

If you want to open a file saved as an attachment, the code I provided does exactly that.

 

[remeng]

MajP,

Thank you for the help. With your code, what am I doing incorrectly to be getting the error I previously mentioned?

Mike

 

[MajP]

See my reply of, 12 Feb 13 22:30