Check if a file exists and display on a continuous form. (1 Viewer)

P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
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.

Code: 
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:44
Joined
May 7, 2009
Messages
19,235
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
 
P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
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.
 
S

sneuberg

AWF VIP
Local time
Today, 04:44
Joined
Oct 17, 2014
Messages
3,506
Assuming
Code: 
 Dir([FileServer] & Format([ID], "\00000") & ".pdf") = ""

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

Code: 
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:

Code: 
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.
 

Attachments

  • FileExists.accdb
    404 KB · Views: 122
P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
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.

Code: 
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

Code: 
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:44
Joined
May 7, 2009
Messages
19,235
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.
 
S

sneuberg

AWF VIP
Local time
Today, 04:44
Joined
Oct 17, 2014
Messages
3,506
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.

Code: 
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

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:44
Joined
May 7, 2009
Messages
19,235
or you can move the commandbutton to footer section and use the current event of the form to hide/unhide them.
 
P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
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
 
P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
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
 
S

sneuberg

AWF VIP
Local time
Today, 04:44
Joined
Oct 17, 2014
Messages
3,506
PSSMargaret said:
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
Click to expand...

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

sneuberg

AWF VIP
Local time
Today, 04:44
Joined
Oct 17, 2014
Messages
3,506
Here's the new function

Code: 
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
Code: 
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.
 

Attachments

  • FileExists.accdb
    400 KB · Views: 135
P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
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
 
S

sneuberg

AWF VIP
Local time
Today, 04:44
Joined
Oct 17, 2014
Messages
3,506
PSSMargaret said:
Is there something I should do to mark this thread as SOLVED or is there some way to give you credit?
Click to expand...

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 :D
 
P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
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).

Code: 
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


Code: 
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
 
S

sneuberg

AWF VIP
Local time
Today, 04:44
Joined
Oct 17, 2014
Messages
3,506
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
Code: 
Dir([FileServer] & Format([ID], "\00000") & ".pdf")

the same as
Code: 
Dir(FileServer & Format(ID, "\00000") & " -" & ADNo & ".pdf")

now?
 
P

PSSMargaret

Registered User.
Local time
Today, 04:44
Joined
Jul 23, 2016
Messages
74
That was the issue. The FileServer field wasn't included is the search queries. All is good now. Thanks again.
 
S

sneuberg

AWF VIP
Local time
Today, 04:44
Joined
Oct 17, 2014
Messages
3,506
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.

Code: 
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
 
You must log in or register to reply here.

Similar threads

P
Create seperate PDF Files from a report Base on a query with more than 300 records
Replies
5
Views
509
Uncle Gizmo
Uncle Gizmo
P
Solved Multiple instances of accde file
Replies
8
Views
370
theDBguy
theDBguy
DittoBird
Is There a Way to Copy a Linked Image to The Clipboard From a Form Linked Image Control?
2
Replies
30
Views
1,065
moke123
moke123
O
  • Question
VBA code and buttons for downloading, uploading and printing a file attached to the attachment field
Replies
5
Views
714
Onem
O
E
Macro in Excel to replicate Acrobat command: "Merge supported files in acrobat" (.docx, .pdf, .jpg, .jpeg, .png, .xlsx, etc.)
Replies
2
Views
970
561414
5

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom