Access 2003 File Search / Hyperlink difficulties

[Klingon Coder]

Hi,

I have a form in Access 2003 that has several command buttons that allow the
user to either open an existing hyperlink or assign one if there is not one
stored in the corresponding text boxes which have been set to invisible.

The problem I am having stems from one command button, which needs to
perform mulitiple tasks.

First: I need to check if a hyperlink has been stored and if so open it.
(no difficulties with this part)

Second: If no hyperlink is stored in the text box I need to search a
specific directory for an Excel file that contains part of the primary key
listed on my form as the file name i.e. PrimaryKey_Form.xls. (no clue on
this)

Third: If the file exists I need to add the hyperlink to the text box and
open the file. (clueless here too)

Fourth: If the file does not exist, I need to be able to open Form.xls,
rename it to PrimaryKey_Form.xls and then carry out the Third step.

I am hoping someone can explain how I can go about achieving these goals or
at least point me in the right direction. I should warn you that my VBA
skills are limited and quite rusty, so if you provide code examples, it would
be helpful if you can provide comments to explain what is happening.

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Wayne Morgan]

1) You say you know how to do this part.

2) If you know the directory ahead of time, you can use the Dir() function
to verify that the file exists. If it does, the function will return the
name of the file. If it doesn't, the function will return an empty string
(""). If you don't know the directory, you may want to browse for the file.
In that case, this article should help:
http://www.mvps.org/access/api/api0001.htm.

3) The second part of this one is the same as #1. Once you add the
hyperlink, just do what you do for #1. To add the hyperlink to the textbox,
you would take the result from #2 and assign it as the value of the textbox.
This assumes that the control is bound to a hyperlink field in a table. You
will need to save the record after you make this change.

Example:
Me.txtMyTextbox = "#C:\Path\PrimaryKey_Form.xls#"
Me.Dirty = False 'save the record

4) Do you need to be able to open Form.xls or do you just need to copy or
rename it? To copy it, use the FileCopy statement and to rename it use the
Name statement.

Example:
FileCopy "c:\test\t.txt" "c:\test\t2.txt"
Name "c:\test\t.txt" As "c:\test\t3.txt"

If you change the directory path when you rename the file, it will "move"
the file to the new directory path and use the new name. To move the file
only, just use the same name with the new path.

 

[Klingon Coder]

Hi,

Thanks for your reply. Your answers look like they will do the trick, and I
will let you know after I have tried them out. But first I would like to
clarify several points.

1) As I mentioned I have several buttons that check for hyperlinks and all
of them use the code shown below (renamed for the appropriate button and text
boxes of course). Everything works fine for the other buttons, but as per my
previous post this button needs to have more functionality. Here is the code
that I am currently using:

Private Sub cmdStrategy_Click()
Dim Response, MyString
On Error GoTo cmdStrategy_Click_Error

If ValidLink(Me![txtBidStrategy]) = False Then

Response = MsgBox("There is no bid proposal attached to this RFP.
Would you like to start one now?", vbYesNo, "Open Bid Proposal")

If Response = vbYes Then
txtBidStrategy.Visible = True
txtBidStrategy.SetFocus
DoCmd.RunCommand acCmdInsertHyperlink
cmdStrategy.SetFocus
txtBidStrategy.Visible = False
Hyper_Colours 'changes colour of button caption to indicate
there is a link
Else
MyString = "No"
End If

End If

exit_cmdStrategy_Click:
Exit Sub

cmdStrategy_Click_Error:
If Err <> 2501 Then
MsgBox Err & ": " & Err.Description
Resume exit_cmdStrategy_Click
End If
End Sub

Of course I will have to modify the code to perform the other actions.

2) Yes I do know which directory I want to search, but I will want to limit
the search to only .xls files not the .doc and .pdf files that are going to
be in there as well. I assume this will be done in the argument section of
the Dir() function?

3) Yes the hyperlinks are stored in a bound field on the underlying table,
so your example makes perfect sense. The question now is; once the new link
has been assigned to the text box, how will I go about opening the linked
file without having to re-click the command button?

4) Actually, no I don't need to open the Form.xls, what I should have typed
was that I need to make a copy of it, and the copy should be renamed
PrimaryKey_Form.xls
So if I understand your example correctly, if I were to use:

FileCopy "c:\test\form.xls" "c:\test\newDir\PrimaryKey_form.xls"

that would result in the new file being created and moved to the appropriate
directory? If so, then I assume I use your example code to assign the
hyperlink to the appropriate text box?

If I am off the mark I would appreciate your corrections, and thanks again
for your help.

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Wayne Morgan]

2) Yes.

3) In your If statement where you check to see if the hyperlink is there, if
it's not you add it. After you add it, just run the same code you run now to
activate it. Just copy the code you would have used had the hyperlink
already been there and paste it into the If statement after the part where
you add the hyperlink.

4) Yes.

--
Wayne Morgan
MS Access MVP

Hi,

Thanks for your reply. Your answers look like they will do the trick, and
I
will let you know after I have tried them out. But first I would like to
clarify several points.

1) As I mentioned I have several buttons that check for hyperlinks and all
of them use the code shown below (renamed for the appropriate button and
text
boxes of course). Everything works fine for the other buttons, but as per
my
previous post this button needs to have more functionality. Here is the
code
that I am currently using:

Private Sub cmdStrategy_Click()
Dim Response, MyString
On Error GoTo cmdStrategy_Click_Error

If ValidLink(Me![txtBidStrategy]) = False Then

Response = MsgBox("There is no bid proposal attached to this RFP.
Would you like to start one now?", vbYesNo, "Open Bid Proposal")

If Response = vbYes Then
txtBidStrategy.Visible = True
txtBidStrategy.SetFocus
DoCmd.RunCommand acCmdInsertHyperlink
cmdStrategy.SetFocus
txtBidStrategy.Visible = False
Hyper_Colours 'changes colour of button caption to indicate
there is a link
Else
MyString = "No"
End If

End If

exit_cmdStrategy_Click:
Exit Sub

cmdStrategy_Click_Error:
If Err <> 2501 Then
MsgBox Err & ": " & Err.Description
Resume exit_cmdStrategy_Click
End If
End Sub

Of course I will have to modify the code to perform the other actions.

2) Yes I do know which directory I want to search, but I will want to
limit
the search to only .xls files not the .doc and .pdf files that are going
to
be in there as well. I assume this will be done in the argument section
of
the Dir() function?

3) Yes the hyperlinks are stored in a bound field on the underlying table,
so your example makes perfect sense. The question now is; once the new
link
has been assigned to the text box, how will I go about opening the linked
file without having to re-click the command button?

4) Actually, no I don't need to open the Form.xls, what I should have
typed
was that I need to make a copy of it, and the copy should be renamed
PrimaryKey_Form.xls
So if I understand your example correctly, if I were to use:

FileCopy "c:\test\form.xls" "c:\test\newDir\PrimaryKey_form.xls"

that would result in the new file being created and moved to the
appropriate
directory? If so, then I assume I use your example code to assign the
hyperlink to the appropriate text box?

If I am off the mark I would appreciate your corrections, and thanks again
for your help.

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

1) You say you know how to do this part.

2) If you know the directory ahead of time, you can use the Dir()
function
to verify that the file exists. If it does, the function will return the
name of the file. If it doesn't, the function will return an empty string
(""). If you don't know the directory, you may want to browse for the
file.
In that case, this article should help:
http://www.mvps.org/access/api/api0001.htm.

3) The second part of this one is the same as #1. Once you add the
hyperlink, just do what you do for #1. To add the hyperlink to the
textbox,
you would take the result from #2 and assign it as the value of the
textbox.
This assumes that the control is bound to a hyperlink field in a table.
You
will need to save the record after you make this change.

Example:
Me.txtMyTextbox = "#C:\Path\PrimaryKey_Form.xls#"
Me.Dirty = False 'save the record

4) Do you need to be able to open Form.xls or do you just need to copy or
rename it? To copy it, use the FileCopy statement and to rename it use
the
Name statement.

Example:
FileCopy "c:\test\t.txt" "c:\test\t2.txt"
Name "c:\test\t.txt" As "c:\test\t3.txt"

If you change the directory path when you rename the file, it will "move"
the file to the new directory path and use the new name. To move the file
only, just use the same name with the new path.

 

[Klingon Coder]

Thanks Wayne for all your help with this problem. As I had reported
everything worked out fine.

The thing that we discovered is that when the front end is located in the
same location as the back end and the files that we are trying to link to,
everything works without a hitch. The hyperlink is added and the file opens,
or the file is created, the link is added and the new file opens, as per the
code. Of course once the users place the front end on their own pc, then the
whole process goes out the window because the source file and the folders
that are required for storing the new file are no longer in the same
directory as the front end.

While we could hard code the pathway for the files/folders, what we are
wanting to do is see if there is a way to have the database use a relative
link to find where the back end is when it is searching for the source file.
This is important as we may need to relocate the backend later and obviously
don't want to have to recode the form when this happens. So the question
is...Is this possible and if so, can someone point me in the right direction.


--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Wayne Morgan]

You are wanting a "relative link" off of the location of the back-end, so
the file will be in the same or a subfolder of where the back-end is
located, correct?

If so, you need to get the current path to the back-end then concatenate the
relative portion of the path to this. To get the path to the back-end, it is
probably easiest to get the Connect property of one of the linked tables in
the front-end. It is possible to have more than one back-end file, so all
the tables may not be linked to the same place. You will have to choose a
table that is linked to the back-end desired. To get the path from the
linked table:

The Connect property will return ;DATABASE=<Path>\FileName.mdb. You need to
strip off ";DATABASE=" and "FileName.mdb" to get the path to the back-end.

strBackEndPath = Mid(CurrentDb.TableDefs("tblTableName").Connect, 11)
strBackEndPath = Left(strBackEndPath, InStrRev(strBackEndPath, "\"))

 

[Klingon Coder]

Hi Wayne,

Thanks a million, your code worked like a charm. There is an odd situation
that occurs with the file when it opens. The exel file has macros so when
the file opens the user is prompted as to whether or not they want to allow
macros, and when they answer yes the file opens but then they get the error
message "Reference is not valid". This message comes up twice and then the
file is good to go. This only happens when the file is opened by the command
button. If you open the file from windows explorer, or if you click the
hyperlink in the table itself the file opens without issue, but if the button
on the form is used, the error message comes up twice. Any ideas why this is
happening? The file is NOT linked to any other file, nor is it linked to the
database other than by the hyperlink. If this is not your area of expertise
I understand and can always post the question in the appropriate forum. If
on the other hand you can help clarify the situation, I would appreciate it.

--
Cheers

Klingon Coder

"Ancient Klingon Proverb - Act and you shall have dinner, Think and you
shall be dinner"

 

[Wayne Morgan]

I really don't know. If the path to the file is a UNC path (i.e.
\\ComputerName\ShareName) you may be getting caught by IE trying to protect
you from a script running on the Internet.