code to open attachment

[aspen]

Good day Every one
I Have a database with One table "Table1" and aform based on thar table. The table has two fields.
'ID' -Autonumber
'Files' -An attacment field.
I have a form build on that table
Can some one help me with a code to open the Attachment file by clicking on the 'ID" field.(each record has only one attach ment ). The files are stored in the database only.Not in a seperate folder or location in the PC. and I dont want use hyperlink metthod.
I would apreciate if some would help me with a code
Thanks

 

[GregRun]

(Nearly) Anything is possible in VBA with the right amount of determination!

Is the Records ID Field you're clicking on in a table, query, form, or report?

If it's in a table or query, the answer is NO! (and the follow up question is why are you manipulating data inside of a table or query and not a form?)

If it's in a form or report, there are several ways you can go about doing this.

Probably the most straightforward is to put a FollowHyperlink in the field's OnClick event:

Private Sub RecordsID_Click()
    Dim AttFilePath as String
    
    AttFilePath = "Your File Path Goes Here"

    Application.FollowHyperlink AttFilePath
End Sub

 

[aspen]

Dear GregRun
Thank you verymuch for your cooperation ,hope and help
My table has two fields 'ID' and 'Files' which is an attacment field. My Form is Based on the table. Can you please advice me how to code it on the click event of the ID field so that
It would open the Attachment file on click.
thank you very much

 

[aspen]

Dear GregRun
your code works fine. Can you please change it forme so that whe I click on the ID field on a form The attachment in the Attacment field opens with what evwr program apropriate to it. Like supose my table is table1.
It contains two fields.'ID" and 'Files' (Where files is an attachment Field)
And my form 'Form1" is based on that table ......
Private Sub ID_Click()
Code
End Sub
Can you please help me make this possible too

 

[GregRun]

Check out: http://blogs.office.com/b/microsoft...-in-the-new-access-2007-attachment-field.aspx

 

[smig]

You can use system shell to open any file
search the forums.
I'm not at my home machine now and can't put the code

 

[aspen]

Actually i tried my level best to make the blog thing work
and on clicking the id field it opens the file but only the attachment for the first record for every record. I have different file attachments for eaach record. But if i clic the ID field for record 2 . it opens the attachment for record 1. I want it to open record 2's attachment if i click record 2's ID
Can some one please modify this code forme so it would only open the corresponding record when we click the ID field
Private Sub ID_CLICK()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Const strTable = "Table1" '<- Replace this with your table with attachment
Const strField = "Files" '<- Replace this with the fieldname of your attachment
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strTable)
'rst.MoveNext ' Uncomment this line to go to the 2nd row in the Table.
OpenFirstAttachmentAsTempFile rst, strField
rst.Close
End Sub

Public Function OpenFirstAttachmentAsTempFile(ByRef rstCurrent As DAO.Recordset, ByVal strFieldName As String) 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 Function 'OpenFirstAttachmentAsTempFile

The code is is under the on click event of the ID field
Thanks

 

[smig]

Did you search for the System Shell as I suggested ?

Here is the code I use
Put this code in normal module (not form's module)

Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" (ByVal hWnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long

Private Const SW_SHOWNORMAL = 1

 
Public Function OpenDirApp(WhatToOpen As String)
ShellExecute 0, "Open", WhatToOpen, vbNullString, vbNullString, SW_SHOWNORMAL
End Function

Send the full file name (full path + file name) as string - WhatToOpen


When you put code please put in into a code section (#)

 

[aspen]

Hi Smig
Thanks for this
I would like to use two filelds in my form:ID and an Attachment field. And I would like to click on each records ID and open the Record related to the specific record. I mean I Can't usea file path hence There are so many attachments and I delet the attachment from folder after attachment. Can you make a code like that. Theat blog example opens the first record's attachment on clicking any ID. If wecan make it open only the attachment related to the specific ID only.
Please help me fix this if you can.
Thanks

 

[smig]

I have no idea how and where you save your files.
This code is to open an alreay saved file

 

[aspen]

Dear Smig
Thanks trying to help me and any body else who may also need it. Let me explain it. My database has one table and on form based on the table named 'Table1'. It has two fields "ID" fileld And 'Files' ('files' is the attachment field). So the files are save in the Table ones's 'Files' Field. So can we just use these 2 fields in the cord without regaurd to the Folder in the pc from where the attachment was brought to the table.
I often use macros but I dont think a macro will work for this.

 

[smig]

It seems to me you simply copied the code GregRun gave you without undersatnding how it works and what it do.

The first function is what export the file out of the db and show it.

The second function is to take all the files stored in the table, send them one by one to the first function (for exporting and opening).
In this function the .MoveNext is commented so it will only run for the first file. It was done for testing only

As you want to select the file to export and view using a form this code is useless for you as it is

Try this code, in any .On_Click/.OnDouble_Click event you choose:

[FONT=Courier New]Dim dbs As DAO.Database[/FONT]
[FONT=Courier New]Dim rst As DAO.Recordset[/FONT]
[FONT=Courier New]Dim fldAttach As DAO.Field[/FONT]
[FONT=Courier New]Dim strTempDir As String[/FONT]
[FONT=Courier New]Dim strFileName as string[/FONT]
[FONT=Courier New]Dim strFilePath As String[/FONT]
 
[FONT=Courier New]strTempDir = Environ("Temp") ' Get the Temp directory from the environment variable.[/FONT]
[FONT=Courier New]If Right(strTempDir, 1) <> "\" Then strTempDir = strTempDir & "\" ' Make sure the path always ends with a backslash.[/FONT]
 
[FONT=Courier New]Set dbs = CurrentDb[/FONT]
[FONT=Courier New]Set rst = dbs.OpenRecordset("Select * From [table1] Where [ID] = " & Me.ID)   ' -- Table1 hold the files. Me.ID is the ID selected on the form[/FONT]
[FONT=Courier New]rst.MoveFirst[/FONT]
[FONT=Courier New]strFileName = rst.Fields("FileName")   ' -- The FileName field in Table1 hold the name of the file[/FONT]
[FONT=Courier New]strFilePath = strTempDir & strFileName  ' -- Append the name of the attached file to temp dir.[/FONT]
[FONT=Courier New]call KillFileFromTempFolder(strFilePath)   ' -- Kill an old file if exist[/FONT]
[FONT=Courier New]Set fldAttach = rst.Fields("FileData") ' -- FileData field in Table1 hold The binary data of the file.[/FONT]
[FONT=Courier New]fldAttach.SaveToFile strFilePath   ' -- Save the file[/FONT]
[FONT=Courier New]rst.Close   ' -- close the rs and clean[/FONT]
[FONT=Courier New]Set rst = Nothing[/FONT]
 
[FONT=Courier New]  VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch  the file.[/FONT]

This public sub is to kill the file before you try to export new one

[FONT=Courier New]Public sub KillFile(strFilePath as string)[/FONT]
[FONT=Courier New]If Dir(strFilePath) <> "" Then ' the file already exists--delete it first.[/FONT]
[FONT=Courier New]VBA.SetAttr strFilePath, vbNormal ' remove any file attributes (e.g. read-only) that would block the kill command.[/FONT]
[FONT=Courier New]VBA.Kill strFilePath ' delete the file.[/FONT]
[FONT=Courier New]End If[/FONT]
[FONT=Courier New]end sub[/FONT]

 

[aspen]

Hi Smig
Thanks. I was so thrilled I wanted to hold you up against the sky and embrace. I am sure we are very close and I am very new to VBA though my goal is high. Thanks Forbeing there and I hope that That you would be with me how ever small i am.
Below is how I Pasted your code in the code window On the forms on click event of the 'ID'.
It gave the error massage "compile errorr
sub or function not defined
and it highlights This part of the code
"Call KillFileFromTempFolder"

Private Sub ID_CLICK()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fldAttach As DAO.Field
Dim strTempDir As String
Dim strFileName As String
Dim strFilePath 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 dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From [table1] Where [ID] = " & Me.ID) ' -- Table1 hold the files. Me.ID is the ID selected on the form
rst.MoveFirst
strFileName = rst.Fields("FileName") ' -- The FileName field in Table1 hold the name of the file
strFilePath = strTempDir & strFileName ' -- Append the name of the attached file to temp dir.
Call KillFileFromTempFolder(strFilePath) ' -- Kill an old file if exist
Set fldAttach = rst.Fields("FileData") ' -- FileData field in Table1 hold The binary data of the file.
fldAttach.SaveToFile strFilePath ' -- Save the file
rst.Close ' -- close the rs and clean
Set rst = Nothing

VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file.e
End Sub

Public Sub KillFile(strFilePath As String)
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
End Sub

please see whats wrong
and please help

 

[aspen]

Dear smig

please note

The Form opens in data sheet view

 

[smig]

change this line to:
Call KillFile(strFilePath)

My mistake. I changed the name of the sub (The second one) but didn't change how I call it.
That's why it can't find the Sub or Function

whan you post code please put it into a code section (#, in advanced mode - not quick reply)

 

[aspen]

Hi smig
strFileName = rst.Fields("FileName") ' -- The FileName field in Table1 hold the name of the file

This line is high lighted now

Thank you

 

[aspen]

Hi Smig

this is how I pasted it
please check if its done right

Private Sub ID_CLICK()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim fldAttach As DAO.Field
Dim strTempDir As String
Dim strFileName As String
Dim strFilePath 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 dbs = CurrentDb
Set rst = dbs.OpenRecordset("Select * From [table1] Where [ID] = " & Me.ID) ' -- Table1 hold the files. Me.ID is the ID selected on the form
rst.MoveFirst
strFileName = rst.Fields("FileName") ' -- The FileName field in Table1 hold the name of the file
strFilePath = strTempDir & strFileName ' -- Append the name of the attached file to temp dir.
Call KillFile(strFilePath) ' -- Kill an old file if exist
Set fldAttach = rst.Fields("FileData") ' -- FileData field in Table1 hold The binary data of the file.
fldAttach.SaveToFile strFilePath ' -- Save the file
rst.Close ' -- close the rs and clean
Set rst = Nothing

VBA.Shell "Explorer.exe " & Chr(34) & strFilePath & Chr(34), vbNormalFocus ' Use Windows Explorer to launch the file.e
End Sub

Public Sub KillFile(strFilePath As String)
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
End Sub

Thank you

 

[aspen]

I checked almost all forums and the net

There seems to be nothing like this

Us this forum is about make hitory

 

[smig]

Your table should have at least 3 fields - the table key, the name of the file and the binary data of the file
You can name theese fields as you like. Change the code so the fields names will match the real names.

A tip: don't put spaces in names. Under line is OK. Don't use names that might be reserved words, like : name, date, key, field



Please,please.... Put code in code section

 

[aspen]

Hi smig

I owe you one

best regaurds