Attachment List

[Ineedcoffee]

Hi,

I have attachments for customers and I want to make a text box that displays the names of all attachments the customer has.

Any idea how this would work?

Thanks.

 

[vbaInet]

Hello again. When you say the names of the attachments what are you referring to? The name of the file or the full hyperlink?

 

[Ineedcoffee]

Just the name of the file if possible

 

[vbaInet]

A list box would be a good visual representation of all the file names. Or you could use a subform. The functions to use are InStr(), Mid() and Left(). Have a look at those functions and see what you come up with. The idea is getting rid of the last 4 or 5 characters to the right (for e.g. ".pdf" or ".xlsx"), searching for the last backslash "\" and returning all characters after that.

 

[Ineedcoffee]

Gotcha, thanks. Once I get round to it I'll post a copy of it working in case anyone else needs this.

Thanks again.

 

[vbaInet]

Goodie. You're welcome. Keep us posted

 

[Lightwave]

The following code takes the string from a field where the path and filename (DocMgrPath) are stored and returns the filename solely and puts it into a field called
DocMgrDesc

Private Sub Command45_Click()
On Error GoTo Err_Command45_Click

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    Dim intNumber As Integer
    Dim intDifference As Integer
    Dim strfilename As String
    Dim intSplit As Integer
    
    intNumber = Len([DocMgrPath])
    intSplit = InStrRev(Me.DocMgrPath, "\")
    intDifference = intNumber - intSplit
    
    strfilename = Right(Me.DocMgrPath, intDifference)
    
    Me.DocMgrDesc = strfilename
    
    
Exit_Command45_Click:
    Exit Sub

Err_Command45_Click:
    MsgBox Err.Description
    Resume Exit_Command45_Click
    
End Sub

 

[Ineedcoffee]

Hi Lightwave,

Thanks for the code, was tearing my hair out. I take it that intNumber = Len([DocMgrPath]) would be the drive letter and path of where the document is stored?


Thanks.

 

[Ineedcoffee]

Hmm, think it is me being stupid. I now think I understand, this code is to do the attachment, it then puts the attachments name in to the field DocMgrDesc.

Is this right?

 

[Ineedcoffee]

I have tried the code and I'm coming up with an error when compiling, error is to do with intSplit = InStrRev(Me.DocMgrPath, "\") the error is Method or Data member not found.

Not sure what I have done wrong, any advice?


Thanks.

 

[vbaInet]

Me.DocMgrPath is the field or control that holds the value of the file path.

 

[Ineedcoffee]

Ok, I don't have a field that stores the path to the file, at the moment I just use the built in Access attachment field. I did not think that this was the field that stores the file path or does it?

 

[vbaInet]

That name could have read, Document Manager Path, so I would believe it's the field that stores the path.

 

[vbaInet]

Here's another way:

Private Function GetFileName(strPath as string)

Dim posStart As Long

posStart = InStrRev(strPath, "\", , vbTextCompare) + 1
GetFileName = Mid(strPath, posStart, (InStrRev(strPath, ".", , vbTextCompare) - posStart))

End Function

 

[Lightwave]

Ok, I don't have a field that stores the path to the file, at the moment I just use the built in Access attachment field. I did not think that this was the field that stores the file path or does it?

Sorry Inc I wasn't completly sure how you were linking to the attachments. In one particular application I have a button that will grab file names from a file manager dialogue box so that a user can link documents into a record.

Rather than typing the name of the document the user can subsequently hit a button and it takes the stored path and returns the filename irrespective of the complexity of the directory structure.

And yes it takes the filename from the longer DocMgrPath field and puts it into the shorter subset DocMgrDesc field all on one record line.

I used Access 2003 and I'm not sure that I've used the access attachment field. That field may have an option to show the path in which case you would be wanting to run the functions on that path to get the document name.

Afraid you may have to experiment with VBinets code.
You may find it easier to export all the paths into excel - perform the concatenation there and then import back.

Assuming you can get a list of the filepaths.

All depends on how many records you have and whether you can get the filepaths out.

M