Page 1 of 2 12 LastLast
Results 1 to 10 of 16

Creating a Browse button in Access

This is a discussion on Creating a Browse button in Access within the Microsoft Access forums, part of the Question Forums category; Recently, I stumbled onto some code that is supposed to place a browse button onto a form, and a textbox ...

  1. #1
    Board Regular
    Join Date
    May 2007
    Posts
    88

    Default Creating a Browse button in Access

    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.

  2. #2
    Board Regular VicRauch's Avatar
    Join Date
    Mar 2006
    Location
    Carmichael, CA
    Posts
    2,032

    Default

    strDialogTitle:="Find File (Select The File And Click The Open Button)")

    Remove that last paren ")" and let us know what happens.
    Vic

  3. #3
    Board Regular
    Join Date
    May 2007
    Posts
    88

    Default

    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?

  4. #4
    Board Regular VicRauch's Avatar
    Join Date
    Mar 2006
    Location
    Carmichael, CA
    Posts
    2,032

    Default

    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?
    Vic

  5. #5
    Board Regular
    Join Date
    May 2007
    Posts
    88

    Default

    Oh yeah, and it would probably be useful for you to have the error message. It says named arguments not allowed and highlights strDialogTitle:=

  6. #6
    Board Regular VicRauch's Avatar
    Join Date
    Mar 2006
    Location
    Carmichael, CA
    Posts
    2,032

    Default

    I would image the "Named arguments not allowed" is because you don't have tsGetFileFromUser function in your VBA project.
    Vic

  7. #7
    Board Regular
    Join Date
    May 2007
    Posts
    88

    Default

    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

  8. #8
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    65,962

    Default

    sharks12

    That code works fine for me.

    Well it opens the dialog box anyway.

    What version of Access are you using?
    If posting code please use code tags.

  9. #9
    Board Regular VicRauch's Avatar
    Join Date
    Mar 2006
    Location
    Carmichael, CA
    Posts
    2,032

    Default

    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.
    Vic

  10. #10
    Board Regular
    Join Date
    May 2007
    Posts
    88

    Default

    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.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com