Solved

Use result of CommonDialogAPI.FileName in text field of form

Posted on 2011-02-11
5
387 Views
Last Modified: 2012-08-13
I have a MS Access DB which contains forms that have a text field that links to a .jpg file to display a photo in an image frame. They are long paths for typing so I am using the CommonDialogAPI to pop up the open file dialog box. I can selelct the file and if I display the value of CommonDialogAPI.FileName in a msgbox it is correct. However, if I try to set the field on the form to that value nothing happens. I have tried setting a variant to the return value and it displays OK as well. I tried CStr on the variant but still no joy. As soon as I try to set the form field it stops. I can set the form field OK if I replace the return value with a string constant so I know the field is enabled. The field is long enough because I can type it in full directly into the field. Is there something about the return from CommonDialogAPI.FileName that needs to be handled differently?
My code is below.

If I replace the line
    Me.Photos = CStr(FilePath)
with
    Me.Photos = "Any old path text"
the field is populated and displays fine.

Private Sub cmdPhoto_Click()
On Error GoTo Err_cmdPhoto_Click

Dim FilePath As Variant

    CommonDialogAPI.Filter = "All Files|*.*"
    CommonDialogAPI.CancelError = True
    On Error GoTo Canceled
    CommonDialogAPI.ShowOpen
    GoTo NotCanceled

Canceled:
    On Error GoTo 0
    Exit Sub

NotCanceled:
    MsgBox "You chose: " & CommonDialogAPI.FileName             'Displays OK
    FilePath = CommonDialogAPI.FileName
    MsgBox "FilePath variant is " & FilePath                                  'Displays OK
    Me.Photos = CStr(FilePath)                                                    'Stops here and form field blank
    MsgBox "Form contains " & Me.Photos                                   'No display
   
Exit_cmdPhoto_Click:
    Exit Sub

Err_cmdPhoto_Click:
    MsgBox Err.Description
    Resume Exit_cmdPhoto_Click
   
End Sub
0
Comment
Question by:JoanEdington
  • 4
5 Comments
 
LVL 31

Accepted Solution

by:
Helen_Feddema earned 500 total points
ID: 34874868
I don't know about the API CommonDialog method, but if you use the FileDialog object instead, you can definitely write the return value to a textbox; I do it all the tiem on my databases.  Here is an example:
Private Sub cmdTemplatePath_Click()
'Created by Helen Feddema 17-Nov-2005
'Modified by Helen Feddema 29-Sep-2008

On Error GoTo ErrorHandler

   Dim strText As String
   Dim fd As Office.FileDialog
   Dim txt As Access.TextBox
   
   'Create a FileDialog object as a Folder Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFolderPicker)
   Set txt = Me![txtTemplatePath]
   
   With fd
      .Title = "Browse for folder where Word templates are located"
      .ButtonName = "Select"
      .InitialView = msoFileDialogViewDetails
      '.InitialFileName = strPath
      If .Show = -1 Then
         strPath = CStr(fd.SelectedItems.Item(1))
         txt.Value = strPath
         strPropertyName = "TemplatesPath"
         strPropertyValue = strPath & "\"
         lngDataType = dbText
         Call SetProperty(strPropertyName, lngDataType, _
            strPropertyValue)
      Else
         Debug.Print "User pressed Cancel"
      End If
   End With

On Error Resume Next

   DoCmd.RunCommand acCmdSaveRecord
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in " & Me.ActiveControl.Name & " procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 500 total points
ID: 34874872
The FileDialog object is part of Office XP or higher.  To use it, you need to set a reference to the Office object model.
0
 
LVL 31

Assisted Solution

by:Helen_Feddema
Helen_Feddema earned 500 total points
ID: 34874883
That example is for a FolderPicker FileDialog, but you can also create a FilePicker:  Here is one, though it doesn't write to a textbox:
Public Function SelectWordDoc() As String
'Requires Office XP (2002) or higher
'Requires a reference to the Microsoft Office Object Library
'Created by Helen Feddema 3-Aug-2009
'Last modified 11-Feb-2010

On Error GoTo ErrorHandler

   Dim fd As Office.FileDialog
   Dim varSelectedItem As Variant
   Dim strFileNameAndPath As String
   
   'Create a FileDialog object as a File Picker dialog box.
   Set fd = Application.FileDialog(msoFileDialogFilePicker)
   
   With fd
      'Set AllowMultiSelect to True to allow selection of multiple files
      .AllowMultiSelect = False
      .Title = "Browse for Word Document"
      .ButtonName = "Select"
      .Filters.Clear
      .Filters.Add "Documents", "*.doc; *.docx", 1
      .InitialView = msoFileDialogViewDetails
      If .Show = -1 Then
         'Get selected item in the FileDialogSelectedItems collection
         For Each varSelectedItem In .SelectedItems
            strFileNameAndPath = CStr(varSelectedItem)
         Next varSelectedItem
      Else
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   
   SelectWordDoc= strFileNameAndPath
   
ErrorHandlerExit:
   Set fd = Nothing
   Exit Function

ErrorHandler:
   MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
   Resume ErrorHandlerExit

End Function

Open in new window

0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 34874894
FilePath may be a method of the control you are using, in which case having a variable of the same name might be the problem.  Make sure you declare all variables as the appropriate data types, and give them names that are not the names of properties, methods, etc.
0
 

Author Comment

by:JoanEdington
ID: 34878497
Many thanks for this Helen. All my googling must have brought up solutions for Access prior to Office XP. If I'd known such an easy method was already available I'd have saved so much time. You answered so quickly that I feel embarassed at taking so long to thank you but I am in Scotland and I asked the question at the end of a long frustrating day :-)
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
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…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

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