UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V  1 2 >  (Go to first unread post)
   Reply to this topicStart new topic
> Open Attachment On Double Click From Query Entry    
 
   
mlindenbach
post Sep 3 2013, 01:31 PM
Post#1



Posts: 10
Joined: 3-September 13



Hello,
I am building a contract management database and for each contract number there could be multiple attachments. I have built a query to display the names of the attachments corresponding to the respective contract number but is there a way to open that attachment when the “paper clip” is double clicked? I know there is the option to have an attachment control that you can double click and it shows all the attachments; however, I would like to eliminate the number of clicks and make this more user friendly. I have posted the example database (under the “attachments” tab).
Thank you SO MUCH for any help / input!
Melissa
Attached File(s)
Attached File  ContractDatabase.zip ( 59.75K )Number of downloads: 7
 
Go to the top of the page
 
theDBguy
post Sep 3 2013, 02:27 PM
Post#2


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Melissa,
welcome2UA.gif
Perhaps these two methods would help?
Go to the top of the page
 
mlindenbach
post Sep 3 2013, 03:14 PM
Post#3



Posts: 10
Joined: 3-September 13



Hello!
It looks like those instructions are to add files - I am wanting to double click on the icon in the query and pull up documents that have already been stored.
Please advise.
Thanks!
Melissa
Go to the top of the page
 
theDBguy
post Sep 3 2013, 03:21 PM
Post#4


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Melissa,
Yes, but I was thinking that you could use the SaveToFile method to pull out a copy of the attachment from your table, save it temporarily on the user's hard drive, and then open it using Shell or ShellExecute API to view the attached file.
Hope that makes sense...
Go to the top of the page
 
mlindenbach
post Sep 3 2013, 03:25 PM
Post#5



Posts: 10
Joined: 3-September 13



Hey!
My access skills are self-taught so that was kind of Greek to me < Would it be a terrible inconvenience for you to amend the example so as per your directions so I can take a look?
Thanks in advance,
Melissa
Go to the top of the page
 
theDBguy
post Sep 3 2013, 03:35 PM
Post#6


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


What Access version was it in? When I get a chance, I'll download it and see. Just need to make sure I"m on the right machine with the correct version of Access for your file.
Go to the top of the page
 
mlindenbach
post Sep 3 2013, 03:40 PM
Post#7



Posts: 10
Joined: 3-September 13



I am using Access 2010
Thanks, AGAIN!
Go to the top of the page
 
mlindenbach
post Sep 4 2013, 03:52 PM
Post#8



Posts: 10
Joined: 3-September 13



Hello again!
Any luck with this. Sorry to harass < The last piece of my database to figure out before finalizing..... and with all my googling I am anxious to find a solution to this maddness!
Thanks again!
Go to the top of the page
 
theDBguy
post Sep 4 2013, 04:13 PM
Post#9


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi,
Sorry for the delay... I started working on it last night but didn't finish. I'll try again tonight. I only have 2007 at work, so I have to wait 'til I get home to a 2010 machine.
Cheers!
Go to the top of the page
 
theDBguy
post Sep 4 2013, 10:54 PM
Post#10


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi,
Here you go... It's not perfect but give this one a try...
Attached File(s)
Attached File  ContractDatabase.zip ( 52.95K )Number of downloads: 9
 
Go to the top of the page
 
mlindenbach
post Sep 5 2013, 10:34 AM
Post#11



Posts: 10
Joined: 3-September 13



OMG I am so excited! Thank you so much! That works EXACTLY as I had hoped it would! I do have a quick question – you code worked PERFECTLY in the example but when I plugged it into mine I kept getting an error at this line:
trSQL = "SELECT Attachments FROM tblContractInforamtion WHERE ID=" & Nz(Me.ID, 0)
because it didn’t recognize the “ID” in Me.ID. Why would that be? Did you change the value of something somewhere? I looked and can’t seem to find it.
Oalso saw your note that “Clean up routine to delete the copied file is not included in this demo” Do you know how to delete the copied file? That would be ideal but not absolutely mandatory.
Thanks again! I really appreciate all your help!
Melissa
Go to the top of the page
 
theDBguy
post Sep 5 2013, 10:52 AM
Post#12


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Melissa,
did make some minor changes to your file, but I tried not to change everything. For example, your table name "tblContractInforamtion" is probably spelled incorrectly, but I didn't fix it because it would affect other parts of your database that I didn't want to get into at the moment because I only had a limited time to get the part you wanted working. However, I did change the record source of your subform because I thought the parameter query you were using was unnecessary. So, that's where the ID part came from. However, you don't really need to change your subform and just try modifying the code as follows to see if it works:
strSQL = "SELECT Attachments FROM tblContractInforamtion WHERE ID=" & Nz(Me.Parent.ID, 0)
To delete a file, you can use the Kill statement. For example, this statement will delete the file you just opened:
Kill strFilePath & strFileName
The problem is that using that command right after the ShellEx code will fail because the file is currently open and cannot be deleted until it is closed. So, you really need to issue the Kill statement after the user has closed the file, which is not easy because we can't tell ahead of time when the user will actually close it.
You can write a loop to "wait" for the open file to close, but that doesn't address the fact that the user could go back to Access without closing the file. If they do that, they won't be able to do anything because the loop is still running in the background.
I think the best approach would be to delete the files when the user closes the database. To do that, we'll need to keep track of any files they opened. For that, you can create another table to store the filepath and filenames and then run through it to delete the files and the records at exit.
A simpler approach might be is to change the file path so that instead of the same directory as the database, the files to be opened are created in the system temporary folders. That way, you can just ignore them, and when the system cleans up the temporary folders, those files will be deleted for you.
Go to the top of the page
 
mlindenbach
post Sep 5 2013, 11:39 AM
Post#13



Posts: 10
Joined: 3-September 13



Loved your idea about the temp files – worked like a charm!
oing back to the ID. I changed the file to Me.Parent.ID as you suggested and now I get a break at
Set rs1 = db.OpenRecordset(strSQL, dbOpenSnapshot)
When I hover over the highlighted error I get a comment stating “rs1=nothing” What may that be from?
Thank you again for all of your guidance and patience <
Melissa
Go to the top of the page
 
theDBguy
post Sep 5 2013, 11:41 AM
Post#14


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Melissa,
also forgot to mention that I changed the name of the attachment field in your demo. You had Field1, I changed it to Attachments. Maybe that's the problem?
Go to the top of the page
 
mlindenbach
post Sep 5 2013, 11:55 AM
Post#15



Posts: 10
Joined: 3-September 13



OK slowly making our way down this code – I really appreciate your patience with me!
did notice the change from File1 to Attachment – thank you for that.
Ochanged SELECT Attachments FROM tblContractInforamtion WHERE ID=" & Nz(Me.Parent.ID, 0) to SELECT Attachment FROM tblContractInforamtion WHERE ID=" & Nz(Me.Parent.ID, 0) (deleted the “s” on attachment)
Now I get an error at Set rs2 = rs1.Fields(0).Value
When I hover rs2 I get a message stating “rs2=nothing” and when I hover over the second part of the equation it states “rs1.Fields(0).Value=<No Current Record.>
I just noticed where I think the issue is. When I hover over strSQL = "SELECT Attachment FROM tblContractInforamtion WHERE ID=" & Nz(Me.Parent.ID, 0) I get a note explaining the code stating “pull where ID=5”) but the ID should be 1 (for the example I am playing with). Where would that error be coming from? I changed a few things in my system and currently only have 3 example attachments……therefore, there is no ID=5 (which is why I think there is a "no record" error).
Thanks again!
Melissa
Go to the top of the page
 
theDBguy
post Sep 5 2013, 12:34 PM
Post#16


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi Melissa,
irst of all, "Attachment" is a data type, so it could be a *reserved* word in Access. That's why I called the field Attachments (with the "s"), but I really wanted to call it Documents to further avoid any conflicts.
I thought I had a check in there for "no records?" It should look something like this:
If Not (rs1.BOF And rs1.EOF) Then
Set rs2 = rs1.Fields(0).Value
...
Again, I am not on a 2010 machine now to check.
Go to the top of the page
 
mlindenbach
post Sep 5 2013, 01:31 PM
Post#17



Posts: 10
Joined: 3-September 13



I am still struggling a bit and hoping for one last ask of assistance! I have attached my revised database. I changed from “attachment” to “document” as per your suggestion. I also changed the format a bit (which I probably shouldn’t have done – I apologize). I wanted a separate table (form) to capture information about the attachment so we could look at a description of what that attachment was (so we don’t have to open everything to see what it contains). For whatever reason, it is still showing “no record” and an error when I double click the name. I am hoping that through showing you the database this will be easier for you.
Thanks so much, again, for all your help!
Me
Attached File(s)
Attached File  ContractDatabase2.zip ( 77.93K )Number of downloads: 8
 
Go to the top of the page
 
theDBguy
post Sep 8 2013, 01:24 PM
Post#18


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


Hi,
Just a quick question before we get too deep... Do you really need to store the attachments (documents) in the database? Do you know that storing attachments increases the size of the database file? It's always been considered best practice to only store the network path to the files instead of the files themselves. Is that not possible in your case?
Go to the top of the page
 
mlindenbach
post Sep 9 2013, 08:17 AM
Post#19



Posts: 10
Joined: 3-September 13



At this point it is not possible.
Go to the top of the page
 
theDBguy
post Sep 9 2013, 08:58 PM
Post#20


Access Wiki and Forums Moderator
Posts: 71,199
Joined: 19-June 07
From: SunnySandyEggo


I see. Okay, take a look at the attached modified copy of your db.
Attached File(s)
Attached File  ContractDatabase2.zip ( 62.94K )Number of downloads: 8
 
Go to the top of the page
 
2 Pages V  1 2 >


Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 12:08 PM