Solved

Use result of CommonDialogAPI.FileName in text field of form

Posted on 2011-02-11
5
402 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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 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