Hi,

I am new to VBA and access and was wondering if there is a predefined set of forms that one can open? More specifically the form that opens when you want to open a file in any windows application. If so how would I go about using it.

Thanks in advance,
Richard

Richard,

I'm sure there are some newer ways to do this, but I do have a solution for you to start with. What you are referring to are called Common Dialog forms (save, save as, open, fonts, etc).

I use the Open common dialog quite extensively for many of my own applications. You can use the common dialog control from both MS-VB and MS-Access, but there are some subtle differences. I do use a time-proven method in MS-Access applications that does not actually rely on the common dialog controls themselves.

You need a couple of things to run this - first of all, you need to declare the type and called function like this:

Type adh_accOfficeGetFileNameInfo
    hWndOwner As Long
    strAppName As String * 255
    strDlgTitle As String * 255
    strOpenTitle As String * 255
    strFile As String * 4096
    strInitialDir As String * 255
    strFilter As String * 255
    lngFilterIndex As Long
    lngView As Long
    lngFlags As Long
End Type

Declare Function adh_accOfficeGetFileName Lib "msaccess.exe" _
 Alias "#56" (gfni As adh_accOfficeGetFileNameInfo, ByVal fOpen As Integer) As Long


I usually put these declares, etc into their own module for reusability, especially if I want to call them from multiple locations within the application. I myself keep these declares in a module named basDeclares.

Inside your code-behind-forms or module (depending on where you want to call this from), put the following into the Declares section:

Dim gfni As adh_accOfficeGetFileNameInfo


To call the Open common dialog form, you can do something like the
following:

    With gfni
        .hWndOwner = Application.hWndAccessApp
        .strAppName = "Select the filename to process"
        .strDlgTitle = "Select the filename to process"
        .strOpenTitle = "Select"
        .strFile = ""
        .strInitialDir = "C:\My Documents"
        .strFilter = "All Files (*.*)"
        .lngFilterIndex = 0
        .lngView = adhcGfniViewList
        .lngFlags = adhcGfniNoChangeDir Or adhcGfniInitializeView
    End With

    If adhOfficeGetFileName(gfni, True) = adhcAccErrSuccess Then
       strPathAndFileName = Trim(gfni.strFile)
    End If

In this case, strPathAndFileName will contain the actual drive, path, and filename of the file that was selected by the user using the Open common dialog. If I remember correctly, if the user clicks the [Cancel] button, strPathAndFileName will contain a zero-length string.

As I was saying, there are other common dialog forms (save, save as, etc) that can also be invoked - there are also common dialog controls in both Access and VB which can also be used. If you'd like, I can also look up the other adh functions for the other common dialogs.

Hope this helps.

Warren
:D

Warren,

Thank you for the information it got me closer to what I need to accomplish. The problem that I am having now is that I am getting undefined variable errors with these variables

adhcGfniViewList
adhcGfniNoChangeDir
adhcGfniInitializeView
adhcAccErrSuccess

Is there anything that I have to "include" (sorry C/C++ stuff) to define these variables?

Thanks,
Richard

P.S. if it helps I am using access 97

Richard,

My apologies - I forgot to include some of the declarations that you need to operate some of these functions. You can copy|paste the following into the area where you defined the adh_accOfficeGetFileNameInfo Type:


' General Errors
Public Const adhcAccErrSuccess = 0
Public Const adhcAccErrUnknown = -1

' FileExists return values
Public Const adhcFileExistsYes = 1
Public Const adhcFileExistsNo = 0

' Common Dialogs
' GetFileName errors
Public Const adhcAccErrGFNCantOpenDialog = -301
Public Const adhcAccErrGFNUserCancelledDialog = -302

' GetFileNameInfo flags
Public Const adhcGfniConfirmReplace = &H1 ' Prompt if overwriting a file?
Public Const adhcGfniNoChangeDir = &H2 ' Disable the read-only option
Public Const adhcGfniAllowReadOnly = &H4 ' Don't change to the directory the user selected?
Public Const adhcGfniAllowMultiSelect = &H8 ' Allow multiple-selection?
Public Const adhcGfniDirectoryOnly = &H20 ' Open as directory picker?
Public Const adhcGfniInitializeView = &H40 ' Initialize the view to the lView member or use last selected view?

' Views in the Office Find File dialog
Public Const adhcGfniViewDetails = 0 ' Details
Public Const adhcGfniViewPreview = 1 ' Preview
Public Const adhcGfniViewProperties = 2 ' Properties
Public Const adhcGfniViewList = 3 ' List (typical)


This should give you a little flexibility as well.

Good luck!

Warren

Warren -

I, too, have been using this code from Getz/Litwin/et al for years! However, I just ported my code utilities library which contains this code for the common file dialogs to Access 2003 for the first time, and now it won't compile under Access 2003! Do you have any idea how this declaration needs to change to work with Access 2003?

TIA,

Matt Gauch



Declare Function adh_accOfficeGetFileName Lib "msaccess.exe" _
Alias "#56" (gfni As adh_accOfficeGetFileNameInfo, ByVal fOpen As Integer) As Long