0

I have a small problem with my code (MS Access VBA). It's not a big deal, but it's not something that should be seen with a cancellation request.

The initial part of the code allows me to extract the path and filename of an image which works beautifully.

Private Sub Image2_DblClick(Cancel As Integer)

'Source: http://stackoverflow.com/questions/14915179/ms-access-browse-for-file-and-get-file-name-and-path

    Dim f As Object
    Dim strFile As String
    Dim strFolder As String
    Dim varItem As Variant

    Set f = Application.FileDialog(3)
    f.AllowMultiSelect = False
    If f.Show Then
        For Each varItem In f.SelectedItems
            strFile = Dir(varItem)
            strFolder = Left(varItem, Len(varItem) - Len(strFile))
            TempVars.Add "imagePath2", strFolder & strFile

       Next
    End If
    Set f = Nothing

The next portion inserts that string into the specified field in my query. This also works beautifully.

With DoCmd
    .SetWarnings False
    .OpenQuery "updateQueryVarietiesImage2"
    .SetWarnings True
     DoCmd.RunCommand acCmdRefresh
     Me.Requery

End With

End Sub

The problem I am having is if I cancel selecting the image from the generated pop-up window. After cancelling I get "Run-time error '3464': Data type mismatch in criteria expression"

Clicking on "Debug" highlights 

.OpenQuery "updateQueryVarietiesImage2"

I'm sure the error has to do with the fact that the query didn't run due to the cancellation, but I don't want the error to show up. What code should I be using to stop the error from coming up if the user hits Cancel?

Erik A
  • 31,639
  • 12
  • 42
  • 67
Hal Jam
  • 28
  • 4

1 Answers1

1

Couldn't you just skip the code if no file is selected:

If f.SelectedItems.Count > 0 Then
    With DoCmd
        .SetWarnings False
        .OpenQuery "updateQueryVarietiesImage2"
        .SetWarnings True
        .RunCommand acCmdRefresh
    End With
    Me.Requery        
End If
Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Perfect!....Well almost perfect. I was still getting an error after replacing my code with yours but I quickly found out I just had to remove "Set f = Nothing" from the first portion and your code worked perfectly. FYI removing "Set f = Nothing" from my initial code still produced the error so it was your code that did the trick. – Hal Jam Jan 02 '17 at 15:13
  • Great. Then please mark as answered. Happy New Year! – Gustav Jan 02 '17 at 15:31