SteveL13
asked on
Is there a way to search for an attachment?
Using Access 2010 and incorporating the attachment feature for a field...
Is there a way, via an unbound textbox on a form, to search for an attachment by part of it's name? When I click a command button under the field I would like all attachments with the string of characters entered in the text box to appear so that when the user clicks on one of them, the attachment will open for preview.
Can this be done?
--Steve
Is there a way, via an unbound textbox on a form, to search for an attachment by part of it's name? When I click a command button under the field I would like all attachments with the string of characters entered in the text box to appear so that when the user clicks on one of them, the attachment will open for preview.
Can this be done?
--Steve
if you are referring to an "Attachment" Field, see this link
How to: Work With Attachments In DAO
http://msdn.microsoft.com/en-us/library/bb258184.aspx
.
How to: Work With Attachments In DAO
http://msdn.microsoft.com/en-us/library/bb258184.aspx
.
ASKER
I understand. But if I have several records and each record has one or more attachments and I want to find the records that have, for example, an attachment named "attachment1" and also "attachment2", and then another record that only has "attachment2", I want to have the result display both records since they both have the attachment.
I think your best bet would probably be to write a function that you pass a record ID and the name of the attachment field, and the name of the file your are looking for.
You then use code similar to what is described in the link that capricorn1 provided to:
1. Determine whether that record contains an attachment
2. If it contains an attachment, loop through the attachments and check to see whether the attachment name matches what you are looking for. If you find it, set the functions return value to True, otherwise, set it to false.
That will at least allow you to identify the records that contain the attachment.
You then use code similar to what is described in the link that capricorn1 provided to:
1. Determine whether that record contains an attachment
2. If it contains an attachment, loop through the attachments and check to see whether the attachment name matches what you are looking for. If you find it, set the functions return value to True, otherwise, set it to false.
That will at least allow you to identify the records that contain the attachment.
This is why many developers don't use Attachment datatypes (files stored IN the database)
The issue is that since one record can store multiple attachments, you must "Loop" though the attachment field for each record.
Besides you are not telling us how/where you need the results displayed.
The exact interface required will increase the complexity here.
Are you looking for a full solution here, or can you create the system to list/display the images on your own?
...and you just need us to help build the list of matching file names...?
JeffCoachman
The issue is that since one record can store multiple attachments, you must "Loop" though the attachment field for each record.
Besides you are not telling us how/where you need the results displayed.
The exact interface required will increase the complexity here.
Are you looking for a full solution here, or can you create the system to list/display the images on your own?
...and you just need us to help build the list of matching file names...?
JeffCoachman
...as fyed states, you need to reference the Attachment Filename and the recordID it is associated with, ...
... this makes "displaying" the image more complex as well.
So the code would have to first collect the search term.
Then loop all the records
Then inside the record loop, gather the attachment name (matching the search term) and the record ID
Then store this two dimensional data "somewhere" (array, table, ...etc)
Then display the filenames in a (listbox, combobox, ...etc)
...Then when a filename is selected in the list/combobox, do something *roughly* like this psuedo syntax:
TableName.RecordID.Attachm entField.( Attachment ID).Open
From where I stand this all seems possible, ...but I don't have time to dig much further right now.
So here again, you need to be very specific on exactly what parts of this system you need help with...
I know that fyed is great at these types of questions, (attachments loops, ..etc) so check back with him first, (as I am short on time.)
In any event, this code works fine to collect a list of attachment names matching the search term, and displaying them in a listbox:
(again, this does not store the RecordID, which is also needed, ...so perhaps this will give you a good start on the solution...)
'Code Start
Dim rstRecords As DAO.Recordset
Dim strAttName As String
Dim strSearchTerm As String
Dim rstPictures As DAO.Recordset
Dim strAccAttNames As String
strSearchTerm = Me.txtSearch
Set rstRecords = CurrentDb.OpenRecordset("Y ourTable")
rstRecords.MoveFirst
'Loop the records
Do Until rstRecords.EOF
Set rstPictures = rstRecords.Fields("YourAtt achmentFie ldName").V alue
'Loop the attchments
While Not rstPictures.EOF
strAttName = rstPictures.Fields("Filena me")
If InStr(strAttName, strSearchTerm) > 0 Then
strAccAttNames = strAccAttNames & "," & rstPictures.Fields("Filena me")
End If
rstPictures.MoveNext
Wend
rstRecords.MoveNext
Loop
'If no matching names were found
If Len(strAccAttNames) = 0 Then
MsgBox "No matching files found."
strAccAttNames = ""
Else
'Strip the leading delimiter character from the string of accumulated file names
strAccAttNames = Right(strAccAttNames, Len(strAccAttNames) - 1)
End If
'Load the list of names into the Listbox rowsource (Listbox rowsource set as "Value List")
Me.List1.RowSource = strAccAttNames
'Refresh the listbox.
Me.List1.Requery
'Code End
I may be able to play around a bit with this over the weekend, to see if I can get you a bit closer, ...but no guarantees.
But again, check with fyed first, as I may have even over-thought this...
;-)
JeffCoachman
... this makes "displaying" the image more complex as well.
So the code would have to first collect the search term.
Then loop all the records
Then inside the record loop, gather the attachment name (matching the search term) and the record ID
Then store this two dimensional data "somewhere" (array, table, ...etc)
Then display the filenames in a (listbox, combobox, ...etc)
...Then when a filename is selected in the list/combobox, do something *roughly* like this psuedo syntax:
TableName.RecordID.Attachm
From where I stand this all seems possible, ...but I don't have time to dig much further right now.
So here again, you need to be very specific on exactly what parts of this system you need help with...
I know that fyed is great at these types of questions, (attachments loops, ..etc) so check back with him first, (as I am short on time.)
In any event, this code works fine to collect a list of attachment names matching the search term, and displaying them in a listbox:
(again, this does not store the RecordID, which is also needed, ...so perhaps this will give you a good start on the solution...)
'Code Start
Dim rstRecords As DAO.Recordset
Dim strAttName As String
Dim strSearchTerm As String
Dim rstPictures As DAO.Recordset
Dim strAccAttNames As String
strSearchTerm = Me.txtSearch
Set rstRecords = CurrentDb.OpenRecordset("Y
rstRecords.MoveFirst
'Loop the records
Do Until rstRecords.EOF
Set rstPictures = rstRecords.Fields("YourAtt
'Loop the attchments
While Not rstPictures.EOF
strAttName = rstPictures.Fields("Filena
If InStr(strAttName, strSearchTerm) > 0 Then
strAccAttNames = strAccAttNames & "," & rstPictures.Fields("Filena
End If
rstPictures.MoveNext
Wend
rstRecords.MoveNext
Loop
'If no matching names were found
If Len(strAccAttNames) = 0 Then
MsgBox "No matching files found."
strAccAttNames = ""
Else
'Strip the leading delimiter character from the string of accumulated file names
strAccAttNames = Right(strAccAttNames, Len(strAccAttNames) - 1)
End If
'Load the list of names into the Listbox rowsource (Listbox rowsource set as "Value List")
Me.List1.RowSource = strAccAttNames
'Refresh the listbox.
Me.List1.Requery
'Code End
I may be able to play around a bit with this over the weekend, to see if I can get you a bit closer, ...but no guarantees.
But again, check with fyed first, as I may have even over-thought this...
;-)
JeffCoachman
;-)
hmmm.
I don't see an "Open" or "Activate" property of the attachment object, so you may have to load the selected file as a temp saved file, and open it in an image control...
I don't see an "Open" or "Activate" property of the attachment object, so you may have to load the selected file as a temp saved file, and open it in an image control...
hey cap, is that you?
I see your little smiley...
;-)
I use the info in your links all the time.
But the link info will only "store" or "Save" the attachment, not get the attachment name ("FileName" )
(and also deos not discuss storing the RecordID, which seems neede in this case)
So here, all of the experts posts may be similar, but still remain distinct.
You can also see that I posted a working example of the code,
(loop records, accumulate the names, load the names in a listbox, ...etc)
so this is not just a re-post of the code in the link)
...if your smiley was meant to indicate that my post was an extension of the basic info in the link.
;-)
As you can see, I am taking this year off from EE for the most part.
I am just sticking my head in on a few interesting questions form time to time
I'm so busy that many times I don't even have time to follow up.
So no top expert here should be worried about me...
I just want to coast under the radar this year.
;-)
Jeff
I see your little smiley...
;-)
I use the info in your links all the time.
But the link info will only "store" or "Save" the attachment, not get the attachment name ("FileName" )
(and also deos not discuss storing the RecordID, which seems neede in this case)
So here, all of the experts posts may be similar, but still remain distinct.
You can also see that I posted a working example of the code,
(loop records, accumulate the names, load the names in a listbox, ...etc)
so this is not just a re-post of the code in the link)
...if your smiley was meant to indicate that my post was an extension of the basic info in the link.
;-)
As you can see, I am taking this year off from EE for the most part.
I am just sticking my head in on a few interesting questions form time to time
I'm so busy that many times I don't even have time to follow up.
So no top expert here should be worried about me...
I just want to coast under the radar this year.
;-)
Jeff
Jeff,
Here is a sub I use to extract attachments. You will see that you have to open a second recordset (denoted by <<======) to get the name of the file:
Here is a sub I use to extract attachments. You will see that you have to open a second recordset (denoted by <<======) to get the name of the file:
Public Sub ExtractAttachment(TableName As String, FieldName As String, Optional Criteria As Variant = Null)
'Opens a recordset base on TableName, and extracts all attachments from field: FieldName
'If no criteria string is provided, loops through all records in the table and extracts all of the attachments
Dim rsMain As DAO.Recordset, rsAtt As DAO.Recordset
Dim strSQL As String
Dim strPath As String
On Error GoTo ProcError
strSQL = "SELECT * FROM [" & TableName & "]" & (" WHERE " + Criteria)
strSQL = Replace(Replace(strSQL, "[[", "["), "]]", "]")
Set rsMain = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
'Select the folder to extract the files to
strPath = CurrentProject.Path & "\"
'Loop through the records
Do While Not rsMain.EOF
Set rsAtt = rsMain.Fields(FieldName).Value '<<======
While Not rsAtt.EOF
If FileExists(strPath & rsAtt.Fields("FileName")) Then
Kill strPath & rsAtt.Fields("FileName")
End If
rsAtt.Fields("FileData").SaveToFile strPath
rsAtt.MoveNext
Wend
'Delete the attachments from the table
If fnAdmin() = False Then
rsAtt.MoveLast
While Not rsAtt.BOF
rsAtt.Delete
rsAtt.MovePrevious
Wend
End If
rsMain.MoveNext
Loop
ProcExit:
On Error Resume Next
If Not rsAtt Is Nothing Then
rsAtt.Close
Set rsAtt = Nothing
End If
If Not rsMain Is Nothing Then
rsMain.Close
Set rsMain = Nothing
End If
Exit Sub
ProcError:
If Err.Number = 3420 Then
Resume Next
ElseIf Err.Number = 3820 Then
MsgBox "Selected file already exists in this record!"
Resume Next
ElseIf Err.Number = 3839 Then
'Kill
Resume Next
Else
Debug.Print Err.Number, Err.Description, "LoadAttachment"
Resume ProcExit
End If
End Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
...sounds like a plan to me...
;-)
;-)
It sounds a bit odd that a single record has so many attachments that you need a search facility to find the one you want.