Use result of CommonDialogAPI.FileName in text field of form

Posted on 2011-02-11
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)
    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
    GoTo NotCanceled

    On Error GoTo 0
    Exit Sub

    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 Sub

    MsgBox Err.Description
    Resume Exit_cmdPhoto_Click
End Sub
Question by:JoanEdington
  • 4
LVL 31

Accepted Solution

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, _
         Debug.Print "User pressed Cancel"
      End If
   End With

On Error Resume Next

   DoCmd.RunCommand acCmdSaveRecord
   Exit Sub

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

End Sub

Open in new window

LVL 31

Assisted Solution

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.
LVL 31

Assisted Solution

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.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
         Debug.Print "User pressed Cancel"
         strFileNameAndPath = ""
      End If
   End With
   SelectWordDoc= strFileNameAndPath
   Set fd = Nothing
   Exit Function

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

End Function

Open in new window

LVL 31

Expert Comment

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.

Author Comment

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 :-)

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access query with left expression 5 29
Create Form using Wizard 14 32
Getting Run-Time Error 13 - Type Mismatch 3 26
Update Access FrontEnd by Version # 9 21
In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

910 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

18 Experts available now in Live!

Get 1:1 Help Now