Solved

Select a file listed in a list box and push a button and have the file automatically be attached to the attachment field in an Access 2010 database

Posted on 2013-01-08
4
410 Views
Last Modified: 2013-01-16
History of situation
I have an access 2010 database that part of it's use it to manage drawings that get sent to us for fabrication. These drawings get dropped into many different directories that then need to be checked periodically throughout the day to see if there are any new ones. Needless to say this is quite time consuming and prone to error. So, I've created a form that will with the push of a button pull in the path and fill name of all the files in each directory into a table and then compare against the previous import. The new, non-matching drawing files get listed in a list box. From there we can select each new drawing file listed and precede with each of our remaining actions.

Question
I tell you all of that so you will understand where I am and now what it is I want to be able to do. One of the actions I would like to perform is when I select on one of the files listed in the list box I would then like to push a button that would activate the code behind it that would take the information in the file name field and file path fields and using VBA be able to automatically attach the selected drawing file to the attachment field.

Database design
I have attached a screen shot of what this part of the form looks like. You will see a drawing file highlighted and the button I will click to be able to view the file based on the linked path stored in the table. During the time that file is opening I would also like the original file to be automatically linked, or just the information updated to the attachment field if that's possible.

The name of the table and fields are :tblDrawings, fName, fPath, fFilePath, and the attachment field. frmGetNewDrawings, btnPreviewDrawings.

For those that may read this and want to suggest that I would normally be better off leaving the files linked instead of attaching, well I agree, except that all users of this database do not have access to the server where the files reside. These users will be accessing through the web so they will need local copies of the files, hence the need for the attachment field use. They will be reading the drawings, they will not be linking and attaching them.

I appreciate the solution to this problem because it's badly needed. I'm awarding maximum points to the quickest and completest solution,.

Thanks,
George
0
Comment
Question by:gedwardnelson
  • 2
4 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38757754
see the codes from this link

* Add an Attachment to a Record


http://msdn.microsoft.com/en-us/library/bb258184.aspx
0
 
LVL 77

Expert Comment

by:peter57r
ID: 38757909
"These users will be accessing through the web..."

How will they do this- have you built a web front-end  for the application ?
0
 

Author Comment

by:gedwardnelson
ID: 38758515
The users that will be performing the attaching actions will be using an access 2010 front end hosted by a sharepoint server. That part works. I just need to add the additional step of clicking on the file that shows up in the list box, clicking the button and attaching that file to the attachment field of that record.

The user that will be viewing the attachment (all will be .pdf) will be using a web form. Two different types of users.

Does that help?
0
 

Author Comment

by:gedwardnelson
ID: 38758701
Here's some additional information.

The SQL behind lstNewDrawings is:

SELECT tblDrawings.FName, tblDrawings.fFilePath, tblDrawings.DateViewed
FROM tblDrawings
WHERE (((tblDrawings.DateViewed) Is Null))
ORDER BY tblDrawings.FName;

The file attachement field is FileAttachment.

Table structure:
FName: File Name only
fFilePath: Complete file path including path and file name

So, I want to be able to click on a record in lstNewDrawings, click btnPreviewDrawings, and after the code runs that opens the linked file I'd like the file also attached., to the same record.
0

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

747 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now