Solved

Compile error: msoFileDialogFolderPicker Variable not defined.

Posted on 2014-10-21
7
818 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
  • 3
  • 3
7 Comments
 
LVL 47

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 119

Expert Comment

by:Rey Obrero
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 47

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 47

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now