Solved

Compile error: msoFileDialogFolderPicker Variable not defined.

Posted on 2014-10-21
7
1,094 Views
Last Modified: 2014-10-21
I use Access 2010.

I imported a table and an associated form from one database to another. (From HQM to Olivier - the names of the two databases.)

I use the form to select applicable folders where I store certain items like images. For that purpose the form has a button on it with the code as shown below.

My problem is that the code works perfectly in database HQM, but gives an error when I run it in database Olivier. The error is below. It refers to "msoFileDialogFolderPicker" in the line
intResult = Application.FileDialog(msoFileDialogFolderPicker).Show

Open in new window


Compile error
The code is:
Option Compare Database
Option Explicit


Private Sub cmdSelectFolder_Click()

Dim intResult As Integer
Dim strPath As String
'the dialog is displayed to the user
intResult = Application.FileDialog(msoFileDialogFolderPicker).Show
'checks if user has cancled the dialog
If intResult <> 0 Then
    'dispaly message box
'Call MsgBox(Application.FileDialog(msoFileDialogFolderPicker _
    ).SelectedItems(1), vbInformation, "Selected Folder")
    strPath = Application.FileDialog(msoFileDialogFolderPicker _
    ).SelectedItems(1)
    Folder = strPath & "\"  'Folder is a textbox on my form
End If

End Sub

Open in new window


The references in both cases are as below.
References in both files
What can the reason for this problem be?
0
Comment
Question by:Fritz Paul
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 48

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 40395467
from my iPad.

I believe the # you are looking for is 4 (for folders) and 3 (for files).

I generally just hard code the numbers
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40395469
try adding to the references

Microsoft Office 14.0 Object Library
0
 

Author Closing Comment

by:Fritz Paul
ID: 40395506
Hi Dale, Thanks it works!
I still wonder why it was necessary to change to 4 in the one case and not the other?
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40395545
Rey is probably right on this one.  You probably have the reference to the Microsoft Office 14.0 Object library in one, but not the other.

I generally prefer to go with late binding (due to the fact that I have numerous clients that incrementally field new versions of office), so unless I absolutely need the reference, I generally go back through my code and replace messages to the object constants with the actual values.  Then I add a remark at the end of the line to indicate what that value is:

intResult = Application.FileDialog(4).Show 'msoFileDialogFolderPicker = 4
0
 

Author Comment

by:Fritz Paul
ID: 40395604
I checked my references meticulously and both are exactly the same and specifically Microsoft Office 14.0 Object library.

I don't follow your second and third paragraphs. I realize there is a gap in my education. I will follow up on that.

Is Microsoft Office 14.0 Object library associated with Access 2010? What about A2007? and A2013? Is that the issue that you overcome by using object constants?
0
 
LVL 48

Expert Comment

by:Dale Fye (Access MVP)
ID: 40395716
There are some libraries which you can use without actually referencing them in your application.  If you have users using more than one version of Office, it is generally considered best practice to develop in the lowest level of Access that any of the users will have access to.  If you do this diligently, then adding the references to these libraries will not usually be a problem, since Access will upgrade the references to coincide with the version that is running at the time.  But if you set a reference to the Office 14 Object library and one of your users is using 2007 (Office 12 Object Library) then Access will not downgrade that reference to the 12.0 version and you will end up with reference errors.

On the other hand, if you don't establish the link to those references, you will not have intellisense to assist you in your coding efforts at design time.  For that reason, I generally set the references at design time (to Excel 14 for instance), but before I deploy the application, I make changes to all of my dimension statements, and replace the constants that are associated with that library with the actual values; then I remove the reference to the library.  As an example, in Excel, I would replace:

Dim xl as Excel.Application
Dim wbk as Excel.Workbook

with

Dim xl as Object        'Excel.Application
Dim wbk as Object    'Excel.Workbook

and then in my code, I would do something like the following
    With sht.Range("A1:AI1")
        With .Interior
            .Pattern = 1                    'xlSolid
            .PatternColorIndex = -4105      'xlAutomatic
            .Color = 10092543
            .TintAndShade = 0
            .PatternTintAndShade = 0
        End With
        .autofilter
    End With

Open in new window

Notice how on lines 3 and 4, I replaced the Excel constants xlSolid and xlAutomatic with their associate values.  The technique described above is called late binding.  It requires a little more work, as you have to go through and replace all of the declaration statements and the references to the Excel object, but it generally ensures that you will not have any of the issues associated with having the incorrect version of a library mentioned in your code.
0
 

Author Comment

by:Fritz Paul
ID: 40396198
Thanks Dale, I appreciate.
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question