?
Solved

Is there a way to search for an attachment?

Posted on 2013-05-10
13
Medium Priority
?
171 Views
Last Modified: 2013-05-14
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
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 39155331
Can you provide some context for this.

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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39155346
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


.
0
 

Author Comment

by:SteveL13
ID: 39155351
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.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 48

Expert Comment

by:Dale Fye
ID: 39155418
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39156248
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39156684
...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.AttachmentField.(AttachmentID).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("YourTable")
 
rstRecords.MoveFirst
    'Loop the records
    Do Until rstRecords.EOF
        Set rstPictures = rstRecords.Fields("YourAttachmentFieldName").Value
        'Loop the attchments
        While Not rstPictures.EOF
            strAttName = rstPictures.Fields("Filename")
            If InStr(strAttName, strSearchTerm) > 0 Then
                strAccAttNames = strAccAttNames & "," & rstPictures.Fields("Filename")
            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
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 39156697
;-)
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39156698
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...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39156785
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
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 39156891
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:
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

Open in new window

0
 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 1000 total points
ID: 39156944
Dale
Hi...

My post was to explore the concept of finding the attachments that matched the search term and listing them in a list/combobox.
How to actually "open/display" the image was beyond me.

I had hoped that the attachment object would have an "Open/activate" property but no such luck.
:-(

So that is why I suggested storing the selected file as a saved file, then opening that.
So your post may very well be more to the heart of the issue.

So let's see what the OP comes back with.

Jeff
0
 
LVL 48

Accepted Solution

by:
Dale Fye earned 1000 total points
ID: 39156991
Jeff,

My thought was rather than looping through all of the records in a single function call, opening a specific record (passed by the function call) in the function,  then running code similar to mine, but rather than doing the save, just doing the loop to see whether the attachments file name, denoted by:

rsAtt.Fields("FileName")

matches the name of the file that the OP is looking for.  If so, the function would return TRUE and jump to the end of the function to close the recordsets.  If false, it would continue to loop through rsAtt until the EOF marker, which would then generate a FALSE return value.

This would allow the OP to determine which records contain the desired file name.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 39163588
...sounds like a plan to me...
;-)
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question