Adding an attachmetn to a record from a user form

[dccjr]

I am trying to allow the user to add an attachment to the record being creaed in the userform. This attachment could be one of several types: Word document, JPEG, Bitmap, PDF, etc. I would like to open an object similar to the File Open dialog box and allow the user to navigate to the attachment from there. Then I need a way to open this attachment for viewing and/or printing with the report.



I am in over my head and could use some help.

[June7]

Why not use the Attachment dialog to attach files? If you don't want to use the Attachment dialog then this gets complicated.

Review http://blogs.office.com/b/microsoft-...cess-2007.aspx

Why are you embedding files in the database? This can really bloat the file size. Access has a 2gb limit.

[dccjr]

This project tracks manufacturing defect reports. At times, photos or technical drawings are attached to the root cause report. It would be reaaly helpful to keep all of the documentation together. I was not aware that Access had a 2gb limit. Any ideas on how to handle this?

[June7]

Save the documents externally and have a child table that saves the directory path and file name of each document. This will be a 1-to-many relationship. Many document records for each parent record. Can use the File Dialog to allow users to navigate to the file and grab the path and name for saving to table. Only complication with this is the path is mapped drive referencing. If this is a multi-user database, every user would need the same drive mapping on their computer and everyone would have to navigate the same path when saving. Alternative referencing is UNC (Universal Naming Convention) that uses server name in the path instead of mapped drive letters. If the files are always in the same location then can automate the path prefix and just have user select the file and store the file name.

[dccjr]

???? I am completely lost as to where to even start on that, though it does sound like a good idea. The tables sit on the server, while each person has a copy of the frontend on their local. That makes your solution seem even more plausible. Where do I start?

[June7]

What type of files? Image (jpg, bmp, pdf, Autocad, Word, Excel). The type of file will dictate how Access works with it. Most image filetypes (not pdf) are easily opened in an Image control on form or report. Other files can be opened in their native application by a hyperlink action. Other Office files (Word, Excel) can be embedded in a control on form (I have never done this).

Start by setting up table and relationships. Build form/subform for the data entry. Set office policy on where the files will be stored. This will dictate if the entire path/filename must be saved in table or if the path is consistent up to a certain point and can be prefixed by code to path/filename saved in table.

Either use UNC pathing for accessing/opening the external files or everyone must have the same drive mapping. Must use code to utilize UNC pathing.

We use ArcGIS software and the nature of that app requires that everyone have the same drive mapped. Every new computer is set up with an R:\Crm mapped drive. When creating project with ArcGIS, all referencing must go through the R:\Crm heirarchy so that when someone else opens the same project the software can find all the pieces of the project.

However, in Access I use UNC referencing and the syntax is: \ \servername\path\filename

Example from my system: \ \dotatufs02\Crm\Lab\Database\Materials.accdb

Yes, this is a bit more complicated than embedding files in the database. But if you will have a lot of of records and documents so that the 2gb file size will be exceeded, I don't know any other alternative.

[dccjr]

Thanks for the info. I have created a table for the path/file name. I need to make this a child to the main table? Even though all attachments will be saved to a central folder on the network drive, I am using UNC. I am unsure how to proceed, so let me describe the actions as I see them.

A "Attachments" button on the data entry form.
This will open a dialog (Open or Save As) box from which the user can navigate to the attachment(s) (be it a an autoCAD drawing or Quality Inspection report) on a local or network drive, or to a digital camera connected via USB.
The user will select the files to be attached.
These will be resaved to the RCAAttachments folder and the path and file names to the table.

The logic works in my head, but am not sure how feasible it is in practice. Thoughts or suggests for an avenue to pursue?

[June7]

You want to:

1. allow user to select a file

2. copy file to another location

3. save file name to field in table

For 1 and 2 review https://www.accessforums.net/program...ile-31690.html

For 3 need additional code, depends on how you want to save the file name. If a form bound to the destination table is open and user is entering data into record, then could just save the file name to field of the form's RecordSource.
Me!fieldname = strTo

[dccjr]

The table that the form is bound is not the table where I was goint to save the file name(s). I set up another table specifically for the file names. Upon furthewr consideration, I am not sure why I could not just add the field to the main table. Any functional reason why I shouldn't? Thanks for all the help. The link you provided was just what I needed. I am well on my way to adapting it to my purpose.

Many, many thanks.

[June7]

If there is only one external file per record, then yes, include on main table.