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
On Error GoTo 0
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