Solved File exist button in continous form

[dk_bhn]

Hi all

First of all english is not my native lang.

On a school I'm managing PC that students can borrow. For each PC that is borrowed by a student, they sign a physical contract, that is scanned into a .pdf file and placed in a folder in "F:\PathToContacts\" . The PC's are named ABC001, ABC002, ABC003 etc. and the corresponding pdf's are named ABC001.pdf, ABC002.pdf, ABC003.pdf etc. (some numbers can be missing, if the PC has been retired).

The first column in the continous form is titled ABC and shows the PC names (ABC001, ABC002 etc.)

Now I have a continous form with all the PC's and I would like to have a button (or simply a text saying Yes or No) in each row that shows if the pdf exists or not?

Private Sub Form_Load()

Dim ContractPath As String

ContractPath ="F:\PathToContacts\"

If Dir(ContractPath & [ABC] & ".pdf") <> "" Then

Me.PDF.Visible = True

Else

Me.PDF.Visible = False

End If

End Sub

The above code shows the same for all records, which I found out is the value for ABC001.

Any help is highly appreciated

 

[arnelgp]

it is continuous form so every row has the same values.
what you need to do is create a query to save the pdf path (if ever they exists).
first create this function in a Module:

Public function pdfPath(Byval ABC As Variant) As Boolean
Const p As String ="F:\PathToContacts\"
Dim File As String
If IsNull(ABC) Then
   Exit Function
End If
File = Dir$(p & ABC)
IF Len(File) <> 0 Then
   pdfPath = True
End If
End Function

now create a query and call the function from it:

SELECT ABC, IIF(pdfPath([ABC]), [PDF], NULL) As PDFExits FROM yourTableName;

save the query and use it in your continuous form.

 

[dk_bhn]

it is continuous form so every row has the same values.
what you need to do is create a query to save the pdf path (if ever they exists).
first create this function in a Module:

Public function pdfPath(Byval ABC As Variant) As Boolean
Const p As String ="F:\PathToContacts\"
Dim File As String
If IsNull(ABC) Then
   Exit Function
End If
File = Dir$(p & ABC)
IF Len(File) <> 0 Then
   pdfPath = True
End If
End Function

now create a query and call the function from it:

SELECT ABC, IIF(pdfPath([ABC]), [PDF], NULL) As PDFExits FROM yourTableName;

save the query and use it in your continuous form.

Thanks for the fast respone.

Made the module, no problem, but where exactly in the query do I put the line of code to call the function?

 

[arnelgp]

but where exactly in the query do I put the line of code to call the function?

see the SQL on post #2. create the Query and select SQL view and try to mimic the sql.

 

[dk_bhn]

Okay, now I have done the changes, but when I try to enter the query, I get the attached error?

 

[arnelgp]

did you put the function in a Module?

 

[dk_bhn]

Yes, only difference from your screenshot is that I named the module pdfPath

 

[arnelgp]

no, the Module name should not be The Same with the function name.
delete the module and just name the module anything, except pdfPath.

 

[dk_bhn]

Just deleted it and made a new and named it Module1. Now I get this

 

[arnelgp]

ok, you do not have PDF field.
change the query to this:

SELECT ABC, IIF(pdfPath([ABC]), "Yes", "No") As PDFExits FROM yourTableName;

 

[dk_bhn]

Okay, getting there But it says No at all numbers?

 

[arnelgp]

change the function again and add ".pdf" to the filename:

Public function pdfPath(Byval ABC As Variant) As Boolean
Const p As String ="F:\PathToContacts\"
Dim File As String
If IsNull(ABC) Then
   Exit Function
End If
File = Dir$(p & ABC & ".pdf")
IF Len(File) <> 0 Then
   pdfPath = True
End If
End Function

 

[dk_bhn]

Thankyou so much for your patience and for your help

 

[Gasman]

You could have a chec

Just deleted it and made a new and named it Module1. Now I get this

View attachment 116466

Just FYI, you could have just renamed it?

 

[dk_bhn]

I do have an additional question though, I would like to use "Environ$("USERPROFILE")" in the module, because the pdf files are saved in a sharepoint folder

Const p As String ="F:\PathToContacts\"
So it looks something like this
Const p As String = Environ$("USERPROFILE") & "\The\Rest\Of\The\Path\"

But I won't accept it as part of a path

 

[Gasman]

Why not show what the UserProfile looks like?

 

[dk_bhn]

Why not show what the UserProfile looks like?

Maybe I misunderstand your question, but we are multiple users of the database, so to be able to use the same frontend I would like it to be as described above

 

[Gasman]

One should not be using the same frontend. each user should have their own copy.
I am asking to see what the Sharepoint userprofile looks like.

Mine on simple PC is just "C:\Users\Paul"

 

[CJ_London]

but we are multiple users of the database, so to be able to use the same frontend I would like it to be as described above

The database should be split - the data in tables in the backend (BE) and located on a server where users have access and everything else (forms, queries, reports, modules) in the front end (FE) and a copy located on each users local machine.

Unless you clarify why not, the pdf's should be in a folder on the server - perhaps in the same folder as the BE

and you don't need any code with regards what to show

in a textbox control in the detail section, put

=Dir(ContractPath & [ABC] & ".pdf") <> ""

change names to suit.

And in the controls format property put

;"Yes","No"

if you want colour use

;[Green]"Yes",[Red]"No"

 

[dk_bhn]

One should not be using the same frontend. each user should have their own copy.
I am asking to see what the Sharepoint userprofile looks like.

Mine on simple PC is just "C:\Users\Paul"

Okay I didn't express myself right. We do indeed use each our own copy of the file, but the file still need to be universal, since I'm the only guy who knows how to make changes to the front end..

And the sharepoint path looks like this:
C:\Users\MyName\SharepointName\IT - General