Checking if file exists from two conditions (1 Viewer)

[nstratton]

I am sure this is posted somewhere but I could not find it.

What I am wanting to do is use the code below to determine if a file exists give the user an option to open the file. It is working like I would expect it to when a body no. has an associated file.

The issue arises when a body no does not have an associated file. It still pops up the msgbox saying a file exists but when I click yes to view it ends the sub.

What should happen is if a body no has no associated file show a msgbox saying no file exists, in this case it is "Keep Working" and the user can then carry on to something else.

I'm sure it is something simple like msgbox code placement but any help will be appreciated.

Private Sub cmdCheckThreePanel_Click()
Dim sMyPath As FileDialog
Dim sPath As Variant
Dim strFolderPath As String
Dim strMessage As String
Dim strTitle As String

strMessage = "A Three Panel exists for this Body No." & Chr(13) & Chr(10) & _
             "Would you like to view this file now?"
             
strTitle = "Three Panel Exists"

strFolderPath = "C:\Users\cole.stratton\Documents\Procurement\Database Folders\Three Panels\" & Me.Supplier & "\"

Response = MsgBox(strMessage, vbYesNo, strTitle)

Set sMyPath = Application.FileDialog(msoFileDialogFilePicker)

Debug.Print "Looking for: " & strFolderPath
strFile = Dir(strFolderPath & "*" & Me.BodyNo & "*")
'Add an 'AND' statement to the If function to check body number in filename
'If BodyNo does NOT have a file, return a no file exists.
If (strFile <> "") Then
    If Response = vbYes Then
        With sMyPath
            .AllowMultiSelect = False
            .InitialFileName = "C:\Users\cole.stratton\Documents\Procurement\Database Folders\Three Panels\" & Me.Supplier
            .Title = "Three Panel Check"
            .Filters.Add "All Files", "*.*"
            .Show
        End With
    Else
      MsgBox "Action Cancelled"
    End If
Else
    MsgBox "Keep working"
End If

End Sub

 

[Isskint]

hi nstratton

Not sure but most times i see DIR used to check for existence of a file, developers use Len() of the return and check for 0. So

If (Len(strFile)=0 Then

Not sure if DIR() returns "" for no match or NULL.

 

[nstratton]

This line is equivalent to using the Len method I do believe (somebody correct me if I am wrong):

If (strFile <> "") Then

The way I understand the code to be working is I go to a record and click the button it is checking the supplier field to see if a folder exists for that supplier. If yes, then the msgbox is displayed saying a three panel exists. A yes click on the msgbox will open the file dialog box.

While yes the folder may exist for the supplier a file may not exist for the body number associated with that supplier. I only want the msgbox to show if the file exists.

I have added three images to maybe help clarify what I am looking for. The third image is a body number that does not have a file associated with it.

 

[nstratton]

I figured it out. I had the code

Response = MsgBox(strMessage, vbYesNo, strTitle)

in a spot where they will always show before the if statements are run.

I moved it under the correct if statement and problem solved.