Checking if a File exists

[Sam Summers]

Hi guys,

All i am trying to do here is let the user know if there are any certs being printed or if there are none.
It all prints fine if there are certs, but if there are none, i still get the message "Printing Certs shortly" ??????

Thanks in advance.

*****************************************************

Public Function PrintFile(sFileName As String)
On Error GoTo Err_PrintFile

Dim I As String
Dim S As String

I = Me.First_Name.Value
S = Me.Surname.Value

sFileName = "F:\Personnel Certs\Rope Access\" & I & S & "\" & I & S & ".pdf"

PrintFile = ShellExecute(Application.hWndAccessApp, "Print", sFileName, "", "C:\", 0)

Exit_PrintFile:
Exit Function

Err_PrintFile:
MsgBox "Sorry no files currently stored for this employee", vbInformation + vbOKOnly, "IPDMS Personnel Database"
Resume Exit_PrintFile

End Function

Public Function FileExists(strPath As String, Optional lngType As Long) As Boolean
On Error Resume Next
FileExists = Len(Dir(strPath, lngType)) > 0
End Function

******************************************************

Private Sub PrintCerts_Click()
Dim sFileName As String

If FileExists(sFileName) = False Then
MsgBox "Sorry no files currently stored for this employee", vbInformation + vbOKOnly, "IPDMS Personnel Database"
Else
Me.PrintFile (sFileName)
MsgBox "Printing Certs shortly", vbInformation + vbOKOnly, "IPDMS Personnel Database"
End If

End Sub

 

[RuralGuy]

Try:

Public Function FileExists(strPath As String, Optional lngType As Long) As Boolean
On Error Resume Next
FileExists = Dir(strPath) <> vbNullString
End Function

 

[Sam Summers]

Thanks for that.

It still does the same - "Printing Certs shortly" comes up even though when i test it on my name which i know has no certs stored in the location. ???

 

[Rickster57]

certs

In the On Click event of your command button :

Check to see if the files exists and if not then exit the subroutine

If dir("F:\folderofinterest")="" then
msgbox("There are no certs to print.")
exit sub
end if

If it exists then print the report


Put that before your Printfile statement

 

[RuralGuy]

Actually, your original code should have worked! Mine is simply wrong! Can you single step the code and see what is happening?

 

[Sam Summers]

Thanks guys, but this is driving me mad !!!!
I have tried loads of stuff and must be missing something somewhere, it still comes up with the "printing Certs shortly" even if i dont exist.

This is the latest code i've tried:-

If Dir(sFileName) = "" Then
MsgBox "Sorry no files currently stored for this employee", vbInformation + vbOKOnly, "IPDMS Personnel Database"
ElseIf FileExists(sFileName) = True Then
Me.PrintFile (sFileName)
MsgBox "Printing Certs shortly", vbInformation + vbOKOnly, "IPDMS Personnel Database"
Else
Exit Sub
End If

 

[gemma-the-husky]

this worked ok for me - I've done it as separate if then statements to avoid the elseifs. You don't seem to have enough end ifs - does it compile without errors?

IF YOU ARE STILL HAVING PROBLEMS, put a breakpoint in the code, and run it line by line - easiest way to see what the code is doing.

If Dir(sfilename) = "" Then
MsgBox "Sorry no files currently stored for this employee", vbInformation + vbOKOnly, "IPDMS Personnel Database"
Exit Sub
End If

NOTE THAT this test should be unnecessary as the dir test already reported success!
If FileExists(sfilename) Then
'PrintFile (sfilename)
MsgBox "Printing Certs shortly", vbInformation + vbOKOnly, "IPDMS Personnel Database"
Else
MsgBox "Unexpected - File not found", vbInformation + vbOKOnly, "IPDMS Personnel Database"
End If


JUST ONE OTHER THOUGHT - I wonder if the embedded space in the directory/path is causing problems with these statments somewhere- you know how windows doesn't like spaces in file paths.

 

[Sam Summers]

Hi there,

Thanks for replying. This whole thing is strange!!
Tried your code and it went straight to "printing certs" again??
Inserted breakpoint at:- If FileExists(sFileName) = "" Then,
and sFileName did equal "",
Then i comented out this line:- <> vbNullString,
in the included module below

Public Function FileExists(strPath As String, Optional lngType As Long) As Boolean
On Error Resume Next
FileExists = Dir(strPath) '<> vbNullString
End Function

and this did exit at your Msgbox "Unexpected - File not found", vbInformation + vbOKOnly, "IPDMS Personnel Database"

But when i tested it on a file that i know exists (which does print), it did the same. So thats not working.
Now i have this code in place but this is throwing an error at the first line?

If FileExists(sFileName) = "" Then
MsgBox "Sorry no files currently stored for this employee", vbInformation + vbOKOnly, "IPDMS Personnel Database"
Else
PrintFile (sFileName)
MsgBox "Printing Certs shortly", vbInformation + vbOKOnly, "IPDMS Personnel Database"
End If

Still stumped for something that should be simple??????

 

[gemma-the-husky]

do you really have the extra single quote in your fileexists test. That will cause problems.

Perhaps there is a problem in there, and its returning false for the test because you are not explicitly setting it if the error traps. This will intercept the errror.

Adter the breakpoint, The F8 will continue to step thorugh the fileexists code, so you can see what is going on in there, if thats the problem

Public Function FileExists(strPath As String, Optional lngType As Long) As Boolean
On Error Resume fail
FileExists = Dir(strPath) '<> vbNullString

exithere:
exit function

fail:
fileexists= false
resume exithere
End Function

 

[Sam Summers]

Thanks again,

It still throws an error at the same line (type mismatch). Even after i took the comment out at the vbNullstring line.

The entire code in the form that i am using for this procedure is this:-

******************************************************

Public Function PrintFile(sFileName As String)
On Error GoTo Err_PrintFile

Dim I As String
Dim S As String

I = Me.First_Name.Value
S = Me.Surname.Value

sFileName = "F:\Personnel Certs\Rope Access\" & I & S & "\" & I & S & ".pdf"

PrintFile = ShellExecute(Application.hWndAccessApp, "Print", sFileName, "", "C:\", 0)

Exit_PrintFile:
Exit Function

Err_PrintFile:
MsgBox "Sorry no files currently stored for this employee", vbInformation + vbOKOnly, "IPDMS"
Resume Exit_PrintFile

End Function

****************************************************

Public Function FileExists(strPath As String, Optional lngType As Long) As Boolean
On Error Resume Next
FileExists = Dir(strPath) <> vbNullString
Exit_FileExists:
Exit Function

FileExists = False
Resume Exit_FileExists
End Function

*****************************************************

Private Sub PrintCerts_Click()
Dim sFileName As String

If FileExists(sFileName) = "" Then
MsgBox "Sorry no files currently stored for this employee", vbInformation + vbOKOnly, "IPDMS Personnel Database"
Else
PrintFile (sFileName)
MsgBox "Printing Certs shortly", vbInformation + vbOKOnly, "IPDMS Personnel Database"
End If

End Sub

 

[meloncolly]

the fileexists function is the problem

this line makes no sense (to me)

FileExists = Dir(strPath) <> vbNullString

If you use the DIR function and no file is found, then it returns ""

I'd try:

Public Function FileExists(strPath As String, Optional lngType As Long) As Boolean
Dim Filename as string
On Error Resume Next
FileName = Dir(strPath)
if Filename = "" then
FileExists = False
else
FileExists = true
end if
End Function

 

[Sam Summers]

Tried this and it still doesn't work.
Even though i dont exist it displays the message "Printing files shortly" and thats all. ?????????????
Should be so simple as well.

 

[gemma-the-husky]

NOTE: there's nothing wrong this line

FileExists = Dir(strPath) <> vbNullString

fileexists is a boolean which is set to true if the dir function returns a value.
which is correct.

------------------------
right. I think i have it!

In printcerts, you have dimmed sfilename, and then called dir with sfilename as a blank! in these circumstance dir actually just works as dir *.* and finds the first file in the current dir, and therefore file exists is always returned as true.

Now, within printfile, you launch the shellexecute etc which sets anther process runnnig. NOW ACCESS doesn't wait for a shelled process, it just carries on, hence you come back to the
msgbox("printing files shortly") in Access

however your shell is probably failing without you being aware of it.

so when you say its throwing an error, I don't think you mean the code is halting with an error do you? its just not doing what you expect. i think.

Put a breakpoint at the top of printcerts, and trace through line by line, then you'll see whats happening i think.

 

[Steve R.]

The code below works. The reason for posting is that it required tweaks that I was able to find courtesy of this forum. I had a different version of determining if a file exists, but RuralGuy's version, which I adopted, was simplier.

First, I needed to add the TRIM function to get the image to open. It took a while to figure that one out since you don't see spaces.
Second, as a result of re-writing the code I began to experience a "Write Error Conflict" for some reason. My thanks to a post by Pat Hartman, the solution was: DoCmd.RunCommand acCmdSaveRecord

FullDirectoryPath = CurrentProject.Path & Me.PICTURELOCATION
    FileExistsbol = Dir(FullDirectoryPath) <> vbNullString
    If FileExistsbol Then
            Me.Image70.Picture = Trim(FullDirectoryPath)
        Else
            DoCmd.RunCommand acCmdSaveRecord
            MSG = "Missing Image"
            TTL = "MISSING IMAGE"
            Me.Image70.Visible = False
            MsgBox MSG, vbOKOnly, TTL
            intBookId = Me.BOOKID
            strUnderLyingfrm = Me.Name
            stDocName = "PICTUREADDFRM"
            stLinkCriteria = "BOOKID= " & intBookId
            DoCmd.OpenForm stDocName, acNormal, , stLinkCriteria
        End If

PS: If you are wondering why there is a variable FullDirectoryPath is that the database has been designed so that the images are stored in directories under the database. Thus if the database is moved to another computer or another location on the current computer, the path to the image will remain valid. Thanks to GHUDSON's post of June 2002.