Linking to a file from a report

[MSACSNewb]

Total newb here.
I want to have a link to a PDF file from a report. In theory the file name
will be different for each record. Each record has a field for file name
whether or not there is a file available (blank or null).
All the files are in the same folder.
If I understand correctly, the path needs to be stored in the table holding
the file name.
I have different users entering the data in a form but I don't want them to
have to enter in the file path, just the file name. Also, I don't want the
file path displayed on the report, just the file name.
Is this done with an Input Mask on the form or is it done with some sort of
concatenation in the underlying table ("file path"+"user input"+".pdf")?
If this is addressed in the help files and you could provide some keywords I
would greatly appreciate it!
Total newb here.

PS; Access 2007

 

[MSACSNewb]

Bueller, Bueller...

 

[MSACSNewb]

Nothin'?
Am I missing something? Searched for quite a while using all the keywords I
could think of.

 

[John W. Vinson]

Total newb here.
I want to have a link to a PDF file from a report. In theory the file name
will be different for each record. Each record has a field for file name
whether or not there is a file available (blank or null).
All the files are in the same folder.
If I understand correctly, the path needs to be stored in the table holding
the file name.

Not really. You can concatenate the filename and the path later if there's a
need to do so.

I have different users entering the data in a form but I don't want them to
have to enter in the file path, just the file name. Also, I don't want the
file path displayed on the report, just the file name.
Is this done with an Input Mask on the form or is it done with some sort of
concatenation in the underlying table ("file path"+"user input"+".pdf")?

Neither. An Input Mask is VERY limited - it will just prevent the user from
typing incorrect characters, it won't let you add anything. And there's no
real benefit to storing this constant data in the table at all - it would just
waste space.

What will you want to have on the report? A printed sheet of paper with a
filename? a path and filename? a Hyperlink printed? A live hyperlink onscreen?
If you just want the full path and filename printed on the report, you can set
the Control Source of a textbox to

="D:\MyPdfFiles\" & [filename] & ".pdf"

It's more work if you want a Hyperlink but it's quite doable.

 

[MSACSNewb]

What will you want to have on the report? A printed sheet of paper with a
filename? a path and filename? a Hyperlink printed? A live hyperlink onscreen?
If you just want the full path and filename printed on the report, you can set
the Control Source of a textbox to

="D:\MyPdfFiles\" & [filename] & ".pdf"

It's more work if you want a Hyperlink but it's quite doable.

Thank you for replying!!!
The report will only ever be viewable onscreen. Currently it is
saved/printed as a static PDF. I then manually create links to the other PDF
files from the PDF file of the report.
We would like to integrate the report into our intranet; user clicks button
and runs report directly instead of opening PDF of report.
The hangup is losing the ability to open the other PDF files (engineering
drawings) from the report.
Hope this makes sense?
PS; if this is covered in a tutorial or in the help files if you could point
me in the right direction I would really appreciate it. I have learned way
more by the trial and error of doing that than any Access class I ever took!

 

[John W. Vinson]

What will you want to have on the report? A printed sheet of paper with a
filename? a path and filename? a Hyperlink printed? A live hyperlink onscreen?
If you just want the full path and filename printed on the report, you can set
the Control Source of a textbox to

="D:\MyPdfFiles\" & [filename] & ".pdf"

It's more work if you want a Hyperlink but it's quite doable.

Thank you for replying!!!
The report will only ever be viewable onscreen. Currently it is
saved/printed as a static PDF. I then manually create links to the other PDF
files from the PDF file of the report.
We would like to integrate the report into our intranet; user clicks button
and runs report directly instead of opening PDF of report.
The hangup is losing the ability to open the other PDF files (engineering
drawings) from the report.
Hope this makes sense?
PS; if this is covered in a tutorial or in the help files if you could point
me in the right direction I would really appreciate it. I have learned way
more by the trial and error of doing that than any Access class I ever took!

There's a Microsoft website that describes how to construct a hyperlink from
its components:

http://support.microsoft.com/kb/208200

It's for A2000 but it works in all versions. Try it out and post back with
your code if this isn't working for you.

What I DON'T know - and I don't believe it will - is whether you can
interactively click on a hyperlink in a Report in preview mode. Reports aren't
particularly interactive! It *MIGHT* be possible in A2007, but it does not
seem to be in 2003, based on my limited experimentation. If you're viewing
onscreen, a Form is a better choice - it certainly is interactive and a
properly constructed hyperlink will indeed work.

 

[MSACSNewb]

Thank you for replying!!!

There's a Microsoft website that describes how to construct a hyperlink from
its components:

http://support.microsoft.com/kb/208200

It's for A2000 but it works in all versions. Try it out and post back with
your code if this isn't working for you.

What I DON'T know - and I don't believe it will - is whether you can
interactively click on a hyperlink in a Report in preview mode. Reports aren't
particularly interactive! It *MIGHT* be possible in A2007, but it does not
seem to be in 2003, based on my limited experimentation. If you're viewing
onscreen, a Form is a better choice - it certainly is interactive and a
properly constructed hyperlink will indeed work.

I read the "HOW TO:" you linked to and did some searching around in there
using phrases from it. But I still didn't get what I was looking for. It
might be that I do not know what I am looking for...

I broke down and called the IT/IS manager and he sent one of his coding guys
down to give me a hand.
Here is what we came up with.
We started out in my form (I think because when he showed up I was trying to
figure out a way to store the hyperlink in a table?). I wanted the link to be
from the field that held the data but we were unable to do that. So we did a
"Insert Hyperlink". This required us to select an actual file to link to.
Once that functioned we went to - Event/On Click/Code Builder and created
this bit of code:
CADFILE.SetFocus
Label51.HyperlinkAddress = "\\xx\xx\xx\xx\Pdf\Drawings\" & CADFILE.Text &
".pdf"
CADFILE is the field holding the data and Label51 is the name Access gave
the hyperlink field.
I believe by putting this code for the "On Click" it overrides the actual
file path specified in "Hyperlink Address" on the "Format" tab?
This worked, but... I was hoping to get the text for the link to be the data
from the CADFILE field. We were able to make that work but only after you
clicked on the link ("On Mouse Up"?). I think the coding guy said we needed
something like "On Load" but that was not available. That was all the time he
had.
Today (01/25) I copied the hyperlink field from the form to my report. That
worked but it would only open the file specified in the "Hyperlink Address"
on the "Format" tab. I tried to delete that address (which still worked on my
form) but then the link stopped working alltogether.
So here is where I am at right now. I would prefer the link to be on the
field that already exists (CADFILE). A seperate field will work if nothing
else can be done though. I am sure I am close for that to work.

But in the back of my mind I am still thinking it would be better to have a
field (CADFILE) the user puts data in to, then the table stores that data in
conjunction with the rest of the "Hyperlink Data Type"
("DisplayText#Address#SubAddress" or
"#[userinput]#\\xx\xx\xx\xx\Pdf\Drawings\[userinput].pdf##" (no
"SubAddress")).
Am I dreaming here?

 

[MSACSNewb]

I wish I could edit my previous reply?!?!
I went back and looked through the link provided before. Instead of staying
on the microsoft site I used the embeded "Bing" search box by accident and
found some other resources.
Something triggered a long distance memory...
The structure of the Hyperlink Data Type = DisplayText#Address#SubAddress#?
(I have already forgotten what the ? was but I know I didn't need it, but not
accounting for it gave me a few hiccups)

My report is based on a query.
I decided that instead of holding the address in the table to try to
concatenate it in the query.
After some fiddling around in the help files I came to realise I needed an
expression. And more help file perusing to dial that in.
This is the expression I ended up with:
Expr1: [CAD FILE] & "#" & "\\xx\xx\xx\xx\Pdf\Drawings\" & [CAD FILE] &
".pdf" & "##"
It took a few trys to get all the "#" where they needed to be.

I deleted the "CAD FILE" field from the report and replaced it with the
field "Expr1".
TADA! I now have a link that opens the PDF files associated with the record!

Thank you Mr. Vinson!
PS; you were right that the link will not work in print preview, but it
works fine in report view.