Solved

Compile error: msoFileDialogFolderPicker Variable not defined.

Posted on 2014-10-21
7
1,219 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 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 48

Expert Comment

by:Dale Fye
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
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

631 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