Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Compile error: msoFileDialogFolderPicker Variable not defined.

Posted on 2014-10-21
7
Medium Priority
?
1,355 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 2000 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

670 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