Solved Upload the Path Auto from the TABLE path

[smtazulislam]

I have 1675 records in my table of tblEMPLOYEEs. Every single items like Image, Passport, Contract etc. is separated folder and In the folder have two kinds of file type is like Image, pdf.

I need a function call in my every(Where needed) textbox in Datasource filed like
'For Employee Image (.jpg)

=fncIsLoadLoc("Image", EmployeeID)                   ' Folder Name and Employee ID numbers

'For Employee Passport (pdf)

=fncIsLoadLoc(Passport, EmployeeID)                 ' Folder Name and Employee ID numbers

Public Function fncIsLoadLoc(sFolderName As Control, sEmployeeID As String)

Dim rst As DAO.Recordset
Dim strFullPath As String

    Set rst = CurrentDb.OpenRecordset("tblFolderPathSyncing")

'I dont know how to call below twice variable. Help me here. 
    sFolderName = rst2!Locations & "\" & rst2!SubFolderName & "\"
    sEmployeeID = 

    strFullPath = sFolderName & "\" & sEmployeeID
' Result
    fncIsLoadLoc = strfullPath 
    rst.Close
End Function

Then a button to open this Image or Pdf

Private Sub CmdViewImage_Click()
Dim strPath As String
'Setting path
    strPath = Me.txtEmployeePicture & ""
    If Dir(strPath) = "" Then 
        MsgBox "Your Image folder is not available same numbers of employee picture !" & vbCrLf & "Check and try again !", vbExclamation, "Open Picture"
        Exit Sub
    End If
' Oppening file
    Application.FollowHyperlink strPath

End Sub

Advanced thanks any help.

 

[CJ_London]

somewhat confused about what you are trying to do. I presume you get an error message - so what is it? I also presume the image is of your tblFolderPathSyncing

Do you have Option Explicit at the top of your module?

you are using

Set rst = CurrentDb.OpenRecordset("tblFolderPathSyncing")

then later you use

sFolderName = rst2!Locations & "\" & rst2!SubFolderName & "\"

but rst2 is not declared and not created

you don't appear to be searching for a specific fid? or dbid?

I don't see fields called Locations or subfoldername

Please provide a much clearer explanation of the requirement. Provide relevant data and examples of the outcome required from that data

 

[smtazulislam]

somewhat confused about what you are trying to do. I presume you get an error message - so what is it? I also presume the image is of your tblFolderPathSyncing

tblFolderPathSyncing is my folder path for including database. See that attached picture.

Do you have Option Explicit at the top of your module?

Yes,
Option Compare Database
Option Explicit

you are using

Set rst = CurrentDb.OpenRecordset("tblFolderPathSyncing")

then later you use

sFolderName = rst2!Locations & "\" & rst2!SubFolderName & "\"

this is mistake. because before I using two table and call openrecordset "2". forget to delete second recordset variable.

Edit :

Public Function fncIsLoadLoc(sFolderName As Control, sEmployeeID As String)

Dim rst As DAO.Recordset
Dim strFullPath As String

    Set rst = CurrentDb.OpenRecordset("tblFolderPathSyncing")

'I dont know how to call below twice variable. Help me here.
    sFolderName = rst!Locations & "\" & rst!SubFolderName & "\"
    sEmployeeID =

    strFullPath = sFolderName & "\" & sEmployeeID
' Result
    fncIsLoadLoc = strfullPath
    rst.Close
End Function

 

[smtazulislam]

Actually, I would like to do,
if I open my frmEmployee then path of Image textbox display like : "E:\TEST LOCATION\EMPLOYEE FOLDER\IMAGE\1.jpg"
And path of pdf textbox display like : "E:\TEST LOCATION\EMPLOYEE FOLDER\PASSPORT\1.pdf"

My Ideas is something like that, fncIsLoadLoc "Collect Main Path from tblFolderPathSyncing" "folder Name want to syncing" "Collect EmpoyeeID from tblEmployee "

 

[moke123]

There are a number of ways to do what we think you want to do.
We need to know more about your folder structure. Is there one image folder for all or is there a folder for each employee with subfolders for image, passport, etc. How are you relating each item to each employee?

 

[smtazulislam]

There are a number of ways to do what we think you want to do.
We need to know more about your folder structure. Is there one image folder for all or is there a folder for each employee with subfolders for image, passport, etc.

There is ONE folder for ALL employees, as same EACH items for ONE FOLDER, but its distributed by employee ID

 

[CJ_London]

Sorry, I'm going to drop out., not getting answers to questions, in particular

Provide relevant data and examples of the outcome required from that data

All we are getting is more descriptions of what you want

 

[smtazulislam]

i also tried this module link do it. But its just display Path and do including the Employee ID

 

[smtazulislam]

Its work! .

Private Sub CallDisplayImage()
Me.txtEmployeePicture = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 1") & "\" & Me.EmployeeID & ".jpg"
Me!txtImageNote = DisplayImage(Me!ImgEmployeePicture, Me!txtEmployeePicture)
End Sub

EDIT :
can someone help me to displaying PDF file in the form ?
I tried it like -

Private Sub CallDisplayImage()
 Me.txtEmployeePicture = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 1") & "\" & Me.EmployeeID & ".jpg"
 Me!txtImageNote = DisplayImage(Me!ImgEmployeePicture, Me!txtEmployeePicture)
 
 Me.txtPassportPDF = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 2") & "\" & Me.EmployeeID & ".pdf"
 Me!txtImageNote = DisplayImage(Me!ImgEmployeePicture, Me!txtEmployeePicture)
End Sub

See error attached.

 

[CJ_London]

You display pdf’s in a web control

 

[moke123]

Could you post a couple examples of the paths to the files of 2 employees . What are you naming the files?

 

[smtazulislam]

Could you post a couple examples of the paths to the files of 2 employees . What are you naming the files?

My file name is EmployeeID . See the red marked.
Me.txtEmployeePicture = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 1") & "\" & Me.EmployeeID & ".jpg"
Me.txtPassportPDF = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 2") & "\" & Me.EmployeeID & ".pdf"

 

[smtazulislam]

Yes

You display pdf’s in a web control

Yes, fields name is "WebBrowser1"

 

[moke123]

Never mind. Seems like your overcomplicating it.

 

[smtazulislam]

Never mind. Seems like your overcomplicating it.

You maybe not get my explanation. my path is displayed in the textbox as like : "E:\TEST LOCATION\EMPLOYEE FOLDER\PASSPORT\1630.pdf "
But the problem is not displayed in the Form within " WebBrowser1".

 

[CJ_London]

But the problem is not displayed in the Form within " WebBrowser1".

so what code are you using to populate the webbrowser control? what is the webbrowser control controlsource value?

 

[smtazulislam]

Thank you @CJ_London & @moke123 .
Its work now.

Its problem with my pdf READER, when I re-installed and Installed new version then form displayed pdf file .

 

[smtazulislam]

My file name is EmployeeID . See the red marked.
Me.txtEmployeePicture = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 1") & "\" & Me.EmployeeID & ".jpg"
Me.txtPassportPDF = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 2") & "\" & Me.EmployeeID & ".pdf"

need a little help..
my some file format is " .png and .jpeg " And VBA there can added ONLY ONE.
How can I added multi EXTENSION for image in same a line.

 

[moke123]

Still too many unanswered questions. Do you have 1600+ records in your employee table as your initial post implies?
Out of the 9 different employee folders, 8 of them would seem to contain just one file for each employee while the documents folder may hold one or more for each employee, or maybe none.

The simplest way would be to store the file name or the fullpath in a table.
Another way would be to loop through the extensions and test if the file exists.
Yet another way would be using fso to match the fso.GetBaseName() to the EmployeeID .

If you go the fso way you could do something like this
(Note that a base name is the file name without the extension)

In a standard module:

Function GetPathWithExt(CheckFolder As String, FileBaseName As String) As String

    Dim fso As New FileSystemObject
    Dim fol As Folder, fil As File

    Set fol = fso.GetFolder(CheckFolder)

    For Each fil In fol.Files
        If fso.GetBaseName(fil.Path) = FileBaseName Then
            GetPathWithExt = fil.Path
            Exit For
        End If

    Next
    
End Function

In your form module's CallDisplayImage procedure:

Private Sub CallDisplayImage()

    Dim IFolder As String, PPFolder As String

    IFolder = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 1")
    PPFolder = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 2")

    Me.ImgEmployeePicture.Picture = GetPathWithExt(IFolder, Me.EmployeeID)
    Me.txtPassportPDF = GetPathWithExt(PPFolder, Me.EmployeeID)

End Sub

 

[smtazulislam]

Still too many unanswered questions. Do you have 1600+ records in your employee table as your initial post implies?
Out of the 9 different employee folders, 8 of them would seem to contain just one file for each employee while the documents folder may hold one or more for each employee, or maybe none.

I have total 8 sub-folder under of EMPLOYEE FOLDER. now here I added ONE more folder for VEHICLE LICENSE.
EACH folder MANAGED add picture or pdf file by EMPLOYEE ID.
in DOCUMENT is various file download from database pdf or word or excel.
Example : Report for Vacation or Absent : its arranged by report Name & Employee ID & Date()
Result : rptVACATION- 1600 - 20230124 Or rptAbsent- 1600 - 20230124

The simplest way would be to store the file name or the fullpath in a table.
Another way would be to loop through the extensions and test if the file exists.
Yet another way would be using fso to match the fso.GetBaseName() to the EmployeeID .

If you go the fso way you could do something like this
(Note that a base name is the file name without the extension)

In a standard module:

Function GetPathWithExt(CheckFolder As String, FileBaseName As String) As String

    Dim fso As New FileSystemObject
    Dim fol As Folder, fil As File

    Set fol = fso.GetFolder(CheckFolder)

    For Each fil In fol.Files
        If fso.GetBaseName(fil.Path) = FileBaseName Then
            GetPathWithExt = fil.Path
            Exit For
        End If

    Next
   
End Function

In your form module's CallDisplayImage procedure:

Private Sub CallDisplayImage()

    Dim IFolder As String, PPFolder As String

    IFolder = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 1")
    PPFolder = DLookup("FolderPath", "tblFolderPathSyncing", "FID = 2")

    Me.ImgEmployeePicture.Picture = GetPathWithExt(IFolder, Me.EmployeeID)
    Me.txtPassportPDF = GetPathWithExt(PPFolder, Me.EmployeeID)

End Sub

It is same result given me with before code. I would looking there 3 or 4 extensions file work Like .jpeg .jpg .png .bmp format