Form indicator if a specific file exist

[dim]

Hi,

I have a form with more records lines. For each record I have a button what will open a specific file if exist.

Dim strFoldername As String
strFoldername = Me.Scan

If Dir("M:\Applications Access\Credit autorisations\Documents\" & strFoldername, vbDirectory) = "" Then
MsgBox "Missing document"
Else
FollowHyperlink "M:\Applications Access\Credit autorisations\Documents\" & strFoldername
End If

To verify if the file exist, instate to press the button, I like to indicate on a new field [Ctrl_Doc], the presence of respective file. (Ex: True or False). I tried this, but doesn’t work:
-----------------------------------------------------------------------------
Private Sub Form_Load()
If Len(Dir("M:\Applications Access\Credit autorisations\Documents\" & strFoldername, vbDirectory) >0) Then
[Ctrl_Doc]= True
Else
[Ctrl_Doc]= False
End If

--------------------------------------------------
Can you help me please?

 

[pbaldy]

You didn't give strFoldername a value in that code.

 

[dim]

Ok, I tried this code on load form:

-----------------------------------------------
Private Sub Form_Load()

Dim strFoldername As String
strFoldername = Me.Scan

If Len(Dir("M:\Applications Access\Credit autorisations\Documents\" & strFoldername, vbDirectory)) > 0 Then
[Ctrl_Doc] = "Yes"
Else
[Ctrl_Doc] = "No"
End If
End Sub
-----------------------------------------------------
The problem is that [Ctrl_Doc] is showing for all the records "Yes", even if the folder or the file doesen't exist.
What I did wrong?

 

[pbaldy]

Try the current event, which will fire as the user changes records. If you're in continuous or datasheet view, this isn't going to work at all. You'd have to try it in Conditional Formatting.

 

[dim]

Thank You,

Yes, I'm in continuous view, so when I use the same code "on current" I see the right result when I click on a specific record. In others words all the lines will show the same [Ctrl_Doc] value depending the selected record.
There is a way to show individually, the right value on I open that form?

Thanks

 

[pbaldy]

Like I said, in a form you'd have to see if Conditional Formatting will work. In a report, code in the detail format event would work, but forms have no such event.

 

[dim]

I tried to add the conditional format to the field [Ctrl_Doc], but is the same problem.
II tried too after I saved that form as a report and ...still not working.

Can you help me with another solution...

Thank You

 

[pbaldy]

What expression did you try in Conditional Formatting?

 

[dim]

After I changed my code with numerical values:
------------------------------------------
Private Sub Form_Current()
Dim strFoldername As String
strFoldername = Me.Scan
If Len(Dir("M:\Applications Access\Credit autorisations\Documents\" & strFoldername, vbDirectory)) > 0 Then
[Ctrl_Doc] = 1
Else
[Ctrl_Doc] = 0
End If
End Sub
----------------------------------------------------------------
I selected [Ctrl_doc] field, then, Format, then "Conditional Formating" and I chose "Field value is" and "equal to" and 1 and I changed the color

Can you please explain how I have to do it?

 

[pbaldy]

That code only fires for the current record. You could add a textbox with a control source like:

=Len(Dir("M:\Applications Access\Credit autorisations\Documents\" & [Scan], vbDirectory)) > 0

And then format on the result of that.

 

[dim]

The text box is my [Ctrl_Doc] where i tried with control source as you suggested:

=Len(Dir("M:\Applications Access\Credit autorisations\Documents\" & [Scan], vbDirectory))

The result is #Name?

Sure if this will work, than I can add the conditional format if...(>0)...

To have a global status for all the records when the user will open that form, maybe there is completlly different other solution...
I'm not a programmer, but maybe a special module could be a solution...or...?

 

[pbaldy]

Curious; in testing, I can't get the Dir() function to work in a control source. Certainly a function could be written that accepted the value of Scan as an input and returned 0 or 1 (or whatever) based on whether that file was found. Here's a start:

http://www.baldyweb.com/Function.htm