Check if a file exists and display on a continuous form.

[PSSMargaret]

Hello all,

I am in need of assistance. I'm updating an old database. Each record in the database has a corresponding .pdf document that is saved in a folder outside of the database on a file server. A separate table in the database contains the location of the storage folder for all of the .PDF documents. The main form displays each record with a PDF image on the form that contains an OnClick event that will open the .PDF by using FollowHyperlink coding. The form contains an OnCurrent event that shows the PDF image if the document exists and will show a different image if the documents doesn't exists. Code below.

Private Sub Form_Current()

If Dir([FileServer] & Format([ID], "\00000") & ".pdf") = "" Then
     Me.cmdpdfX.Visible = True
     Me.cmdpdf.Visible = False

Else
     Me.cmdpdfX.Visible = False
     Me.cmdpdf.Visible = True

End If
   
End Sub

Each record may also have other associated documents besides the main document which are also stored in the same storage folder. The main form contains a continuous subform that lists the associated documents. As many of you will guess, I have been unsuccessful in getting similar code like the above to work for a continuous subform. After researching quite a bit, I have discovered that above won't work for a continuous form. I have also read that I could do something similar with a query and a textbox but I have also been unsuccessful with that as well. The associated document have a different numbering system than the main document so I have provided the directory patch and file criteria below.

If Dir([FileServer] & Format([ID], "\00000") & "-" & [ADNo] & ".pdf")

If anyone can assist, can you please confirm that the above technique cannot be done with a continuous form. If it can be done, the two command buttons for this form are:

cmdADpdf
cmdADpdfX

If this cannot be done on a continuous form, can you provide some guidance on how this can be done with a query. I'd like to return a "?" if the document doesn't exist and a PDF if the document does exists. I can then create a textbox from there.

Sorry this post is so long. I wanted to provide as much info as possible. Any assistance will be greatly appreciated.

Margaret

 

[arnelgp]

i think your code is good, just to simplify:

Private Sub Form_Current()
dim bolVisible As Boolean
bolVisible= (Dir(Me![FileServer] & Format(Me![ID], "\00000") & ".pdf") <> "")
Me.cmdpdfX.Visible = Not bolVisible
Me.cmdpdf.Visible = bolVisible
End Sub

 

[PSSMargaret]

Thanks arnelgp. The code I displayed was an example and works fine but the same code with different command buttons doesn't work on a continuous subform. My question is really about how to implement similar code on a continuous form and if that won't work, how can I implement the same idea in a query.

 

[sneuberg]

Assuming

 Dir([FileServer] & Format([ID], "\00000") & ".pdf") = ""

works for you, you can create a function like that below and put it in a module.

Public Function OnServer(ID As Long, FileServer As String) As String

If Dir(FileServer & Format(ID, "\00000") & ".pdf") = "" Then
    OnServer = "?"
Else
    OnServer = FileServer & Format(ID, "\00000") & ".pdf"
    
End If

End Function

You would use this is a query with an expression like:

File Exists: OnServer([tblFiles]![ID],[tblFiles]![FileServer])

Where you would need to substitute tblFiles with the table name in your system that has the ID and FileServer fields. This is demonstrated in the attached database. Please be aware that this could be a slow query. The Dir function takes a little time.

 

[PSSMargaret]

Thank you for your response sneuberg,

I have created a module and entered the code below. I changed it a bit because I'd like a ? to display if the file doesn't exist and a PDF if the file does exist. Not sure I changed it correctly though. Also, I think I confused the matter by including the coding of my main form that does work. The file names for the subform are different.

Public Function OnServer(ID As Long, FileServer As String) As String

If Dir(FileServer & Format(ID, "\00000") & " -" & " ADNo" & ".pdf") = "" Then
    OnServer = "?"
Else
    OnServer = "PDF"
    
End If

End Function

I entered the below in the query

File Exists: OnServer([tblAssociatedDocuments]![ID],[tblCompanyInfo]![FileServer])

It doesn't seem to be working. When I run the query, each record is displaying a ?, however, I'm further along than I was before.

Margaret

 

[arnelgp]

i think there is no cure for hiding/unhiding command button on contiuous form. either all is hidden or not. the closest you can do, is use a textbox control and make it appear as command button. use conditional format on the textbox.

 

[sneuberg]

I suggest you put a Debug.Print statement in the code as shown below, run the query, and look at the results in the Immediate Window (Go to the VBA Editor and press Ctrl G). If what you see is not the path to the files then you need to adjust the string in the Dir function accordingly.

Public Function OnServer(ID As Long, FileServer As String) As String

Debug.Print FileServer & Format(ID, "\00000") & " -" & " ADNo" & ".pdf"

If Dir(FileServer & Format(ID, "\00000") & " -" & " ADNo" & ".pdf") = "" Then
    OnServer = "?"
Else
    OnServer = "PDF"
    
End If

End Function

 

[arnelgp]

or you can move the commandbutton to footer section and use the current event of the form to hide/unhide them.

 

[PSSMargaret]

sneuberg,

For some reason it's not picking up the second to the last segment of the path; the ADNo. ADNo is a field in the table and the query.

C:\Users\muggs\Dropbox\Project Support Solutions\Tools\Agreements Database\Agreements\00001-.pdf

 

[PSSMargaret]

arnelgp

I played with conditional formatting but couldn't get that to work either. What would the expression be to check if a file exists for conditional formatting? Putting the command button is not an option since some records will have multiple records in the subform.

Margaret

 

[sneuberg]

sneuberg,

For some reason it's not picking up the second to the last segment of the path; the ADNo. ADNo is a field in the table and the query.

C:\Users\muggs\Dropbox\Project Support Solutions\Tools\Agreements Database\Agreements\00001-.pdf

Well that changes things. I'll get you a new function shortly.

 

[sneuberg]

Here's the new function

Public Function OnServer(ID As Long, FileServer As String, ADNo As String) As String

Debug.Print FileServer & Format(ID, "\00000") & " -" & ADNo & ".pdf"
If Dir(FileServer & Format(ID, "\00000") & " -" & ADNo & ".pdf") = "" Then
    OnServer = "?"
Else
    OnServer = "PDF"
    
End If

End Function

I left in the Debug.Print so that you can see if it does what you want. You can delete that line once you get it working.

To use this in the query you would add an expression like

File Exists: OnServer([tblFiles]![ID],[tblFiles]![FileServer],[ADNo])

which differs from the previous version by the addition of the ADNo argument.


The test database is attached.

 

[PSSMargaret]

sneuberg,

SHAZAM! It worked perfectly. I can't thank you enough. Now, I need to review your code and learn from it.

I'm new to the board. Is there something I should do to mark this thread as SOLVED or is there some way to give you credit?

I may be posting another issue after I do some more research. I do try very hard to research as much as possible before going to forums for help.

Margaret

 

[PSSMargaret]

I saw how to mark it as SOLVED.

 

[sneuberg]

Is there something I should do to mark this thread as SOLVED or is there some way to give you credit?

You can click the Thanks button on the right of posts you think were helpful. I think we get some sort of prize each time we get a thousand thanks

 

[PSSMargaret]

Oops, I did discover a glitch.

The main form has an OnCurrent event (code below) that is displaying an error on the first line of code when I perform a search by clicking a cmd button (second set of code below).

Private Sub Form_Current()

[COLOR="Red"]If Dir([FileServer] & Format([ID], "\00000") & ".pdf") = "" Then[/COLOR]
     Me.cmdpdfX.Visible = True
     Me.cmdpdf.Visible = False

Else
     Me.cmdpdfX.Visible = False
     Me.cmdpdf.Visible = True

End If
   
End Sub

Private Sub cmdAgreeNo_Click()

On Error GoTo Err_cmdAgreeNo_Click

    cmdReset.ForeColor = vbRed
    Dim stDocName As String

    'stDocName = "qAgreementNo"
    'DoCmd.OpenQuery stDocName, acNormal, acEdit
    Me.Form.RecordSource = "qAgreementNo"
    'Me.Form.Requery

Exit_cmdAgreeNo_Click:
    Exit Sub

Err_cmdAgreeNo_Click:
    MsgBox Err.Description
    Resume Exit_cmdAgreeNo_Click

End Sub

 

[sneuberg]

What's this record source "qAgreementNo" and does it have fields [FileServer] and ID in it? Can these fields be blank (Null)? And why isn't this

Dir([FileServer] & Format([ID], "\00000") & ".pdf")

the same as

Dir(FileServer & Format(ID, "\00000") & " -" & ADNo & ".pdf")

now?

 

[PSSMargaret]

That was the issue. The FileServer field wasn't included is the search queries. All is good now. Thanks again.

 

[sneuberg]

This won't fix the problem on the main form, but if the fields FileServer, ID or ADNo are nulls this version of the function won't give you a #Error in the query if they are null.

Public Function OnServer(ID As Variant, FileServer As Variant, ADNo As Variant) As String

If Dir(FileServer & Format(ID, "\00000") & " -" & ADNo & ".pdf") = "" Then
    OnServer = "?"
Else
    OnServer = "PDF"
    
End If

End Function