dynamically change hyperlink address on form

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Novice User - 29 Mar 2008 19:49 GMT

I don't know if this is possible to do but maybe someone knows if and how to
do this. I have a form that pulls data from mutliple linked tables. On the
form I want a hyperlink to specific files located on the server. The
problem is that the files and location change depending on what record is
being edited. The main path to the folders\files is the same but the end
file changes. For example:

Record 101

\\server\service\pdfs\101 - (job name)\material order1.pdf
\\server\service\pdfs\101 - (job name)\material order2.pdf

Record 102

\\server\service\pdfs\102 - (job name)\material order1.pdf

Each folder that holds the material order information begins with the job
number. Most of the jobs will only have one material order in the folder,
some will have no material orders. All material orders a named "Material
Order" with the date as part of the file name. So, just to be complex I
guess, what I need to be able to do is have a hyperlink on the edit form that
when the user clicks on it it opens the material order (or orders) relevant
to that specific record (or job). I can easily enough just have a generic
hyperlink that opens the main folder when all the job material orders are
located and the user can then find the relevant folder for that job, but I
would like to elimate the time and step of having the user search through all
the folders and just have the like open the pertinent information. Anyone
know how to do this?

Thanks in advance for your help.

Reply to this Message

Arvin Meyer [MVP] - 30 Mar 2008 12:24 GMT

If you have spaces in your path, you'll need to surround that path with
quotes, and spaces in control and field names need to be surrounded by
square brackets. Try the following, using a command button:

Private Sub cmdLink_Click()
Me.cmdLink.HyperlinkAddress = "\\server\service\pdfs\101 - " &
Me.[txtJob Name]\" & Me.txtFileName
End Sub

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

>I don't know if this is possible to do but maybe someone knows if and how
>to
[quoted text clipped - 31 lines]
>
> Thanks in advance for your help.

Reply to this Message

Novice User - 30 Mar 2008 14:22 GMT

Arvin:

Thank you for your reply, this is all pretty new to me so I appreciate your
patience. I won't be able to try this until tomorrow when I have access to
the server. I'm not sure what I do with the jobs that have more than one
material order or that don't have any - any suggestions for these? They are
all pdf files, if they have more than one I would like for them to be able to
page through them with acrobat open before returning to the form. If there
isn't any then I would like the command button to be deactivated on that job
only. To be sure I am understanding your code suggestion (since I am not
familiar with the code) I am including what modifications I am understanding
I need to make to make it work in this scenario. Is there a set of quotes
missing from your example?

You wrote:
Private Sub cmdLink_Click()
Me.cmdLink.HyperlinkAddress = "\\server\service\pdfs\101 - " &
Me.[txtJob Name]\" & Me.txtFileName
End Sub

"cmdLink" will change to whatever the command button is named - in this case
matorder so:

Code to type:
Private Sub matorder_Click()
Me.matorder.HyperlinkAddress = "\\server\service\service pdfs\" &
Me.[ID] &"\
Material Order " & ".pdf"
End Sub

The job id is the first 4 characters of each folder on the server, which is
followed by the job name. Inside each of these folders is whatever material
orders apply. So I need to find the initial folder by the beginning job
number plus whatever the job name is and then open the material order inside
that folder - named Material Order xx-xx-xx.pdf. An example of the path
would be as follows:

\\server\service\service pdfs\1000 XYZ Fire Protection\Material Order
01-01-08.pdf
\\server\service\service pdfs\1001 ABC Repair Sprinkler\Material Order
02-15-08.pdf
\\server\service\service pdfs\1001 ABC Plumbing Underground\Material Order
02-17-08.pdf
etc

Are the modifications to the code correct?

> If you have spaces in your path, you'll need to surround that path with
> quotes, and spaces in control and field names need to be surrounded by
[quoted text clipped - 39 lines]
> >
> > Thanks in advance for your help.

Reply to this Message

Arvin Meyer [MVP] - 30 Mar 2008 15:19 GMT

The modifications look correct. Your code:

Me.[ID] &"\
Material Order " & ".pdf"

should have been on 1 line or have continuation underscores. I assume that
because of the indentation, it was not newsreader line wrapping, like mine.

As far as multiple records go, you will need to have a many-side table to
store the file names of all of them, if you want to see them.

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> Arvin:
>
[quoted text clipped - 111 lines]
>> >
>> > Thanks in advance for your help.

Reply to this Message

Novice User - 30 Mar 2008 15:30 GMT

thanks for your help, I'll give it a go tomorrow. I think rather than
building the table I will combine multiple pdf's into one - that will work
for what we are trying to do. the code was all on one line when I typed it
but separated when I sent the reply. Thanks again - I trust it will work
just as you indicated.

> The modifications look correct. Your code:
>
[quoted text clipped - 121 lines]
> >> >
> >> > Thanks in advance for your help.

Reply to this Message

Novice User - 31 Mar 2008 14:16 GMT

The pathing code that was supplied gets me there with this exception - the
job folder that contains the material order changes text in addition to the
job number - I don't have a table that ties the job name in anywhere. Can I
have it add whatever follows the first four characters? An asterick is
usually used to denote "whatever is there" in searches and such, can I use it
this way, or is there a way to grab that dymanically to add to the ID number
so it will come up with the folder name?

Me.[ID] & * &

> thanks for your help, I'll give it a go tomorrow. I think rather than
> building the table I will combine multiple pdf's into one - that will work
[quoted text clipped - 127 lines]
> > >> >
> > >> > Thanks in advance for your help.

Reply to this Message

Arvin Meyer [MVP] - 31 Mar 2008 15:28 GMT

Use:

Me.ID & Mid([WhateverYouHave],5)

which will grab everything starting with the 5th character from a
value/field named WhateverYouHave

Signature

Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

> The pathing code that was supplied gets me there with this exception - the
> job folder that contains the material order changes text in addition to
[quoted text clipped - 172 lines]
>> > >> >
>> > >> > Thanks in advance for your help.

Reply to this Message

Stockwell43 - 31 Mar 2008 16:54 GMT

Hi Novice User,

Arvin's idea is excellent and proper and that is what I would recommend.
However, to simplify just create a folder the houses all you Material Order
Folders. Then you can link to that folder and then the users can open it,
look for the job number folder and open it for the job order. Yes, it is not
a neat as Arvin's suggestion but as and alternative, you won't have to change
anything as your folder names change. Just a thought. Personally, I like
Arvin's method.

> I don't know if this is possible to do but maybe someone knows if and how to
> do this. I have a form that pulls data from mutliple linked tables. On the
[quoted text clipped - 26 lines]
>
> Thanks in advance for your help.

Reply to this Message