Hyperlink 2

[Nene75]

Hi Everyone,

I have a form with a Hyperlink field to link the word document. When user filling out the form how to have the file brower open up and user can select the file and then show the path on the hypelink field?

Any help provided would be appreciated.

Thanks!

 

[janetlyn]

Nene75, I use hyperlinks extensively. However, I am using the one in the property box and I have to put the link in myself.

Here is how I am interpreting what you are asking:

A form with a field, has a hyperlink in the property that goes to a Word document.

A user is filling out a form with this field included. From there on, I do not understand what you are asking.

Can you give more details or specific steps for what you are doing and I will try to help from there?

Usually when someone does not answer a thread it is because they do not understand what you are asking.

Janet Lyn

 

[Nene75]

Thanks Janet for replying.

Let me try my best to explain it better ...

I have created a field (hyperlink databype) for user to enter the file path. But users are not sure what to do when they get to that field - because it looks just like a regular textbox. They prefer to have a browser to open up in order to select the file and then once the file is selected, the file name and path should show up in that field. I have made it a hyperlink datatype because once the data is entered in to the system, records are locked but users can just clik on the link to view the document.

Please let me know if this is possible to do or is there any better way to solve this problem?

Thanks in advance!

 

[janetlyn]

Nene, there may be a way to get it to open a browser but I do not know how. However, It would be very easy to have an event that when the user gets to the hyperlink field it opens a message telling them how to input. For example:

Private Sub Hypertextbox_GotFocus()
MsgBox "Enter your hyperlink using the following format

http://"

End Sub

Then you could have the message box close on LostFocus of same textbox.

I hope this helps a little.

You are specifically looking for a browser to open start a new thread with "Open Hyperlink Browser" as the subject so the smart ones will know what you are looking for.

Janet Lyn

Janet Lyn

 

[Glohamar]

Hi,

You might try this. The following code will let you browse for a file. Right now it is only looking for access databases, but should be able to modify it to look for any document. Set the text box to store the file link as a hyperlink and it should work. I will need more time to test my thinking. If I get a chance to try I will post back with my findings.

Dave

Option Compare Database
Option Explicit
Private Type OPENFILENAME
lStructSize As Long
hwnd As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

Private Declare Function GetOpenFileName _
Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
(pOpenfilename As OPENFILENAME) As Long

Private Declare Function GetSaveFileName _
Lib "comdlg32.dll" Alias "GetSaveFileNameA" _
(pOpenfilename As OPENFILENAME) As Long

Private Const OFN_FILEMUSTEXIST = &H1000
Private Const OFN_HIDEREADONLY = &H4
Private Const OFN_OVERWRITEPROMPT = &H2
Private Const OFN_PATHMUSTEXIST = &H800
Private Const OFN_SAVE = 0
Private Const OFN_OPEN = 1

Private Sub Button2_Click()
On Error GoTo ErrHandler

With Me.yourtextbox_in_which_to_save_link_to_file
.Value = DialogFile(1, _
"Please select an Access database...", _
vbNullString, _
"Access Databases (*.mda, *.mdb, *.mde)|*.mda;*.mde;*.mdb", _
CurDir(), _
vbNullString)
'If Len(.Value) Then
'Me.chkUserRoster.Enabled = fIsJet4DB(.Value)
'Call cmdExecute_Click
'End If
End With
ExitHere:
Exit Sub
ErrHandler:
With Err
MsgBox "Error: " & .Number & vbCrLf & .Description, _
vbCritical Or vbOKOnly, .Source
End With
Resume ExitHere
End Sub
Private Function DialogFile(wMode As Integer, szDialogTitle As String, szFileName As String, szFilter As String, szDefDir As String, szDefExt As String) As String
Dim x As Long, OFN As OPENFILENAME, szFile As String, szFileTitle As String
With OFN
.lStructSize = Len(OFN)
.hwnd = Me.hwnd
.lpstrTitle = szDialogTitle
.lpstrFile = szFileName & String$(250 - Len(szFileName), 0)
.nMaxFile = 255
.lpstrFileTitle = String$(255, 0)
.nMaxFileTitle = 255
.lpstrFilter = NullSepString(szFilter)
.nFilterIndex = 2
.lpstrInitialDir = szDefDir
.lpstrDefExt = szDefExt
If wMode = 1 Then
OFN.Flags = OFN_HIDEREADONLY Or OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST
x = GetOpenFileName(OFN)
Else
OFN.Flags = OFN_HIDEREADONLY Or OFN_OVERWRITEPROMPT Or OFN_PATHMUSTEXIST
x = GetSaveFileName(OFN)
End If
If x <> 0 Then
If InStr(.lpstrFile, Chr$(0)) > 0 Then
szFile = Left$(.lpstrFile, InStr(.lpstrFile, Chr$(0)) - 1)
End If
DialogFile = szFile
Else
DialogFile = &quot;&quot;
End If
End With
End Function
Private Function fIsJet4DB(strMDB As String) As Boolean
On Error GoTo ErrHandler
Dim cnn As Object
Set cnn = CreateObject(&quot;ADODB.Connection&quot
With cnn
.ConnectionString = &quot;Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;&quot; _
& &quot;Data Source=&quot; & strMDB & &quot;;Mode=Share Deny None;&quot;
.Open
fIsJet4DB = (cnn.Properties(&quot;Jet OLEDB:Engine Type&quot = 5)
End With
ExitHere:
On Error Resume Next
cnn.Close
Set cnn = Nothing
Exit Function
ErrHandler:
With Err
MsgBox &quot;Error Number: &quot; & .Number & vbCrLf & .Description, _
vbCritical Or vbOKOnly, .Source
End With
Resume ExitHere
End Function
'Pass a &quot;|&quot; separated string and returns a Null separated string
Private Function NullSepString(ByVal CommaString As String) As String
Dim intInstr As Integer
Const vbBar = &quot;|&quot;
Do
intInstr = InStr(CommaString, vbBar)
If intInstr > 0 Then Mid$(CommaString, intInstr, 1) = vbNullChar
Loop While intInstr > 0
NullSepString = CommaString
End Function

 

[Glohamar]

OK. Ignore what I have post before this one, and use what I have found below.

Goto the following link to get the code to open a dialog box to find a file...

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

Use that code to create a new module and save it.

Now, on your form create a command button with the following. The text box is to store the file location in your table.

Private Sub Command2_Click()
Me.Text0 = ahtCommonFileOpenSave
End Sub

I created another command button to open the file in the text box. For some reason I was unable to just click on the linked file and it open. Anyway, the following is my code to view the file...

Private Sub cmdView_Click()
cmdView.HyperlinkAddress = Me.Text0
End Sub

This should get you what you are after.

Any questions let me know. I have tried this and it is working for me.

Dave

 

[janetlyn]

Dave, thanks for the input. I was in way over my head, but no one was helping her, so I thought if she could clarify that might help. Your great. Star to ya for jumping in. JL

 

[Glohamar]

Thanks janetlyn. I appreciate it.

 

[Nene75]

Dave and Janet:

Thanks for putting interest and effort on solving the hyperlink problem.

Thanks a lot Dave for the code and testing it at your end.

It works great. I love it. And sure you deserve lots of stars for it. Thanks!

I have a one more thing to ask. Once the file has been opened and then closed, Why does the form and Access Application minimizes? Is there a way to keep them maximized?

 

[Glohamar]

Because it is a link to a file, it is being treated as a hyperlink. You will notice that in the document that you open, the Web toolbar is activated. If you click the back button on that tool bar, the Access window will not be minimized, otherwise it will be every time. Take a look at the KB Article from Microsoft. I know you are not opening an Internet browser window, but you are still hyper linking to a file.

http://support.microsoft.com/default.aspx?scid=kb;en-us;240962&Product=acc2000

Happy to hear everything worked for you. Thanks for the star.

Dave

 

[Nene75]

Thanks Dave:

Yes I noticed the browser menu toolbar on top of the documents that are hyperlinked. You're right if I click the back button on the toolbar, Access window does not minimized but the document still remains opened. I guess there is no workaround for this problem. Thanks for sharing the link for KB article.

I really appreciated all your help. You provided great help with thorough explainations. Thanks! Here's a STAR for you!