Link to home
Start Free TrialLog in
Avatar of JoanEdington
JoanEdington

asked on

Use result of CommonDialogAPI.FileName in text field of form

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
ASKER CERTIFIED SOLUTION
Avatar of Helen Feddema
Helen Feddema
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of JoanEdington
JoanEdington

ASKER

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