Change from early to late binding in access form

[dhyangya]

Hi All,

I just joined the forum today. I use various databases but was never "formally" schooled. I can write some vba but nothing too advanced.

Here is my issue and code I got from a website describing how to add a hyperlink button to my form that will open the windows "Open File" dialog box. The user would then find the file they want to hyperlink to and that hyperlink would show in a textbox which is bound to a table.

The code works quite well with the exception of the fact that the "Microsoft Office Object" library needs to be checked as a reference due to the early binding.

We use Access 2007, 10, 13 and 16 throughout the company so I can't go to each pc and check the reference in the ide. Because of this, I read that I would need to change the code to use late binding but I can't figure out how to rewrite it. There is one sub routine and one function that gets called.

Here is the Sub:

Private Sub txtbrowse_Click()

Dim strButtonCaption As String
Dim strDialogTitle As String
Dim strHyperlinkFile As String
Dim strSelectedFile As String

'Define your own Captions if necessary
strButtonCaption = "Save Hyperlink"
strDialogTitle = "Select File to Create Hyperlink to"

With Application.FileDialog(msoFileDialogFilePicker)
With .Filters
.Clear
.Add "All Files", "*.*" 'Allow ALL File types
End With
'The Show Method returns True if 1 or more files are selected
.AllowMultiSelect = False 'Critical Line
.FilterIndex = 1 'Database files
.ButtonName = strButtonCaption
.InitialFileName = vbNullString
.InitialView = msoFileDialogViewDetails 'Detailed View
.Title = strDialogTitle
If .Show Then
For Each varItem In .SelectedItems 'There will only be 1
'Display Text + # + Address of Hyperlink (Display Text#Hyperlink Address)
strSelectedFile = varItem
strHyperlinkFile = fGetBaseFileName(strSelectedFile) & "#" & strSelectedFile
Me![txtPicHyper] = strHyperlinkFile
Next varItem
End If
End With

End Sub


-----------------------------------------
Here is the function:

Public Function fGetBaseFileName(strFilePath As String) As String
'This Function accepts the Absolute Path to a File and returns the Base File
'Name (File Name without the Extension)

'Make absolutely sure that it is a valid Path/Filename
If Dir$(strFilePath) = "" Then Exit Function

Dim strFileName As String
Dim strBaseFileName As String

strFileName = Right$(strFilePath, Len(strFilePath) - InStrRev(strFilePath, ""))

strBaseFileName = Left$(strFileName, InStr(strFileName, ".") - 1)
fGetBaseFileName = strBaseFileName
End Function


Any help would be greatly appreciated!

 

[MrHans]

Hi Dhyangya, welcome to the forum.
I'm not entirely sure, but I think you can use the code from the following URL to set a reference to the required DLL using code and for different versions of Office.

http://gainingaccess.net/GainingAccess/CodeDownloads/FolderDialog.htm

I hope it helps.

 

[ByteMyzer]

For the txtbrowse_Click sub, I recommend the following edit. This should help you avoid the necessity for the additional Reference:

[COLOR="Navy"]Private Sub[/COLOR] txtbrowse_Click()

[B][I]
[COLOR="DarkGreen"]' Add these two lines[/COLOR]
[COLOR="navy"]Const[/COLOR] msoFileDialogFilePicker = 3
[COLOR="navy"]Const[/COLOR] msoFileDialogViewDetails = 2
[/I][/B]
[COLOR="navy"]Dim[/COLOR] strButtonCaption [COLOR="navy"]As String
Dim[/COLOR] strDialogTitle [COLOR="navy"]As String[/COLOR]
...

 

[dhyangya]

Wow, thanks ByteMyzer!!!
That did the trick.