Page 1 of 2 12 LastLast
Results 1 to 15 of 20
  1. #1
    Join Date
    Oct 2002
    Posts
    88

    Unanswered: MS common dialog control

    I'd like to use the MS common dialog control in access to use a file selection control for an import macro. I've got the control from VB but I don't know VB.

    As I understand it I add it to a form and it is not visable until it is called from a button which is also on the form. On the on click event I'm to add the code :

    Private Sub Command1_Click()
    CommonDialog1.ShowOpen
    MsgBox "You chose: " & CommonDialog1.filename
    End Sub

    I've done that and nothing happens.

    Can anyone give me some advice on this?

    Ultimately I want to use the file selected by the control for a transfer text action. I'm assuming I would just enter forms!form with control Name!control name in the control source for the transfer text. Am I correct in assuming this?

    Thanks in advance for any help

  2. #2
    Join Date
    Aug 2002
    Posts
    29

    Cool Re: MS common dialog control

    I ran into similar problems with the control. I decided to use API calls instead. The code is kind of lengthy but it gets the job done. I took out some defaulting directories code, but this should work.

    Code:
    ' -----------BEGIN FORM CODE ------------------------
    Private Const mcsFileFilter As String = _
            "Excel Files (*.xls)|*.xls|" & _
            "All Files|*.*||"
    
    Private Sub cmdCommonDialog_DE_Click()
        txtImportFile_DE = CommonDialogClickGs(Me, mcsFileFilter)
    End Sub
    ' -----------END FORM CODE --------------------------
    
    
    ' -----------BEGIN modFunction module---------------
    Public Function ReplaceG(sString As String, sFind As String, sReplace As String) As String
    On Error GoTo Proc_Err
    
        Dim lPtr As Long
        Dim sTmp As String
        Dim sRetVal As String
        
        sTmp = sString
        
        If sFind <> sReplace Then
            lPtr = InStr(1, sTmp, sFind)
            
            Do Until lPtr = 0
                sRetVal = sRetVal & Left(sTmp, lPtr - 1) & sReplace
                sTmp = Right(sTmp, Len(sTmp) - (lPtr + Len(sFind) - 1))
                lPtr = InStr(1, sTmp, sFind)
            Loop
        
        End If
        
        ReplaceG = sRetVal & sTmp
    
    Proc_Exit:
        Exit Function
    Proc_Err:
        MsgBox Err.Description
        Resume Proc_Exit
    End Function
    
    
    Public Function CommonDialogClickGs( _
                                    oFrm As Form, _
                                    Optional sFileFilter As String _
    ) As String
    
    On Error GoTo Proc_Err
    
        Dim sTmp As String
    
        If sFileFilter & "" <> "" Then
            sTmp = GetOpenFileNameGs(oFrm, , , sFileFilter)
        Else
            sTmp = GetOpenFileNameGs(oFrm)
        End If
        
        If sTmp & "" <> "" Then
            CommonDialogClickGs = sTmp
        End If
        
    Proc_Exit:
        Exit Function
    Proc_Err:
        MsgBox Err.Description
        Resume Proc_Exit
    End Function
    ' -----------END modFunction module------------------
    
    
    
    
    ' -----------BEGIN modAPI module--------------------
    Option Compare Database
    Option Explicit
    
    Private Const mcsDefaultFileFilter As String = "DEL File (*.DEL)|*.DEL|" & _
                                    "Comma Delimited Files (*.csv)|*.csv|" & _
                                    "Excel Files (*.xls)|*.xls|" & _
                                    "All Files|*.*||"
    
    Public Const OFN_ALLOWMULTISELECT As Long = &H200
    Public Const OFN_CREATEPROMPT As Long = &H2000
    Public Const OFN_ENABLEHOOK As Long = &H20
    Public Const OFN_ENABLETEMPLATE As Long = &H40
    Public Const OFN_ENABLETEMPLATEHANDLE As Long = &H80
    Public Const OFN_EXPLORER As Long = &H80000
    Public Const OFN_EXTENSIONDIFFERENT As Long = &H400
    Public Const OFN_FILEMUSTEXIST As Long = &H1000
    Public Const OFN_HIDEREADONLY As Long = &H4
    Public Const OFN_LONGNAMES As Long = &H200000
    Public Const OFN_NOCHANGEDIR As Long = &H8
    Public Const OFN_NODEREFERENCELINKS As Long = &H100000
    Public Const OFN_NOLONGNAMES As Long = &H40000
    Public Const OFN_NONETWORKBUTTON As Long = &H20000
    Public Const OFN_NOREADONLYRETURN As Long = &H8000& 'see comments
    Public Const OFN_NOTESTFILECREATE As Long = &H10000
    Public Const OFN_NOVALIDATE As Long = &H100
    Public Const OFN_OVERWRITEPROMPT As Long = &H2
    Public Const OFN_PATHMUSTEXIST As Long = &H800
    Public Const OFN_READONLY As Long = &H1
    Public Const OFN_SHAREAWARE As Long = &H4000
    Public Const OFN_SHAREFALLTHROUGH As Long = 2
    Public Const OFN_SHAREWARN As Long = 0
    Public Const OFN_SHARENOWARN As Long = 1
    Public Const OFN_SHOWHELP As Long = &H10
    Public Const OFS_MAXPATHNAME As Long = 260
    
    'OFS_FILE_OPEN_FLAGS and OFS_FILE_SAVE_FLAGS below
    'are mine to save long statements; they're not
    'a standard Win32 type.
    Public Const OFS_FILE_OPEN_FLAGS = OFN_EXPLORER _
                 Or OFN_LONGNAMES _
                 Or OFN_CREATEPROMPT _
                 Or OFN_NODEREFERENCELINKS
    Public Const OFS_FILE_OPEN_MULTI_FLAGS = OFN_EXPLORER _
                 Or OFN_LONGNAMES _
                 Or OFN_CREATEPROMPT _
                 Or OFN_NODEREFERENCELINKS _
                 Or OFN_ALLOWMULTISELECT
    Public Const OFS_FILE_SAVE_FLAGS = OFN_EXPLORER _
                 Or OFN_LONGNAMES _
                 Or OFN_OVERWRITEPROMPT _
                 Or OFN_HIDEREADONLY
                 
    
    Private Const BIF_RETURNONLYFSDIRS = 1
    Private Const MAX_PATH = 260
    
    Declare Function GetOpenFileName Lib "comdlg32.dll" Alias "GetOpenFileNameA" (pOpenfilename As OPENFILENAME) As Long
    Declare Function SHBrowseForFolder Lib "shell32" (lpbi As BrowseInfo) As Long
    Private Declare Sub CoTaskMemFree Lib "ole32.dll" (ByVal hMem As Long)
    Private Declare Function lstrcat Lib "kernel32" Alias "lstrcatA" (ByVal lpString1 As String, ByVal lpString2 As String) As Long
    Private Declare Function SHGetPathFromIDList Lib "shell32" (ByVal pidList As Long, ByVal lpBuffer As String) As Long
    
    Private Type BrowseInfo
        hWndOwner As Long
        pIDLRoot As Long
        pszDisplayName As Long
        lpszTitle As Long
        ulFlags As Long
        lpfnCallback As Long
        lParam As Long
        iImage As Long
    End Type
    Private Type OPENFILENAME
        lStructSize As Long
        hWndOwner 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
    
    
    Public Function GetOpenFileNameGs( _
                                objForm As Form, _
                                Optional sInitialDir As String = "C:\", _
                                Optional lFlag As Long = 0, _
                                Optional sFileFilter As String = mcsDefaultFileFilter _
    ) As String
        Dim OpenFile As OPENFILENAME
        Dim lReturn As Long
        
        OpenFile.lStructSize = Len(OpenFile)
        OpenFile.hWndOwner = objForm.Hwnd
        OpenFile.hInstance = Application.hWndAccessApp
        OpenFile.lpstrFilter = ReplaceG(sFileFilter, "|", vbNullChar)
        OpenFile.nFilterIndex = 1
        OpenFile.lpstrFile = String(257, 0)
        OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
        OpenFile.lpstrFileTitle = OpenFile.lpstrFile
        OpenFile.nMaxFileTitle = OpenFile.nMaxFile
        OpenFile.lpstrInitialDir = sInitialDir
        OpenFile.lpstrTitle = "Open File"
        OpenFile.flags = lFlag
        lReturn = GetOpenFileName(OpenFile)
        If lReturn = 0 Then
           GetOpenFileNameGs = ""
        Else
           GetOpenFileNameGs = CleanStringMs(OpenFile.lpstrFile & "")
        End If
    End Function
    
    Private Function CleanStringMs(sInput As String) As String
        Dim i As Long
        CleanStringMs = ReplaceG(sInput, vbNullChar, "")
    End Function
    
    ' -----------END modAPI module--------------
    Last edited by GodMadeIncubus; 11-13-02 at 11:30.

  3. #3
    Join Date
    Oct 2002
    Posts
    88
    Thanks,

    After I posted I read that APIs are a better way of doing it especially if database will be installed on several machines.

    I don't really know VB and am learning about modules. I take it that the form code goes in the code part of the on click but I'm not sure what do do with the other two codes.

    If I put them all in one module I get an error stating that only comments can go after an end statement.

    If I make two modules I get a number of errors.

    Can you give me some hints please.

    Thanks again

    Tom

  4. #4
    Join Date
    Aug 2002
    Posts
    29
    Ok... I forgot to add the "ReplaceG" function. I've added to my earlier post.

    In order to get this code working:

    1. Copy the modAPI code into a module.
    2. Copy the modFunction code into a module. (These can be in the same module... it doesn't really matter; I like to keep them logically separated)
    3. Compile the modFunction code.
    4. Compile the modAPI code.
    5. Create a new form
    6. Add a button called "cmdCommonDialog_DE"
    7. Add a text box called "txtImportFile_DE"
    8. Goto the code behind the form.
    9. Copy the Form code in.
    10. Compile, save, and close all windows
    11. Open the form in Design mode again
    12. Right-click on the "cmdCommonDialog_DE" button and select "Build Event..." (This should attach the button's click event to the code you copied in)
    13. Save it.
    14. Run the form.

    Let me know if it works. I've followed these steps on my end to check if it works (it does) but I may have skipped something.

    Good luck!
    Last edited by GodMadeIncubus; 11-12-02 at 15:30.

  5. #5
    Join Date
    Oct 2002
    Posts
    88

    Compile

    I'm new to all this module and VB stuff. Do I need developers edition to compile this code or can I do it with standard access. Or if I can't do it with access do I compile it in VB and then import it somehow.

    Thanks Again

  6. #6
    Join Date
    Aug 2002
    Posts
    29

    Re: Compile

    Compile:

    With the module window open...
    Click "Compile ..." in the Debug menu.

  7. #7
    Join Date
    Oct 2002
    Posts
    88

    Compile

    When I go to compile it goes to the function you added and says it is an undefined function. I go to the help on this error and get:


    Sub, Function, or Property not defined (Error 35)


    A Sub, Function, or Property procedure must be defined to be called. This error has the following causes and solutions:

    You misspelled the name of your procedure.
    Check the spelling and correct it.

    You tried to call a procedure from another project without explicitly adding a reference to that project in the References dialog box.
    To add a reference

    Display the References dialog box.


    Find the name of the project containing the procedure you want to call. If the project name doesn't appear in the References dialog box, click the Browse button to search for it.


    Click the check box to the left of the project name.


    Click OK.
    The specified procedure isn't visible to the calling procedure.
    Procedures declared Private in one module can't be called from procedures outside the module. If Option Private Module is in effect, procedures in the module aren't available to other projects. Search to locate the procedure.

    You declared a Windows dynamic-link library (DLL) routine or Macintosh code-resource routine, but the routine isn't in the specified library or code resource.


    Check the ordinal (if you used one) or the name of the routine. Make sure your version of the DLL or Macintosh code-resource is the correct one. The routine may only exist in later versions of the DLL or Macintosh code-resource. If the directory containing the wrong version precedes the directory containing the correct one in your path, the wrong DLL or Macintosh code-resource is accessed. You gave the right DLL name or Macintosh code-resource, but it isn't the version that contains the specified function.
    For additional information, select the item in question and press F1 (in Windows) or HELP (on the Macintosh).


    Does it have to be defined and what do I have to check?

    Thanks again for all the help

  8. #8
    Join Date
    Oct 2002
    Posts
    88

    copy form code into code behind the form

    you mention to copy the form code into the code behind the form. I thought this went into the on click event for the button. Does it go somewhere in the properties of the form itself?

    Thanks again

  9. #9
    Join Date
    Aug 2002
    Posts
    29

    Re: Compile

    This error is most likely because you didn't copy the "ReplaceG" function into your "modFunction" module. Look at my earlier post (I have modified it since...) for the "ReplaceG" function.

    When you get this error, a function name is usually highlighted. Is it the "ReplaceG" function call?

    Code:
    OpenFile.lpstrFilter = ReplaceG(sFileFilter, "|", vbNullChar)

    Also, when I speak of the "code behind the form", I'm talking about all the code in general... this includes the button click event.
    Last edited by GodMadeIncubus; 11-13-02 at 10:54.

  10. #10
    Join Date
    Oct 2002
    Posts
    88

    replaceG function

    I see this in the form code:

    Public Function ReplaceG(sString As String, sFind As String, sReplace As String) As String
    On Error GoTo Proc_Err

    Dim lPtr As Long
    Dim sTmp As String
    Dim sRetVal As String

    sTmp = sString

    If sFind <> sReplace Then
    lPtr = InStr(1, sTmp, sFind)

    Do Until lPtr = 0
    sRetVal = sRetVal & Left(sTmp, lPtr - 1) & sReplace
    sTmp = Right(sTmp, Len(sTmp) - (lPtr + Len(sFind) - 1))
    lPtr = InStr(1, sTmp, sFind)
    Loop

    End If

    ReplaceG = sRetVal & sTmp

    Proc_Exit:
    Exit Function
    Proc_Err:
    MsgBox Err.Description
    Resume Proc_Exit
    End Function


    so I'm to put this in the mod code as well?

    Thanks

  11. #11
    Join Date
    Aug 2002
    Posts
    29

    Re: replaceG function

    Whoops... actually the "ReplaceG" function should only be in the "modFunction" module. I put it in the wrong place in my first post (it's fixed now).

  12. #12
    Join Date
    Oct 2002
    Posts
    88

    Getting There

    After I put the code in the form and do build event and compile I get an error. See the attached file.

    Thanks Again
    Attached Files Attached Files

  13. #13
    Join Date
    Aug 2002
    Posts
    29

    Re: Getting There

    Here is what your code should look like:

    Code:
    Private Const mcsFileFilter As String = _
            "Excel Files (*.xls)|*.xls|" & _
            "All Files|*.*||"
    
    Private Sub Command0_Click()
            txtImportFile_DE = CommonDialogClickGs(Me, mcsFileFilter)
    End Sub
    Make sure that you add a textbox to the form. In the textbox properties, set the name to "txtImportFile_DE".
    Last edited by GodMadeIncubus; 11-13-02 at 14:54.

  14. #14
    Join Date
    Oct 2002
    Posts
    88

    No Errors Now

    I made the changes and don't get any errors. I think I've done everything you said, added and named the textbox etc (although I don't see how it gets into play)

    I'm attaching the sample database I made to test this so you can see what I'm not doing.

    Thanks again
    Attached Files Attached Files

  15. #15
    Join Date
    Aug 2002
    Posts
    29

    Re: No Errors Now

    Try this.
    Attached Files Attached Files

Posting Permissions

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