Hyperlink to a File Name with a Changing Revision Number 3

[uncleG]

I have a HyperLink on my form that requires the ability to ignore or adapt to the revision number of the target file name. I do not expect the rev # to ever exceed 99, the "rev" and ".doc" are constant.

Link Example on Local Machine:
AS9100/Job Description/John Brown rev 2.doc may change in the future to
AS9100/Job Description/John Brown rev 3.doc

John Brown is supplied from the form as & Me![CoName]

What must I add to the Following code to allow for the rev X.doc or rev XX.doc?

OR Would Find the Max Rev # be a little more fool proof in the long run?

Link Example on Network:
Application.FollowHyperlink "\\Wendy2\AS9100\Job Descriptions\" & Me![CoName]

Thanks,
UncleG

 

[PHV]

You may try something like this:
strDir = "\\Wendy2\AS9100\Job Descriptions\"
Application.FollowHyperlink strDir & Dir(strDir & Me![CoName] & "*.doc")


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dRahme]

Hello, you could try obtaining a match using the 'Like' operator first and build a string from there:

Dim Myfile

Myfile = Dir("\\Wendy2\AS9100\Job Descriptions\" & CoName & "*.*")

Application.FollowHyperlink Myfile

Or a bit more foolproof:

Build your string for the comboBox Selection in the combo's OnEnter Event using the contents of the directory:

Private Sub CoName_Enter()

Myfile = Dir("\\Wendy2\AS9100\Job Descriptions\*.doc*")

stsql = Myfile & ";"

Do While Not (Myfile = "")

Myfile = Dir

stsql = stsql & Myfile & ";"

Loop

Me.CoName.RowSource = stsql

End Sub

That will obtain your list of file names. Then in the after update event of the combo build the rest of the string for Follow Hyperlink.

CoName AfterUpdate:

dim MyDir as String

MyDir = ("\\Wendy2\AS9100\Job Descriptions\")

Application.FollowHyperlink MyDir & CoName

Hope that helps

 

[uncleG]

Thanks PHV and dRahme, I will try the network versions when I am back in the office to see which is better suited.
Uncle G

 

[uncleG]

The Solution fires off a command button using the code as follows and allows for Changes in the rev #.
If a matching file does not exist in the folder the link leaves you at the top of the target directory.

***********
Code
Private Sub Command119_Click()
On Error GoTo Err_Command119_Click

strDir = "\\Wendy2\AS9100\Job Descriptions\"
Application.FollowHyperlink strDir & Dir(strDir & Me![CoName] & " rev *.doc")

Exit_Command119_Click:
Exit Sub

Err_Command119_Click:
MsgBox Err.Description
Resume Exit_Command119_Click

End Sub
******************
Thanks Again PHV and dRahme

uncleG