Conditional Formatting using Dir() to see if File Exists

[ThePoz]

Does anyone know if it is possible to use conditional formatting on fields in a form using the Dir () function if a file exists. And if so how?

Here's my idea: I currently have a sub form with a field that links to files in a folder "Site Reviews". In this field the user types in the date they performed the site review. They are also supposed to put the review letter in the related contractor's folder but that doesn't always happen. So I have it that if you double click on that field Access will run through code building up a filepath (program, contractor, site review folder, then finally the file named with the date of the selected record). If it exists it will open the file, if it doesn't, it will give a message box that the file does not exist, (FYI it will also message out on any step of building the hyperlink (i.e., program folder doesn't exist, contractor folder doesn't exists etc.....)

I used the Dir () function: If Dir(StrFile, vbDirectory) <> vbNullString Then do something, and it works beautifully on the double click event.

But I would like the dates to be conditionally formatted red if the file does not exist and not need to double click the hyperlink to find out. This way I would know that they were missing visually without doing anything. So I tried using the Dir(StrFile, vbDirectory) <> vbNullString with conditional formatting (without the If) and got squat (Tried a few other variations <> "", Len(Dir() etc....nothing...

I then was reading some posts about making another textbox with the control source set to Dir(StrFile, vbDirectory) <> vbNullString etc. so I could give it a 1 or 0 and use that to conditionally format the other field but then I get the #Name? error...

Any ideas? thoughts? Sorry for the long winded post. just wanted to give everyone the picture of what I was trying to accomplish.

As always thank you kindly,

Poz

 

[sneuberg]

I don't think you can do that. I think the limitations of what you can do are revealed if you tried to use the expression builder. The Dir function doesn't show up.

Nonetheless I've attached a simple database where the records in the form that have filenames of existing files are formatted in green. If you are desperate for this functionality it can be done. I just added a boolean field to the table which I use in the conditional formatting. Then I run the follow code in all the relevant events.

Private Sub UpdateFileExists()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim FileExists As Boolean
Set db = CurrentDb
Set rs = db.OpenRecordset("FilePath")
Do While Not rs.EOF
    If Not IsNull(rs!FullFillePath) Then
        If Len(Dir(rs!FullFillePath)) <> 0 Then
            FileExists = True
        Else
            FileExists = False
        End If
        rs.Edit
        rs!FileExists = FileExists
        rs.Update
        rs.MoveNext
    End If
Loop
rs.Close
db.Close


End Sub

You probably knew you could do that.

If you have a lot of records you probably don't want to do that.

 

[ThePoz]

sneuberg,

My apologies I did not return a thanks earlier. Had my sons bday, then pinewood derby, then Super Bowl, then Valentines boxes this week for their school. So a long overdue thank you.

While I did not use your exact example, a few things you said got me thinking. Especially about not wanting to do it that way if you have a lot of records etc.

So I thought if I could only check for the handful of files for each contractor when selected, that would not be many files at all. So I tried to use Dir() in the select query to no avail, however, you can have the Dir() in a public function and then "call" that function in the select query. I also did it that way because unlike the example, I don't have the file paths stored, they are built on the fly using TempVars based on the selected program, contractor, etc...

So I think I just about have it...

But I wanted to say thanks.

Poz

 

[sneuberg]

Wow! That's a great find, or well at least very interesting one. This actually answers your original question since when you enclose the Dir function in a public function it works in conditional format too. I tried it. Unfortunately it's just to slow to be practical. The lag is noticeable with only a few records. The way I did it in my previous post, by updating a boolean field, is actually much faster.

Glad to hear you found something that works for you.

 

[ThePoz]

Cool that you tried it in a conditional formatting formula. I went your route and create a True/False field in my select query and so the conditional formatting is based on that field and is there when the form opens. No lag that I cant detect. (I am a novice at best and do this for fun, plus it works for our purposes. But I'm sure I commit some Access "sins" along the way.) So I am very grateful for all the insight and advice on this forum.

However, that you say you are getting a noticeable delay with only a few records makes me think of another thing I did before using conditional formatting and have been noticing some lag with it and am now thinking that if I can create a Boolean field that the conditional formatting can reference instead of the more complicated process in the conditional formatting that it might be quicker...

Thanks,

Poz

 

[skiandy]

Hi, please could you help. I was looking at your demo sneuberg and it does exactly what I would like it to do. However my table only contains the filename and I generate the link to the file using the following code:-

Private Sub Command7_Click()
Dim strFileName As String
 
strFileName = Dir("C:\AGI\" & Me.[Document Number] & "*")
 
Application.FollowHyperlink ("C:\AGI\" & strFileName)
End Sub

Is it possible to use the same string to populate the file exists column?

 

[sneuberg]

I'm don't understand this. Could you give me some examples of what's in the table and the corresponding path to the file?

 

[skiandy]

Hi sorry I'm new to this.

[Document Number] is the filename without its extension as the files are .pdf, .doc, .dwg etc.

As the file path is generated is there any way that conditional formatting can be applied to highlight if the file exists in C:\AGI\ ? i.e. green for exists / red for doesn't exist.

Thanks for you help.

 

[sneuberg]

It appears that if the file doesn't exist then strFileName would be an empty string. If that is true you could set a field in the table accordingly. Let say the table name is Table1 and you add a Yes/No field named FileExists to it to indicate whether the file exista. The you could set the field with code like:

CurrentDb.Execute "UPDATE Table1 SET [FileExist] = " & Len(strFileName) <> 0 & "WHERE [Document Number] = '" & Me.[Document Number] & "'"

If your record source is a query rather than the table you need to add the field FileExists to it. In the Conditional Formatting you would choose Expression Is and the expression would simply be [FileExists]. Sometimes Conditional Format is stubborn and you have to poke at it to get it working. It should be sufficient that [FiieExists] is a field in the record set, but sometime we've had to put it on the form or record as a hidden field.

 

[skiandy]

Hi Sneuburg,

OK I added these lines of code:

Private Sub UpdateFileExists()
strFileName = Dir("C:\AGI\" & Me.[Document Number] & "*")
CurrentDb.Execute "UPDATE Entries SET [FileExists] = " & Len(strFileName) <> 0 & "WHERE [Document Number] = '" & Me.[Document Number] & "'"


End Sub

However I when I try to run the form I receive the following message:-

"The Microsoft Access database engine cannon find the input table or query 'True'. Make sure that it exists and that its name is spelled correctly.

I added a yes/no field in the table Entries.

It appears that if the file doesn't exist then strFileName would be an empty string. If that is true you could set a field in the table accordingly. Let say the table name is Table1 and you add a Yes/No field named FileExists to it to indicate whether the file exista. The you could set the field with code like:

CurrentDb.Execute "UPDATE Table1 SET [FileExist] = " & Len(strFileName) <> 0 & "WHERE [Document Number] = '" & Me.[Document Number] & "'"

If your record source is a query rather than the table you need to add the field FileExists to it. In the Conditional Formatting you would choose Expression Is and the expression would simply be [FileExists]. Sometimes Conditional Format is stubborn and you have to poke at it to get it working. It should be sufficient that [FiieExists] is a field in the record set, but sometime we've had to put it on the form or record as a hidden field.

 

[sneuberg]

I can't see the problem from the information you provided. Double check the existence and spelling of Entries, FileExist, and Document Number. Especially Document Number needs to be a field in the Entries table or be change to the name of the applicable field. If nothing's wrong with them I think I'll need to see the database. Could you upload your database?

 

[skiandy]

Thanks Sneuberg, please find file attached.

I can't see the problem from the information you provided. Double check the existence and spelling of Entries, FileExist, and Document Number. Especially Document Number needs to be a field in the Entries table or be change to the name of the applicable field. If nothing's wrong with them I think I'll need to see the database. Could you upload your database?

 

[sneuberg]

Change the line to:

CurrentDb.Execute "UPDATE Entries SET [FileExists] = " & (Len(strFileName) <> 0) & " WHERE [Document Number] = '" & Me.[Document Number] & "'"

The boolean condition (Len(strFileName) <> 0) needed to be in parenthesis and a space before the WHERE was needed. Sorry about that.

 

[skiandy]

No problem, thanks.

However it is not updating [FileExists] when I checked the table. The link generated is correct as it opens the file if the file exists in the folder.

 

[sneuberg]

It Updates the FileExists when I open a document from the Entries form but not from the Main form. I suggest adding the code to that button. You should end up with:

Private Sub Command87_Click()


strFileName = Dir("C:\AGI\" & Me.[Document Number] & "*")
CurrentDb.Execute "UPDATE Entries SET [FileExists] = " & (Len(strFileName) <> 0) & " WHERE [Document Number] = '" & Me.[Document Number] & "'"
FollowHyperlink ("c:\AGI\" & [Document Number] & ".pdf")


End Sub

I'll get you something later to bring all of the FileExists fields up to date at once.

 

[sneuberg]

If you want to bring the FileExists fields up to date all at once you can do the following.

Put the follow code in a module

Public Function FilesExists(DocNo As Variant) As Boolean

FilesExists = Len(Dir("C:\AGI\" & DocNo & "*")) <> 0

End Function

Open the query builder switch to SQL view and paste in this SQL

UPDATE Entries SET Entries.FileExists = FilesExists([Entries]![Document Number]);

Run the query.

 

[skiandy]

Thanks, I thought it would execute the command on form load.

 

[skiandy]

Is there any way the button could be hidden if the file does not exist?

 

[sneuberg]

I only way I know of to make the button disappear is to use a textbox in place of the button. You will need to make the border transparent and you can choose whatever foreground and background colors you like. To label it just put ="Open PDF" in the control source. You use the on click event of the text box for you code. To hide the textbox you use conditional formatting to change the foreground and background colors to the color of the detail section.

I tried this once in one of our projects to hide a delete key we didn't want showing on the new record. We abandoned the idea and just had the button code do nothing because we didn't like the behavior of the cursor over the textbox which of course doesn't change to an arrow when you hover over the textbox. You might be able to fix this cursor issue with Screen.MousePointer and a mouse over effect

I suggest you start a new thread with the question of 'How do you conditionally hide a button in continuous forms" so that you get more input on this than mine.