Creating a Browse button in Access

sharks13

Board Regular
Joined
May 23, 2007
Messages
88
Recently, I stumbled onto some code that is supposed to place a browse button onto a form, and a textbox with the name of the file the user selects. Before I get to the code, I'll explain exactly what I want.

Essentially, what I want is to have an "Extras" folder. This folder will hold more information for specific records in the forms. I want the user to be able to, on the click of a button, look through that folder and find the correct file. Once it's found, I want the address of that file to be stored in a textbox, and then I want the user to be able to click the textbox (hyperlink probably?) to bring up the file whenever they want.

Here is the code. I think it's basically what I want, but I can't get it to work.
Code:
Private Sub bBrowse_Click()
On Error GoTo Err_bBrowse_Click
   
    Dim strFilter As String
    Dim lngFlags As Long
    Dim varFileName As Variant

    Me.tbHidden.SetFocus

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

    lngFlags = tscFNPathMustExist Or tscFNFileMustExist Or tscFNHideReadOnly

    varFileName = tsGetFileFromUser( _
    fOpenFile:=True, _
    strFilter:=strFilter, _
    rlngflags:=lngFlags, _
    strInitialDir:="C:\Windows\", _
    strDialogTitle:="Find File (Select The File And Click The Open Button)")
    'remove the strInitialDir:="C:\Windows\", _ line if you do not want the Browser to open at a specific location

    If IsNull(varFileName) Or varFileName = "" Then
        Debug.Print "User pressed 'Cancel'."
        Beep
        MsgBox "File selection was canceled.", vbInformation
        Exit Sub
    Else
        'Debug.Print varFileName
        tbFile = varFileName
    End If

    Call ParseFileName

Exit_bBrowse_Click:
    Exit Sub

Err_bBrowse_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_bBrowse_Click

      
End Sub

The button is of course named bBrowse and I have a textbox called tbHidden and a textbox called tbFile as well. I'm getting an error on the strDialogTitle:="Find File (Select The File And Click The Open Button)") line. Any help would be appreciated.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
strDialogTitle:="Find File (Select The File And Click The Open Button)")

Remove that last paren ")" and let us know what happens.
 
Upvote 0
I get the expected ) error. I think that last paren is linked with the one right after tsGetFileFromUser. Side note though, what is tsGetFileFromUser? tb must be text box, but what does the ts represent?
 
Upvote 0
:oops: Sorry, I had not noticed that was all one statement, starting with tsGetFileFromUser. I normally indent my continuation lines.

You do not have all the code necessary to use this Sub. "tsGetFileFromUser" is a function that you need, but appears that you did not copy into your VBA project. When the paren is there, what is the error message you get?
 
Upvote 0
Oh yeah, and it would probably be useful for you to have the error message. It says named arguments not allowed and highlights strDialogTitle:=
 
Upvote 0
I would image the "Named arguments not allowed" is because you don't have tsGetFileFromUser function in your VBA project.
 
Upvote 0
Oh wow. I just noticed there are modules in the program too. I've never used them before, but now I see where all those random names are. I'll try to make sense of it.

Edit: Actually, the codes comments say I should just copy and paste the module to my own database. I did that, but I'm still getting the same error. I don't know how to work with modules at all. If the coding for it will help you at all here it is.
Code:
'--------------------------------------------------------------------------
'.Description  : This module calls directly into comdlg32.dll to allow user
'.               to select a filename using the Windows Common Dialog.  The
'.               user may browse for a file, or enter a file name directly.
'.Written By   : Carl Tribble
'.Date Created : 04/05/1999 09:56:31 AM
'.Rev. History :
' Comments     : Normally, to use the Common Dialog you need to physically
'                place the ActiveX control onto a form and then use code
'                behind the form to implement its functionality.  This
'                module allows you to incorporate the functionality of the
'                File Open/Save part of the Common Dialog without the
'                ActiveX control itself. This module is completely self-
'                contained.  Simply copy it into your database to use it.
'.-------------------------------------------------------------------------
'.
' ADDITIONAL NOTES:
'
'  This module only provides the Open/Save file dialog, not the other
'  CommonDialog interfaces (ColorChooser, Help, PrintDialog, etc.)
'
'  If you want your user to browse for folder names (paths) you must use
'  the module basBrowseFolders instead.
'
'  TO STREAMLINE this module for production programs, you should remove:
'     1) Unnecessary comments
'     2) Flag Constants which you do not intend to use.
'     3) The test procedure tsGetFileFromUserTest
'
'--------------------------------------------------------------------------
'
' INSTRUCTIONS:
'
'         ( For a working example, open the Debug window  )
'         ( and enter tsGetFileFromUserTest.              )
'
'.All the arguments for the function are optional.  You may call it with no
'.arguments whatsoever and simply assign its return value to a variable of
'.the Variant type.  For example:
'.
'.   varFileName = tsGetFileFromUser()
'.
'.The function will return:
'.   the full path and filename selected or entered by the user, or
'.   Null if an error occurs or if the user presses Cancel.
'.
'.Optional arguments may include any of the following:
'. rlngFlags      : one or more of the tscFN* constants (declared below)
'.                  Combine multiple constants like this:
'.                   tscFNHideReadOnly Or tscFNFileMustExist
'. strInitialDir : the directory to display when dialog opens
'. strFilter     : a string containing any filters you want to use. Each
'.                 part must be separated by the vbNullChar. -example below
'. lngFilterIndex: a 1-based index indicating which filter to start with.
'. strDefaultExt : Extension to use if user does not enter one.
'. strFileName   : Default File to display in the File Name text box.
'. strDialogTitle: Caption to display in the dialog's title bar.
'. fOpenFile     : Boolean-True for the Open dialog, False for Save dialog.
'
' FILTER EXAMPLE: The filter must be a string containing two parts for each
'  filter.  The first part is the Description, it is what the user will see
'  in the Files of Type box, e.g. "Text Files (*.txt)".  The second part is
'  the actual filter, e.g. "*.txt".  Each part and each filter must be
'  separated by the vbNullChar.  For example, to provide two filters, one for
'  *.mdb files, and one for all files, use a statement like this:
'
'  strFilter = "Access (*.mdb)" & vbNullChar & "*.mdb" _
'   & vbNullChar & "All Files (*.*)" & vbNullChar & "*.*"
'
'  Then pass your strFilter variable as the strFilter argument for the call
'  to tsGetFileFromUser()
'
'.--------------------------------------------------------------------------
'.
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

'--------------------------------------------------------------------------
' Project      : tsDeveloperTools
' Description  : An example of how you can call tsGetFileFromUser()
' Calls        :
' Accepts      :
' Returns      :
' Written By   : Carl Tribble
' Date Created : 05/04/1999 11:19:41 AM
' Rev. History :
' Comments     : This is provided merely as an example to the programmer
'                It may be safely deleted from production code.
'--------------------------------------------------------------------------

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
 
Upvote 0
sharks12

That code works fine for me.

Well it opens the dialog box anyway.

What version of Access are you using?
 
Upvote 0
Where did you copy this code? If it is not working, I'm assuming you put it into a form or report module, not into a "normal" module that you would see it's name with the other modules in the database window.
 
Upvote 0
I'm using Access 2000. So all you did was make a new module in your database, copy over the old one, and add a button with the code in the first post? Well you probably had to change a few names, but other than that it works? I'm still getting the same error as before, it acts like the module isn't even there.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,988
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top