Ok, another problem. I'm using Access 2010.

Using the information from this site http://access.mvps.org/access/api/api0002.htm I was able to get a browse directory to open and search and locate the file that I want to hyperlink to in my form. The only issue is that now is that if you click on the link nothing happens. I can't get it to open up the PDF file like I want it too. I've search the internet and everyone is talking about how to set up the browse directory but I can't seem to find a single solution to actually getting the hyperlink to work. What's the point of having the directory if the hyperlink doesn't actually take you there, open the file? Help please. I know I'm asking a lot of questions but that's because I'm an idiot.

Thanks.

Hi,

Maybe if you could post the code you're using, we can tell you if there's anything wrong with it?

Just my 2 cents...

Ok, here are the codes. First this is the code on double click.

Private Sub Document_DblClick(Cancel As Integer)

Dim strFilter As String
Dim strInputFileName As String

strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)


Me.Document = strInputFileName
End Sub

And this is the code for the Module.

Option Compare Database
Option Explicit

Private Declare Function ts_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (tsFN As tsFileName) As Boolean

Private Declare Function ts_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (tsFN As tsFileName) As Boolean

Private Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Private Type tsFileName
lStructSize As Long
hwndOwner As Long
hInstance As Long
strFilter As String
strCustomFilter As String
nMaxCustFilter As Long
nFilterIndex As Long
strFile As String
nMaxFile As Long
strFileTitle As String
nMaxFileTitle As Long
strInitialDir As String
strTitle As String
Flags As Long
nFileOffset As Integer
nFileExtension As Integer
strDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
End Type

' Flag Constants
Public Const tscFNAllowMultiSelect = &H200
Public Const tscFNCreatePrompt = &H2000
Public Const tscFNExplorer = &H80000
Public Const tscFNExtensionDifferent = &H400
Public Const tscFNFileMustExist = &H1000
Public Const tscFNPathMustExist = &H800
Public Const tscFNNoValidate = &H100
Public Const tscFNHelpButton = &H10
Public Const tscFNHideReadOnly = &H4
Public Const tscFNLongNames = &H200000
Public Const tscFNNoLongNames = &H40000
Public Const tscFNNoChangeDir = &H8
Public Const tscFNReadOnly = &H1
Public Const tscFNOverwritePrompt = &H2
Public Const tscFNShareAware = &H4000
Public Const tscFNNoReadOnlyReturn = &H8000
Public Const tscFNNoDereferenceLinks = &H100000

Public Function tsGetFileFromUser( _
Optional ByRef rlngflags As Long = 0&, _
Optional ByVal strInitialDir As String = "", _
Optional ByVal strFilter As String = "All Files (*.*)" & vbNullChar & "*.*", _
Optional ByVal lngFilterIndex As Long = 1, _
Optional ByVal strDefaultExt As String = "", _
Optional ByVal strFileName As String = "", _
Optional ByVal strDialogTitle As String = "", _
Optional ByVal fOpenFile As Boolean = True) As Variant
On Error GoTo tsGetFileFromUser_Err

Dim tsFN As tsFileName
Dim strFileTitle As String
Dim fResult As Boolean

' Allocate string space for the returned strings.
strFileName = Left(strFileName & String(256, 0), 256)
strFileTitle = String(256, 0)

' Set up the data structure before you call the function
With tsFN
.lStructSize = Len(tsFN)
.hwndOwner = Application.hWndAccessApp
.strFilter = strFilter
.nFilterIndex = lngFilterIndex
.strFile = strFileName
.nMaxFile = Len(strFileName)
.strFileTitle = strFileTitle
.nMaxFileTitle = Len(strFileTitle)
.strTitle = strDialogTitle
.Flags = rlngflags
.strDefExt = strDefaultExt
.strInitialDir = strInitialDir
.hInstance = 0
.strCustomFilter = String(255, 0)
.nMaxCustFilter = 255
.lpfnHook = 0
End With

' Call the function in the windows API
If fOpenFile Then
fResult = ts_apiGetOpenFileName(tsFN)
Else
fResult = ts_apiGetSaveFileName(tsFN)
End If

' If the function call was successful, return the FileName chosen
' by the user. Otherwise return null. Note, the CancelError property
' used by the ActiveX Common Dialog control is not needed. If the
' user presses Cancel, this function will return Null.
If fResult Then
rlngflags = tsFN.Flags
tsGetFileFromUser = tsTrimNull(tsFN.strFile)
Else
tsGetFileFromUser = Null
End If

tsGetFileFromUser_End:
On Error GoTo 0
Exit Function

tsGetFileFromUser_Err:
Beep
MsgBox Err.Description, , "Error: " & Err.Number _
& " in function basBrowseFiles.tsGetFileFromUser"
Resume tsGetFileFromUser_End

End Function

' Trim Nulls from a string returned by an API call.
Private Function tsTrimNull(ByVal strItem As String) As String
On Error GoTo tsTrimNull_Err
Dim I As Integer

I = InStr(strItem, vbNullChar)
If I > 0 Then
tsTrimNull = Left(strItem, I - 1)
Else
tsTrimNull = strItem
End If

tsTrimNull_End:
On Error GoTo 0
Exit Function

tsTrimNull_Err:
Beep
MsgBox Err.Description, , "Error: " & Err.Number _
& " in function basBrowseFiles.tsTrimNull"
Resume tsTrimNull_End

End Function

Public Sub tsGetFileFromUserTest()
On Error GoTo tsGetFileFromUserTest_Err

Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant

' strFilter = "Access (*.mdb)" & vbNullChar & "*.mdb" _
' & vbNullChar & "All Files (*.*)" & vbNullChar & "*.*"
strFilter = "All Files (*.*)" & vbNullChar & "*.*"

lngFlags = tscFNPathMustExist Or tscFNFileMustExist Or tscFNHideReadOnly

varFileName = tsGetFileFromUser( _
fOpenFile:=True, _
strFilter:=strFilter, _
rlngflags:=lngFlags, _
strDialogTitle:="GetFileFromUser Test (Please choose a file)")

If IsNull(varFileName) Then
Debug.Print "User pressed 'Cancel'."
Else
Debug.Print varFileName
'Forms![Form1]![Text1] = varFileName
End If

If varFileName <> "" Then MsgBox "You selected the '" & varFileName & "' file.", vbInformation

tsGetFileFromUserTest_End:
On Error GoTo 0
Exit Sub

tsGetFileFromUserTest_Err:
Beep
MsgBox Err.Description, , "Error: " & Err.Number _
& " in sub basBrowseFiles.tsGetFileFromUserTest"
Resume tsGetFileFromUserTest_End

End Sub


The browse directory comes up but even though it's set to Hyperlink I can't click on it for it to bring up the file. What I was hoping was that on double click on the field so that the browse directory will come up and a single click will open the PDF file...

Hi,


Are you saying that the Document field is a Hyperlink data type? Or, are you saying that the IsHyperlink property of the textbox is set to Yes?

Click to view attachment Sorry for not be specific. I meant that in the table, under "Data Type" it's set to Hyperlink which in terms sets the IsHyperlink property of the textbox set to Yes.

I've attached a stripped down version of my database with only this issue on the form.

Hi,

I believe for something to be considered a true hyperlink, it has to include the protocol. Just having a filepath in the textbox does not make it a hyperlink.

Try the following:

1. Enter a web address in the Document textbox, for example, http://www.utteraccess.com
2. Save the record and then click on the text inside the textbox. Your browser should open to the UA website.

If you don't plan on using web addresses in your database, I would suggest not using a Hyperlink data type and add a Click event to your Document textbox using the FollowHyperlink method.

For example:

Application.FollowHyperlink Me.Document

If you still want to use a Hyperlink data type, then you'll need to add the protocol to the file path before storing it in the field.

Just my 2 cents...

Fan-freakin-tastic idea. It worked perfectly. Kudos to you my good sir. Kudos to you.

Ok... it just worked but now it's giving me and Invalid use of Null error message. Here's my code.

Private Sub Document_Click()
Application.FollowHyperlink Me.Document
End Sub

Try changing it to:

Application.FollowHyperlink Nz(Me.Document,"")

Just my 2 cents...

That works! Thanks for the quick response.

Hi,



Glad to hear it worked for you. Good luck with your project.

I might have to try this theDBguy! I non-elegantly solved this by having two fields, one where I manually input file:// followed by the folder path in one field and then used an afterupdate event to make a second field (that was set to Hyperlink in the table) equal to the first field. This worked out well for me as I used the text only field later on some other vba code for creating an html hyperlink in an Outlook email.

Hi stickmankm,



Actually, since you knew the protocol needed for the hyperlink to work, I think you could just add it to the filepath and store it in a Hyperlink field, and it should work without needing a Click event and the FollowHyperlink method. For example (using tobabygu's code):

Me.Document = "file://" & strInputFileName

(untested)
Just my 2 cents...

Thanks for the warm welcome! I'm finding the place quite cozy and love the support so far.

Not sure if I follow (I'm a little slow most of the time...okay all of the time). So would I only have the one field with an AfterUpdate event of:

Private Sub Document_AfterUpdate()
strInputFileName = path of my folder or document
Me.Document = "file://" & strInputFileName
Application.FollowHyperlink Me.Document
End Sub

My coding is very beginner at best, so sorry for the frustratingly easy question.

Hi,

Yes, I think you only need one field and you probably don't even need an AfterUpdate event.

In tobabygu's code, he/she is using the textbox Double-Click event. If you're using the same thing, then that's all you need.

The user double-clicks on the textbox, the file dialog opens up, user selects the file, your code adds the protocol to the file's path, and voila, when the user clicks on it (because it's a hyperlink field), the file opens up.

Just my 2 cents...

Oooh, that is nice! I am going to try that tonight hopefully when I get a chance.

Okay, let us know how it goes... Good luck!

Oh, this is awesome! Thanks so much to both tobabygu and theDBguy. Is there a way to easily change the code to accept a folder path instead of a file?

My current application hyperlinks a folder path so other users can quickly access a project folder full of different project files. My coding is limited to simple if-thens, equations and....well that's about it.

Have a great weekend guys.

Yes, instead of the code that tobabygu posted, use this one:

API: BrowseFolder Dialog

Cheers

Man, is there anything you don't know? That worked fantastically. I have one last question. I want to have the dialog box open to a fixed default folder location. Clicking on the Stephan Lebans link from the link you provided discusses this and provides code to do this, but it seems to be way overkill for what I need and requires extra forms, buttons and text boxes to execute. I have only one fixed folder that I would like the dialog box to open to so I thought perhaps it is easily implemented into the code from the link you provided. I was thinking perhaps if changing szPath to equal my folder path that it might do the trick, but to no avail. Any ideas? If not, I will happily ride off in the sunset on this topic with some major thanks to you!

Hi,

Unfortunately, the only way to open the folder picker API to a specific directory is how Lebans showed it in his version.

Fortunately, another option for a folder picker is to use a FileDialog object.

If you're using Acc2003 and earlier, the FileDialog object is a separate ActiveX component. If you are using Acc2007 or later, the FileDialog is now included in the Access Objects library.

Take a look at this MSDN article for more info.

Just my 2 cents...

What I did was I put the codes in the buttons on the actual textbox so that I won't need buttons or what not just the textbox. That's why I wanted it to seperated into "Click" activity and "Double Click" activity.

Hi tobabygu,

What do you mean by "buttons on the actual textbox?"

Just curious...