Attach multiple scanned pdfs to one record (1 Viewer)

[jmsjazz]

I would like to be able to add more than one scanned document and link them to one record.
If it is possible, I would like to have a command something like "Attach file" on the form displaying the record. The user could browse to find the scanned document and it becomes a link. The user could then scan and browse for another document, which becomes a second link, in some kind of list box. Do you think it might work?

 

[pbaldy]

Sure, done it several times. I use a second table with a one to many relationship to the main table, and a subform to display them. I just store the path to the file. Lots of browse methods, here's one:

http://access.mvps.org/access/api/api0001.htm

 

[jmsjazz]

Excellent! Thanks again.

 

[pbaldy]

No problem! Post back if you get stuck. I should add I've always used a simple text data type, not hyperlink.

 

[jmsjazz]

Hi - I got stuck!

Then I found this:

http://www.access-programmers.co.uk/forums/showthread.php?t=153350

I created a new module and put the functions in it; I can now browse for a file, and store the link in a textbox. I would like to be able to add more than one file, and have them available in a list box.

 

[pbaldy]

It appears my earlier response got lost in the forum's problems. I use a subform to display, but you can probably use a listbox. You'll lose the way a subform will stay in sync automatically, but it can be done.

 

[jmsjazz]

Thanks...I am using a subreport, which looks exactly how I want it to.

As for adding the attachments to tblAttachments, I found this from Bootwalker:

SQLstring = "Insert into [Hours]([Entry date], [User], [Hours], [Task], [Description]) " & _
"Values (#" & Me.Controls("day" & I).Caption & "#,'" & TempVars!tmpUserID & "'," & _
"'" & Me.Controls("hours" & I).Value & "','" & Me.Controls("labelTask").Caption & "'," & _
"'" & Me.Controls("comment" & I).Value & "')"

My fields to update are [ID] and [filename], but I get mixed up with the "' syntax: can you explain it please?

 

[pbaldy]

Here's a tutorial:

http://www.baldyweb.com/BuildSQL.htm

And an important debugging tool:

http://www.baldyweb.com/ImmediateWindow.htm

 

[jmsjazz]

Many thanks - this has helped solve it.

 

[pbaldy]

Happy to help!

 

[jmsjazz]

Oops! Spoke too soon. The hyperlinks appear fine, but when I click on them in the subreport, sometimes the links work, and sometimes they don't.

On examining the table, I found by copying and pasting that the filenames which do not work have the characters ## at the end of the filename - e.g. C:\datafolder\Incident1.pdf## (the ## is not visible unless you copy and click paste. You can then delete the characters, and the whole thing works.)

There seems to be no pattern as to why the ## should have been added to the filename. I checked filename length, whether there were spaces etc.

Should I move this to a different thread?

 

[pbaldy]

What is the data type of the field? I use a plain text data type. I know the hyperlink data type stores the path with the # in some sort of pattern.

 

[jmsjazz]

The data I captured from the browse command was stored originally as VARIANT. On finding the error, I tried changing to String.

In the tblAttachments file, the data type is Hyperlink.

I included an IF statement in the Browse command to remove any trailing characters if they were "#", but that still did not work.

 

[pbaldy]

Like I said, I use Text as the data type, not Hyperlink. Then FollowHyperlink in the code to open a file.

 

[jmsjazz]

Thanks again - eventually got it using a subform with a listbox; on click, the pdf is opened using Call Shell. Harder than clicking the hyperlink, but I learned a lot!