Capturing a file path with VBA

[utzja1]

I have a textbox on my form that has an event handler (on click) associated with it. The macro that launches is supposed to open a file dialog box and capture what the user selects. The forms can then use that file path to display a picture to assist in generating a section of a report.

Private Sub Defect1_Click()

Dim fd As FileDialog
Set fd = Application.FileDialog(msoFileDialogFilePicker)
Dim vrtSelectedItem As Variant

With fd
.AllowMultiSelect = False
.Title = "Browse to Select a File"
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
MsgBox "Selected item's path: " & vrtSelectedItem
Me.DefectPhoto1.Value = vrtSelectedItem
Next
End If
End With

Set fd = Nothing

Form.Refresh

End Sub

When clicked, I immediately get an error message on the statement above in bold. It tells me the method or data member not found. I have used this construction before with no issues and was curious if anyone could decipher the issue with this syntax. The field "DefectPhoto1" is intended to be short text field, so the issue may be with the ".Value" syntax since that would imply a number? But if not a number, what would go there, or do I have this screwed up to begin with?

Thanks for taking the time look!

 

[MarkK]

When the period occurs in an identifier you are looking at a qualified object, so 'Me' has a member called 'DefectPhoto1', but 'DefectPhoto1' does not have a member called 'Value'. Me is presumably a form, but what is a 'short text field' object, and can it be a member of a form? What type of object, exactly, is DefectPhoto1?

 

[sneuberg]

I tried your code in the attached database and it works fine. I suggest double checking to make sure you have a control (textbox or combo box) named DefectPhoto1.

 

[utzja1]

Thanks to all for responding. I am going on the assumption that something got corrupted when I built the form, because I deleted the form (it was a pretty simple form) and rebuilt it using the language above. It works fine now. ACCESS is a fickle mistress sometimes, that's all I can say.

 

[MarkK]

FWIW, if you set MultiSelect = false, then you don't need to enumerate the collection because it can never contain more that one element, so your can refactor you code like . . .

Private Sub Defect1_Click()
   With Application.FileDialog(msoFileDialogFilePicker)
      .AllowMultiSelect = False
      .Title = "Browse to Select a File"
      If .Show Then Me.DefectPhoto1.Value = .SelectedItems(1)
   end with
   Me.Refresh
End Sub