How to create Folder exist from TABLE and Syncing in Data Form

[smtazulislam]

Hello, Peace be upon you.
Here I found many create Folder sample. but unfortunately not exist with my required.

I have fixed location in my PC. I would like to create Subfolder under the fixed Locations Folder in the table (tblDBMainLocation). Folder name should taken from TABLE ..
Yellow mark is my main Location.
See the table Relations



Then this subfolder will create in under the subfolder as table recorded name.

Example :

Then these paths will syncing and upload automatically in the form and display the employee photo and some of the PDF file.
How to connected with in the form ?

My sample database attached.
Any help will appreciate..

 

[The_Doc_Man]

I do not often open an uploaded database. So the names I use might not match yours, but I hope you will see what I suggest.

In general, you can use the VBA command-statement MKDIR to create a folder. You must determine the fully qualified path for the new folder, but from what you describe, you should have all you need.

MkDir statement (VBA)

Office VBA reference topic

learn.microsoft.com

In your case, you want to use the location of the database as a base path, which you can determine with application.currentproject.path

CurrentProject.Path property (Access)

Office VBA reference topic

learn.microsoft.com

If this is a split database, you might really want to find the back-end, which you can do using the connection string from any table residing in the back end. If you use this method, you must "clean up the string by using an InStr function to find the ";Data Source=" part and take the right-hand side of that string.

Content retirement

The content that you're looking for is now retired.

learn.microsoft.com

So... you get a string to contain your current project path or back-end connection string. You pick up the field from your table that contains the sub-folder name. I don't know how you are set up to do this, but if it comes from an open recordset It might look about like this:

newpath = application.currentproject.path & "/" & myrecordset.[subpath]
mkdir newpath

Obviously there is more to this because you have to get your folder names and you would (based on your stated goal) have to use a loop to step through the recordset of desired folder names.

Having answered your question, I have to say this sounds like it is a run-once-and-never-again set of code, which begs the question, "Why bother to do it in code?" Whatever you write, you will probably never use again since the 2nd time you run it, you would get errors for "Folder already exists." Further, creating a folder DOES presume you have the required permissions to do so.

 

[smtazulislam]

I do not often open an uploaded database. So the names I use might not match yours, but I hope you will see what I suggest.

In general, you can use the VBA command-statement MKDIR to create a folder. You must determine the fully qualified path for the new folder, but from what you describe, you should have all you need.

MkDir statement (VBA)

Office VBA reference topic

learn.microsoft.com

Thank you very much for your suggest.
Step by step we can discuss.

I tried to crate a subfolder under the my Main Path.
But it was created in Main path. Not going under the main path.

EDIT /

Private Sub cmdCreateSubFolder_Click()
Dim sLocation  As String
Dim spath As String
    sLocation = Me.Locations.Value
    spath = sLocation & "\" & Me.SubFolderName.Value
    mkDir (spath)
End Sub

How can create ALL record at Once Time with sub form folder.

 

[Pat Hartman]

You cannot create the folder and move in files using a single instruction. You must create the folder first, then there are commands that will copy/move files from one folder to another.

Please give us an overview of the process so we can provide more targeted suggestions.

 

[smtazulislam]

You cannot create the folder and move in files using a single instruction. You must create the folder first, then there are commands that will copy/move files from one folder to another.

Thank you for your suggest. I tried One Folder Under one Folder is work

LIke : spath = sLocation & "\" & Me.SubFolderName.Value &"\"& here Input something

But One in twice not working.

 

[The_Doc_Man]

If you try to create a folder that is TWO levels down from your DB's location, you must create the intermediate layer first.

I.e. if you have sLocation and you want sLocation\ONE\TWO, you must first create sLocation\ONE - after which you CAN create sLocation\ONE\TWO

 

[smtazulislam]

you CAN create sLocation\ONE\TWO

Here sLocation and ONE as you callled. it was created.
Ex : Main Folder\Master File\ x64
if I manually write (x64) in the vba. but I would like NAME calling from the TABLE.
Also look my TABLE relations. ONE - MANY.

Secondly problem is dont have sense how to call \X32 folder create.

 

[Pat Hartman]

Here is a sample using FSO (File System Object). To use it, you MUST add a reference to Microsoft Scripting Runtime for your version of Access.

It takes a hard coded high level path (you should reference a form field) and then concatenates "\Error" to create a lower level. Then it creates a second lower level by concatenating "\ClientName". You will see commented out code that references a form field. The idea here is that you are creating a separate folder for each client.

The final step copies files from one folder to another. If you instead use fs.MoveFile, the files will be deleted from the source folder.

Public Sub CreateFolders()
    Dim fs               As Scripting.FileSystemObject
    Dim ErrFolderName    As String
    Dim ClientFolderName
    Dim SourceFolderPath As String

''' requires reference to Microsoft Scripting Runtime   !!!!!!!!!!!  open any code module, select tools/References, pick from list



    SourceFolderPath = "C:\Data\UsefulDatabases\Access_TeachingSamples\aaaaaaaaaa_Testing"
    ErrFolderName = SourceFolderPath & "\Errors"
'' create "error" folder  if necessary
    Set fs = CreateObject("Scripting.FileSystemObject")
    If fs.FolderExists(ErrFolderName) Then
    Else
        fs.CreateFolder (ErrFolderName)
    End If
''Every level must be created separately
    ClientFolderName = ErrFolderName & "\" & "clientname"             '''''''''   "\" & Me.ClientID
    If fs.FolderExists(ClientFolderName) Then
    Else
        fs.CreateFolder (ClientFolderName)
    End If
   
'' copy files
    fs.CopyFile SourceFolderPath & "\*.*", ClientFolderName, False
End Sub

 

[smtazulislam]

Thank you very much. Its work.
But not exist with my required.

ClientFolderName = ErrFolderName & "\" & "clientname" ''''''''' "\" & Me.ClientID

The problem is this line. Here I have to create twice folders as you see Parent in the sub-form. But here only create ONE FOLDER.

Maybe it was call like
ParentForm ID = SubForm ID
No Idea how to call in VBA.

I Tried to ID=ID called but also create ONE folder.

'' copy files
fs.CopyFile SourceFolderPath & "\*.*", ClientFolderName, False
End Sub[/CODE]

also I have no Idea why you use the EXTENSION files copy.

 

[Pat Hartman]

But here only create ONE FOLDER.

ABSOLUTELY. Can you create multiple levels of folders with a single command using file manager? No you cannot. THEREFORE, as we have already mentioned. You CANNOT do it here either. Look again at the example and you will see there are TWO folder creation statements. One for each level.

also I have no Idea why you use the EXTENSION files copy.

Aren't you copying all the files in the folder? If not, then feel free to change the wildcards

 

[smtazulislam]

ABSOLUTELY. Can you create multiple levels of folders with a single command using file manager? No you cannot.

Thanks for your times.
You mean, each level One by one Folder create and Sub Folder create by single click each one record.

EDIT :

If its true. Maybe I need to use and insert a unbound textbox for took row numbers from the subfrom.
Am I right ?

 

[Pat Hartman]

If its true. Maybe I need to use and insert a unbound textbox for took row numbers from the subfrom.
Am I right ?

Sorry, there is some disconnect between what you are asking for and what I think you are asking for.

Are you talking about records or folders? Look at the code I posted to see how it creates two folders. It does NOT use a loop so it only creates one two tier path at a time. If you need to process multiple files, each of which could cause a new folder to be created, you need to put the code I suggested into a loop.

EACH level of folder must be created ONE at a time. I have no idea what your folder scheme is so I don't know whether or not each would require separate action. in my case, the high levels are created when the application is created. All the files are downloaded to a single folder from our third party vendor that opens the letters and scans the documents. The name the documents with the CompanyID, the EmployeeID, the scan date, and the docType. If there are multiple documents of the same type for the same employee received on the same day, there is also a suffix. All the parts of the name are separated with underscores to make parsing simple. So a file might be named:
ACME_1234_20230102_MarriageLicense.PDF

The process reads the download folder and for each file parses the name. It starts by looking for "ACME". If the folder doesn't exist, it creates it. Then it looks at "1234" and sees if Customer "ACME" has an employee with ID = "1234", If so, it logs the file name and copies the file from the download folder to the "ACME" folder. If an error occurs at any point, the code looks to see if the "Error" folder exists, if it doesn't, it is created in the download folder and the file is moved there. After all files have been processed. The inner counts are compared to the count of documents in the download folder. If there is no error, the documents in the download folder are deleted.

This particular process doesn't need to create multiple levels of folders but it does create two types. I creates a folder that is hardcoded with the name "Error" under the download folder and it creates multiple folders at the same level, one at a time whenever it encounters a new Company. I have others that do. But the logic is the same, just one level at a time, In all cases, the first time ever that the code runs, lots of folders will be created. Eventually, new folders only need to be created occasionally as a new company or employee is added to the application.

In the process I described above, it might be rational to create a child folder for each employee. My client didn't want to do that but maybe that is what you want. So, the code would continue processing the file name. After validating that the employeeID is valid, the code could then check to see if the folder for that employee existed yet and if not, create it and then move the file to that folder. So, in this case, if the top level path was:

D:\AccessApps\InsuranceAudit\ Then, the code would produce two lower level folders if necessary:
D:\AccessApps\InsuranceAudit\ACME\1234
It might also need to create the Errors folder:
D:\AccessApps\InsuranceAudit\Errors

 

[smtazulislam]

Sorry, there is some disconnect between what you are asking for and what I think you are asking for.

Are you talking about records or folders? Look at the code I posted to see how it creates two folders. It does NOT use a loop so it only creates one two tier path at a time. If you need to process multiple files, each of which could cause a new folder to be created, you need to put the code I suggested into a loop.

Private Sub cmdCreateSubFolder_Click()
    
    Dim strSQL           As String
    Dim db               As DAO.Database
    Dim rs               As DAO.Recordset
    Dim SourceFolderPath As String
    Dim SubFolderPath    As String
    Dim ClientFolderName As String
    Dim i                As Long
    Dim fs               As Scripting.FileSystemObject
    
    Set db = CurrentDb
    'The code should set the current database active
    strSQL = "SELECT qryDistPath.FID, qryDistPath.SubFolderName, qryDistPath.Locations, qryDistPath.FolderName, qryDistPath.FolderPath" & _
             " FROM qryDistPath;"

    Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
    'The first entry should be taken
    
''' requires reference to Microsoft Scripting Runtime   !!!!!!!!!!!  open any code module, select tools/References, pick from list
    'Main Browse Location
    SourceFolderPath = Me.Locations.Value
    SubFolderPath = SourceFolderPath & "\" & Me.SubFolderName.Value

''' create folder '''''''' if necessary
    Set fs = CreateObject("Scripting.FileSystemObject")

    If fs.FolderExists(SubFolderPath) Then
    Else
       fs.CreateFolder (SubFolderPath)
    End If
    
    i = 0
    Do Until rs.EOF
        ClientFolderName = SubFolderPath & "\" & Form!sfrmFolderPathSyncing.Form!FolderName.Value + 1
        'ClientFolderName = SubFolderPath & "\" & Form!sfrmFolderPathSyncing.Form!FID
        'i..MoveNext
    Loop
    
    If fs.FolderExists(ClientFolderName) Then
    Else
        fs.CreateFolder (ClientFolderName)
    End If
   
    rs.Close
    Set rs = Nothing
    
    Set db = Nothing
    
    MsgBox "Done "
    
'' copy files extension file
    'fs.CopyFile SourceFolderPath & "\*.*", ClientFolderName, False
End Sub

EACH level of folder must be created ONE at a time. I have no idea what your folder scheme is so I don't know whether or not each would require separate action.

exactly pointed you. my folder also client first time (Runtime) will do that by admin. Without it db not running.

in my case, the high levels are created when the application is created. All the files are downloaded to a single folder from our third party vendor that opens the letters and scans the documents. The name the documents with the CompanyID, the EmployeeID, the scan date, and the docType. If there are multiple documents of the same type for the same employee received on the same day, there is also a suffix. All the parts of the name are separated with underscores to make parsing simple. So a file might be named:
ACME_1234_20230102_MarriageLicense.PDF

As same you are mentioned there as mine think.

I also separate by each employee parts LOOK my sub-form Folder Items. But why I put in the table. Because my dB have some customize setting that ALTER the name. If client want to change Name it was first set should do. After setup then not possible to rename.

The process reads the download folder and for each file parses the name. It starts by looking for "ACME". If the folder doesn't exist, it creates it. Then it looks at "1234" and sees if Customer "ACME" has an employee with ID = "1234", If so, it logs the file name and copies the file from the download folder to the "ACME" folder. If an error occurs at any point, the code looks to see if the "Error" folder exists, if it doesn't, it is created in the download folder and the file is moved there. After all files have been processed. The inner counts are compared to the count of documents in the download folder. If there is no error, the documents in the download folder are deleted.

Exactly. I also need it. when my resolve the folder creation.

This particular process doesn't need to create multiple levels of folders but it does create two types. I creates a folder that is hardcoded with the name "Error" under the download folder and it creates multiple folders at the same level, one at a time whenever it encounters a new Company. I have others that do. But the logic is the same, just one level at a time, In all cases, the first time ever that the code runs, lots of folders will be created. Eventually, new folders only need to be created occasionally as a new company or employee is added to the application.

In the process I described above, it might be rational to create a child folder for each employee. My client didn't want to do that but maybe that is what you want. So, the code would continue processing the file name. After validating that the employeeID is valid, the code could then check to see if the folder for that employee existed yet and if not, create it and then move the file to that folder. So, in this case, if the top level path was:

D:\AccessApps\InsuranceAudit\ Then, the code would produce two lower level folders if necessary:
D:\AccessApps\InsuranceAudit\ACME\1234
It might also need to create the Errors folder:
D:\AccessApps\InsuranceAudit\Errors

See I do it manually.

I think, and I am clear that you understood my concept.
can you resolve my code.

 

[Pat Hartman]

can you resolve my code.

No. I can't.
1. I don't have the database to test with
2. I don't know what part of your code isn't working. Which folders are being created and which are not? Where are you getting the list of folders to create? In my example, i show TWO hard-coded folder names at different levels. Looks like you have NINE but they are not in the code and they are all on the same level. If you want to control the names of the folders you automatically create without hard coding them and so you can use a loop rather than nine commands, then create a table to hold the names. Open a recordset to read the table and for each row in the table, create a folder.

And finally, NEVER use a button for a procedure like this. If you always want the child folders to be created when the user creates a folder, then just create the folders. Do not require that the user take an additional step which he might forget. What happens later if the user forgot to click the create folders button?

You say I understand your problem but your questions indicate that I don't.

 

[smtazulislam]

NEVER use a button for a procedure like this. If you always want the child folders to be created when the user creates a folder, then just create the folders. Do not require that the user take an additional step which he might forget. What happens later if the user forgot to click the create folders button?

Sorry for late reply. No sir ! Not like that happens. Because, its work on first time when user like to install the app. App will look up pc main directory path then other will do automatic. That the reason I need a button to work it.

No. I can't.
1. I don't have the database to test with
2. I don't know what part of your code isn't working. Which folders are being created and which are not? Where are you getting the list of folders to create? In my example, i show TWO hard-coded folder names at different levels. Looks like you have NINE but they are not in the code and they are all on the same level. If you want to control the names of the folders you automatically create without hard coding them and so you can use a loop rather than nine commands, then create a table to hold the names. Open a recordset to read the table and for each row in the table, create a folder.

Okay. Thank you for your help. I wait for some can do some code for me.

 

[smtazulislam]

Why does my LOOP statement's immediate window display all records? but here create only one record.

Private Sub cmdCreateSubFolder_Click()
Dim dbs                 As dao.Database
Dim rst                 As dao.Recordset
Dim rstData             As Variant
Dim SourceFolderPath    As String
Dim SubFolderPath       As String
Dim ClientFolderName    As String
Dim fs                  As New FileSystemObject

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblFolderPathSyncing")
    
    Do Until rst.EOF
        rstData = rst!FolderName
        rst.MoveNext
    Loop
    rst.Close
    
'Main Browser Location
    SourceFolderPath = Me.Locations.Value
    SubFolderPath = SourceFolderPath & "\" & Me.SubFolderName.Value
    ClientFolderName = SubFolderPath & "\" & rstData
    
' create sub-folder under the main folder
    Set fs = CreateObject("Scripting.FileSystemObject")
    
    If fs.FolderExists(SubFolderPath) Then
    Else
        fs.CreateFolder (SubFolderPath)
    End If
    
' create current folder under the subfolder
    If fs.FolderExists(ClientFolderName) Then
    Else
        fs.CreateFolder (ClientFolderName)
    End If
    MsgBox "Done "
End Sub

 

[Pat Hartman]

Your loop is doing nothing except overlaying the value in rstData. At the end of the loop, it contains the value of the last record it read.
If you want to create a folder for every value in the table, you need to put the rest of the code INSIDE the loop.

 

[Pat Hartman]

I see that you marked my last post with a like. Does that mean "thank you for nothing" Or does it mean "that solved the problem" No one who finds this thread later will be able to tell.

 

[smtazulislam]

Your loop is doing nothing except overlaying the value in rstData. At the end of the loop, it contains the value of the last record it read.

Yes, it create last record only. I test single module, it was giving all record .. see screenshot

If you want to create a folder for every value in the table, you need to put the rest of the code INSIDE the loop.

No idea ! how I call code inside the loop.

 

[smtazulislam]

I see that you marked my last post with a like. Does that mean "thank you for nothing" Or does it mean "that solved the problem" No one who finds this thread later will be able to tell.

It means thanks for you are again in my thread and expense much time with me.