# Solved: msoFileDialogFilePicker.. variable not found error



## Zul77 (Dec 31, 2009)

Hi;

I want to make a function in my form to insert a picture in it. I use the code as same as Northwind Db.

However I got compile error "variable not found" and "msoFileDialogFilePicker" syntax has been highlighted.

I go for Tool --> References and I found the square box for Microsoft Access 11.0 Object Library is already been checked.

So anyone get any idea how to solve this problem? Thanks for your attention.


----------



## Ziggy1 (Jun 18, 2002)

I can't see all your code, there doesn't look like there is a declaration of a variable and possibly the setting of the variable.

add...

Dim YouVariable As Office.FileDialog


Set YouVariable = Application.FileDialog(msoFileDialogFilePicker)


----------



## Rollin_Again (Sep 4, 2003)

Which application is the macro code being stored in? Also please copy and paste your entire code.

Rollin


----------



## Zul77 (Dec 31, 2009)

Thanks for your willingness to help guys. I use access 2003 and here is my whole code. I got it from Northwind sample.

Option Compare Database
Option Explicit
Dim path As String
Private Sub AddPicture_Click()
' Use the Office File Open dialog to get a file name to use
' as an employee picture.
getFileName
End Sub

Private Sub Form_RecordExit(Cancel As Integer)
' Hide the errormsg label to reduce flashing when navigating
' between records.
errormsg.Visible = False
End Sub

Private Sub RemovePicture_Click()
' Clear the file name for the employee record and display the
' errormsg label.
Me![ImagePath] = ""
hideImageFrame
errormsg.Visible = True
End Sub

Private Sub Form_AfterUpdate()
' Requery the ReportsTo combo box after a record has been changed.
' Then, either show the errormsg label if no file name exists for
' the employee record or display the image if there is a file name that
' exists.
On Error Resume Next
showErrorMessage
showImageFrame
If (IsRelative(Me!ImagePath) = True) Then
Me![ImageFrame].Picture = path & Me![ImagePath]
Else
Me![ImageFrame].Picture = Me![ImagePath]
End If
End Sub
Private Sub ImagePath_AfterUpdate()
' After selecting an image for the employee, display it.
On Error Resume Next
showErrorMessage
showImageFrame
If (IsRelative(Me!ImagePath) = True) Then
Me![ImageFrame].Picture = path & Me![ImagePath]
Else
Me![ImageFrame].Picture = Me![ImagePath]
End If
End Sub

Private Sub Form_Current()
' Display the picture for the current employee record if the image
' exists. If the file name no longer exists or the file name was blank
' for the current employee, set the errormsg label caption to the
' appropriate message.
Dim res As Boolean
Dim fName As String
path = CurrentProject.path
On Error Resume Next
errormsg.Visible = False
If Not IsNull(Me!Photo) Then
res = IsRelative(Me!Photo)
fName = Me![ImagePath]
If (res = True) Then
fName = path & "\" & fName
End If

Me![ImageFrame].Picture = fName
showImageFrame
Me.PaintPalette = Me![ImageFrame].ObjectPalette
If (Me![ImageFrame].Picture <> fName) Then
hideImageFrame
errormsg.Caption = "Picture not found"
errormsg.Visible = True
End If
Else
hideImageFrame
errormsg.Caption = "Click Add/Change to add picture"
errormsg.Visible = True
End If
End Sub

Sub getFileName()
' Displays the Office File Open dialog to choose a file name
' for the current employee record. If the user selects a file
' display it in the image control.
Dim fileName As String
Dim result As Integer
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select Employee Picture"
.Filters.Add "All Files", "*.*"
.Filters.Add "JPEGs", "*.jpg"
.Filters.Add "Bitmaps", "*.bmp"
.FilterIndex = 3
.AllowMultiSelect = False
.InitialFileName = CurrentProject.path
result = .Show
If (result <> 0) Then
fileName = Trim(.SelectedItems.Item(1))
Me![ImagePath].Visible = True
Me![ImagePath].SetFocus
Me![ImagePath].Text = fileName
Me![FirstName].SetFocus
Me![ImagePath].Visible = False
End If
End With
End Sub

Sub showErrorMessage()
' Display the errormsg label if the image file is not available.
If Not IsNull(Me!Photo) Then
errormsg.Visible = False
Else
errormsg.Visible = True
End If
End Sub
Function IsRelative(fName As String) As Boolean
' Return false if the file name contains a drive or UNC path
IsRelative = (InStr(1, fName, ":") = 0) And (InStr(1, fName, "\\") = 0)
End Function
Sub hideImageFrame()
' Hide the image control
Me![ImageFrame].Visible = False
End Sub
Sub showImageFrame()
' Display the image control
Me![ImageFrame].Visible = True
End Sub

Within this post I have attached the file. Hopefully someone can figure it out how to make it happen. Thank you so much.


----------



## Rollin_Again (Sep 4, 2003)

I believe that FileDialog is a member of the *OFFICE* library NOT the *ACCESS* library. Open your references and scroll through the list until you find Microsoft Office 12.0 Object Library and set reference to it by placing a check next to it. If that doesn't work you can always change the code to use a Windows API call instead.

Regards,
Rollin


----------



## Zul77 (Dec 31, 2009)

Thanks for the reply, since I'm using access 2003, I can't make the reference to Office 12.0 Object Library. Do you have example of Windows API call to make insert picture function in form?


----------



## OBP (Mar 8, 2005)

Try MS Office 10 or MS Office 11 instead of MS Office 12.


----------



## Rollin_Again (Sep 4, 2003)

Are you saying that you have a stand alone version of Microsoft Access and have not installed the entire Office Suite? I'll have to dig through my old code library to find the API call you'll need.

Regards,
Rollin


----------



## Ziggy1 (Jun 18, 2002)

Zul77 said:


> Thanks for the reply, since I'm using access 2003, I can't make the reference to Office 12.0 Object Library. Do you have example of Windows API call to make insert picture function in form?


I am able to set the reference, and the dialogue works for me.

* although I have Office 2007


----------



## Rollin_Again (Sep 4, 2003)

I suspect that Zul does not have a full version of Microsoft Office on his pc and that is why the Office Object Library is not available. Are you able to confirm this Zul? If this is the case you can click the link below to see the API way to display the file open dialogue. Just copy the entire code from the page and paste into a blank module and then call the macro "TestIt"

http://www.mvps.org/access/api/api0001.htm

Rollin


----------



## Zul77 (Dec 31, 2009)

Wow... It's so tough to a newbie like me. However, thanks guys for your willingness to help. For this moment I'm going to leave this problem here. Maybe this function is the last thing that I'll impose to my Db. 

This doesn't mean that I've gave up. Thanks again guys. Have a good day


----------



## Rollin_Again (Sep 4, 2003)

Don't give up so easily. We are here to help and walk you through it. It looks like the portion of the code that is failing is the part that opens a explorer style window to allow the user to drill through the directory structure to select the file to process. You can eliminate that portion of the code altogether and "hard-code" your path to the file instead. You would then need to make sure the file to process is always put into the same directory. Your other alternative is to implement the API code I provided. It is very easy to do this. If you don't understand how to do it I'll be happy to modify the code for you. Just let me know.

Rollin


----------



## Zul77 (Dec 31, 2009)

Thanks Rollin_Again. Actually I don't give up, but I spend too much time finding how to configure it. So I manage to leave it first and do any other things first.

Honestly (shamefully) I don't understand your instruction "Just copy the entire code from the page and paste into a blank module and then call the macro "TestIt". I went to the site and found the code are too longer for me to understand. I'm kinda cut and paste person and can only do a simple editing to the code.

I'm please if you can make/modify the code for me and tell me how to use it step by step. Thanks again.


----------



## Rollin_Again (Sep 4, 2003)

See attached example with modified code.

Rollin


----------



## Zul77 (Dec 31, 2009)

Thanks Rolling_Again. I'm really appreciate this. The file that you've attached is working great. That's actually how I want it to be. And sorry for taking quite long to give my appreciation for your help.

I have copied the code that you've made and pasted it in my frmMain.

These are the other steps that I have done.

1. I clicked image button in toolbox window and drag it on the form. I selected a dummy picture file for the image box. Then I open the property for the image box and deleted the source of the dummy picture. I named the image box as ImageFrame.

2. I created a text box on the form and named it ImagePath.

3. I created a cmd button from toolbox window. I don't select any category and action for the cmd button. I named the button as AddPicture in it's property window.

4. I created another cmd button and name it as RemovePicture.

However, when I click the AddPicture button (in the form's view window), I got an error as the picture below. Did I missed any step?










Sorry for bothering you again.


----------



## Rollin_Again (Sep 4, 2003)

Open the DB and press *ALT + F11* to open the VB editor and then click *INSERT >> MODULE*.

When the blank module opens paste the code below into it and then save and try again. BTW....This code was not written by me. I've only tweaked it a bit to get it to work with your application.


```
Option Compare Database

Public vPath As String

' This code was originally written by Ken Getz.
' It is not to be altered or distributed, 'except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code originally courtesy of:
' Microsoft Access 95 How-To
' Ken Getz and Paul Litwin
' Waite Group Press, 1996
' Revised to support multiple files:
' 28 December 2007

Type tagOPENFILENAME
    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

Declare Function aht_apiGetOpenFileName Lib "comdlg32.dll" _
    Alias "GetOpenFileNameA" (OFN As tagOPENFILENAME) As Boolean

Declare Function aht_apiGetSaveFileName Lib "comdlg32.dll" _
    Alias "GetSaveFileNameA" (OFN As tagOPENFILENAME) As Boolean
Declare Function CommDlgExtendedError Lib "comdlg32.dll" () As Long

Global Const ahtOFN_READONLY = &H1
Global Const ahtOFN_OVERWRITEPROMPT = &H2
Global Const ahtOFN_HIDEREADONLY = &H4
Global Const ahtOFN_NOCHANGEDIR = &H8
Global Const ahtOFN_SHOWHELP = &H10
' You won't use these.
'Global Const ahtOFN_ENABLEHOOK = &H20
'Global Const ahtOFN_ENABLETEMPLATE = &H40
'Global Const ahtOFN_ENABLETEMPLATEHANDLE = &H80
Global Const ahtOFN_NOVALIDATE = &H100
Global Const ahtOFN_ALLOWMULTISELECT = &H200
Global Const ahtOFN_EXTENSIONDIFFERENT = &H400
Global Const ahtOFN_PATHMUSTEXIST = &H800
Global Const ahtOFN_FILEMUSTEXIST = &H1000
Global Const ahtOFN_CREATEPROMPT = &H2000
Global Const ahtOFN_SHAREAWARE = &H4000
Global Const ahtOFN_NOREADONLYRETURN = &H8000
Global Const ahtOFN_NOTESTFILECREATE = &H10000
Global Const ahtOFN_NONETWORKBUTTON = &H20000
Global Const ahtOFN_NOLONGNAMES = &H40000
' New for Windows 95
Global Const ahtOFN_EXPLORER = &H80000
Global Const ahtOFN_NODEREFERENCELINKS = &H100000
Global Const ahtOFN_LONGNAMES = &H200000

Function GetFile()
    Dim strFilter As String
    Dim lngFlags As Long
    
    strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")

    ' Uncomment this line to try the example
    ' allowing multiple file names:
    ' lngFlags = ahtOFN_ALLOWMULTISELECT Or ahtOFN_EXPLORER

    Dim result As Variant

    result = ahtCommonFileOpenSave(InitialDir:="C:\", _
        Filter:=strFilter, FilterIndex:=3, Flags:=lngFlags, _
        DialogTitle:="SELECT PICTURE! PLEASE SELECT EMPLOYEE PICTURE!")

    If lngFlags And ahtOFN_ALLOWMULTISELECT Then
        If IsArray(result) Then
            Dim i As Integer
            For i = 0 To UBound(result)
                MsgBox result(i)
            Next i
        Else
            'MsgBox result
        End If
    Else
        'MsgBox result
    End If
    
    vPath = result

    ' Since you passed in a variable for lngFlags,
    ' the function places the output flags value in the variable.
    Debug.Print Hex(lngFlags)
End Function

Function GetOpenFile(Optional varDirectory As Variant, _
    Optional varTitleForDialog As Variant) As Variant

    ' Here's an example that gets an Access database name.
    Dim strFilter As String
    Dim lngFlags As Long
    Dim varFileName As Variant

    ' Specify that the chosen file must already exist,
    ' don't change directories when you're done
    ' Also, don't bother displaying
    ' the read-only box. It'll only confuse people.
    lngFlags = ahtOFN_FILEMUSTEXIST Or _
                ahtOFN_HIDEREADONLY Or ahtOFN_NOCHANGEDIR
    If IsMissing(varDirectory) Then
        varDirectory = ""
    End If
    If IsMissing(varTitleForDialog) Then
        varTitleForDialog = ""
    End If

    ' Define the filter string and allocate space in the "c"
    ' string Duplicate this line with changes as necessary for
    ' more file templates.
    strFilter = ahtAddFilterItem(strFilter, _
                "Access (*.mdb)", "*.MDB;*.MDA")

    ' Now actually call to get the file name.
    varFileName = ahtCommonFileOpenSave( _
                    OpenFile:=True, _
                    InitialDir:=varDirectory, _
                    Filter:=strFilter, _
                    Flags:=lngFlags, _
                    DialogTitle:=varTitleForDialog)
    If Not IsNull(varFileName) Then
        varFileName = TrimNull(varFileName)
    End If
    GetOpenFile = varFileName
End Function

Function ahtCommonFileOpenSave( _
            Optional ByRef Flags As Variant, _
            Optional ByVal InitialDir As Variant, _
            Optional ByVal Filter As Variant, _
            Optional ByVal FilterIndex As Variant, _
            Optional ByVal DefaultExt As Variant, _
            Optional ByVal FileName As Variant, _
            Optional ByVal DialogTitle As Variant, _
            Optional ByVal hwnd As Variant, _
            Optional ByVal OpenFile As Variant) As Variant

    ' This is the entry point you'll use to call the common
    ' file open/save dialog. The parameters are listed
    ' below, and all are optional.
    '
    ' In:
    ' Flags: one or more of the ahtOFN_* constants, OR'd together.
    ' InitialDir: the directory in which to first look
    ' Filter: a set of file filters, set up by calling
    ' AddFilterItem. See examples.
    ' FilterIndex: 1-based integer indicating which filter
    ' set to use, by default (1 if unspecified)
    ' DefaultExt: Extension to use if the user doesn't enter one.
    ' Only useful on file saves.
    ' FileName: Default value for the file name text box.
    ' DialogTitle: Title for the dialog.
    ' hWnd: parent window handle
    ' OpenFile: Boolean(True=Open File/False=Save As)
    ' Out:
    ' Return Value: Either Null or the selected filename
    Dim OFN As tagOPENFILENAME
    Dim strFileName As String
    Dim strFileTitle As String
    Dim fResult As Boolean

    ' Give the dialog a caption title.
    If IsMissing(InitialDir) Then InitialDir = CurDir
    If IsMissing(Filter) Then Filter = ""
    If IsMissing(FilterIndex) Then FilterIndex = 1
    If IsMissing(Flags) Then Flags = 0&
    If IsMissing(DefaultExt) Then DefaultExt = ""
    If IsMissing(FileName) Then FileName = ""
    If IsMissing(DialogTitle) Then DialogTitle = ""
    If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp
    If IsMissing(OpenFile) Then OpenFile = True
    ' Allocate string space for the returned strings.
    strFileName = Left(FileName & String(256, 0), 256)
    strFileTitle = String(256, 0)
    ' Set up the data structure before you call the function
    With OFN
        .lStructSize = Len(OFN)
        .hwndOwner = hwnd
        .strFilter = Filter
        .nFilterIndex = FilterIndex
        .strFile = strFileName
        .nMaxFile = Len(strFileName)
        .strFileTitle = strFileTitle
        .nMaxFileTitle = Len(strFileTitle)
        .strTitle = DialogTitle
        .Flags = Flags
        .strDefExt = DefaultExt
        .strInitialDir = InitialDir
        ' Didn't think most people would want to deal with
        ' these options.
        .hInstance = 0
        '.strCustomFilter = ""
        '.nMaxCustFilter = 0
        .lpfnHook = 0
        'New for NT 4.0
        .strCustomFilter = String(255, 0)
        .nMaxCustFilter = 255
    End With
    ' This will pass the desired data structure to the
    ' Windows API, which will in turn it uses to display
    ' the Open/Save As Dialog.
    If OpenFile Then
        fResult = aht_apiGetOpenFileName(OFN)
    Else
        fResult = aht_apiGetSaveFileName(OFN)
    End If

    ' The function call filled in the strFileTitle member
    ' of the structure. You'll have to write special code
    ' to retrieve that if you're interested.
    If fResult Then
        ' You might care to check the Flags member of the
        ' structure to get information about the chosen file.
        ' In this example, if you bothered to pass in a
        ' value for Flags, we'll fill it in with the outgoing
        ' Flags value.
        If Not IsMissing(Flags) Then Flags = OFN.Flags
        If Flags And ahtOFN_ALLOWMULTISELECT Then
            ' Return the full array.
            Dim items As Variant
            Dim value As String
            value = OFN.strFile
            ' Get rid of empty items:
            Dim i As Integer
            For i = Len(value) To 1 Step -1
              If Mid$(value, i, 1) <> Chr$(0) Then
                Exit For
              End If
            Next i
            value = Mid(value, 1, i)

            ' Break the list up at null characters:
            items = Split(value, Chr(0))

            ' Loop through the items in the "array",
            ' and build full file names:
            Dim numItems As Integer
            Dim result() As String

            numItems = UBound(items) + 1
            If numItems > 1 Then
                ReDim result(0 To numItems - 2)
                For i = 1 To numItems - 1
                    result(i - 1) = FixPath(items(0)) & items(i)
                Next i
                ahtCommonFileOpenSave = result
            Else
                ' If you only select a single item,
                ' Windows just places it in item 0.
                ahtCommonFileOpenSave = items(0)
            End If
        Else
            ahtCommonFileOpenSave = TrimNull(OFN.strFile)
        End If
    Else
        ahtCommonFileOpenSave = vbNullString
    End If
End Function

Function ahtAddFilterItem(strFilter As String, _
    strDescription As String, Optional varItem As Variant) As String

    ' Tack a new chunk onto the file filter.
    ' That is, take the old value, stick onto it the description,
    ' (like "Databases"), a null character, the skeleton
    ' (like "*.mdb;*.mda") and a final null character.

    If IsMissing(varItem) Then varItem = "*.*"
    ahtAddFilterItem = strFilter & _
                strDescription & vbNullChar & _
                varItem & vbNullChar
End Function

Private Function TrimNull(ByVal strItem As String) As String
    Dim intPos As Integer

    intPos = InStr(strItem, vbNullChar)
    If intPos > 0 Then
        TrimNull = Left(strItem, intPos - 1)
    Else
        TrimNull = strItem
    End If
End Function

Private Function FixPath(ByVal path As String) As String
    If Right$(path, 1) <> "\" Then
        FixPath = path & "\"
    Else
        FixPath = path
    End If
End Function

'************** Code End *****************
```
Regards,
Rollin


----------



## Zul77 (Dec 31, 2009)

Thanks. I've paste the code and it's work. However this time when I click close down the select (picture) window, I got another error.










Please review the (latest) modified Db. Thanks


----------



## Rollin_Again (Sep 4, 2003)

Just comment that line out by putting an apostophe mark at the beginning. You can do the same for any other lines of code that reference the object called *errormsg* which is probably some type of label or other control that does not exist on your form. The other option is to create the label on the form and call it *errormsg*

Rollin


----------



## Zul77 (Dec 31, 2009)

Thank so much.... I use the apostophe way and it's work. May I ask you a question... in the future is the action (comment the errormsg line) will effect the Db (giving error in some other parts)? May I know what is the function of the errormsg?

Thanks.


----------



## Rollin_Again (Sep 4, 2003)

In your particular example there is a label on the form that is visible when there is no picture is associated with that specific record. Your form apparently doesn't have this label. It shouldn't affect the functionality as far as I can see.

Regards,
Rollin


----------



## Zul77 (Dec 31, 2009)

Thank you very much. Have a good weekend


----------



## Zul77 (Dec 31, 2009)

Hi Rollin;

Sorry for bothering you again. May I know the code to make the pic from the path(same path as the form) appears in a report? Thanks


----------



## Rollin_Again (Sep 4, 2003)

I'm confused by what you are asking. Are you saying that you want the dialogue box to open with a specified default path which should be the same as the project itself? If so, please post your latest DB project with your latest code and I'll modify it and upload back.

Regards,
Rollin


----------



## Zul77 (Dec 31, 2009)

I'm so sorry if my question is not clear. Here attached my recent Db.

Please go for *rptStudentEvent*. There you can see I have put an image of olympic rings. This image will be shown if I don't put any image on the *frmMain* (the one that you help before).

However if I put any image in the *frmMain*, I want the default image (olympic rings) in the report (*rptStudentEvent*) will be replaced by the one that I put in the *frmMain*.

I'm sorry for my bad English for making you confused. Thanks.


----------

