Solved Create Folders from a Query list

[Lochwood]

I am looking to create a list of folders based on the Assignee field in a query. so if i have 30 records, i would like 30 folders created with the value of the Assignee field in the query. I can do this for a single record but not multiple record but not multiple. Can you Help?

 

[Minty]

Can you show a brief example of the desired folder structure and your existing data?

Something like

Assignee
John Doe
Harry Truman

Folders
\\Server\Share\AssigneeData\JohnDoe
\\Server\Share\AssigneeData\HarryTruman

You will almost certainly need a loop, based of a recordset.

 

[Gasman]

Use a recordset with the query and loop through all the records.

 

[MajP]

Public Sub CreateFolders()
  Const QueryName = "yourQuery" ' your name here
  Const FieldName = "yourField"  'your field name here
  Const Path = "C:\"
  Dim rs As DAO.Recordset
  Dim FolderName As String
 
  Set rs = CurrentDb.OpenRecordset(QueryName)
 
  Do While Not rs.EOF
    FolderName = Path & rs.Fields(FieldName)
    MakeFolder FolderName
    rs.MoveNext
  Loop
 
 End Sub


Public Sub MakeFolder(FolderName As String)
  'code Here to make folder
  Debug.Print FolderName
End Sub

 

[Isaac]

Use the advice given and insert a MkDir or use (my preference) Scripting.Filesystemobject's CreateFolder

 

[Lochwood]

Can you show a brief example of the desired folder structure and your existing data?

Something like

Assignee
John Doe
Harry Truman

Folders
\\Server\Share\AssigneeData\JohnDoe
\\Server\Share\AssigneeData\HarryTruman

You will almost certainly need a loop, based of a recordset.

Button to run the code.. The mainform fields create the Mainfolder and subfolder. I need to then create assignee folders in the subfolder based on assignee values ion the subform/query.. E:G
Mainfolder
subfolder
John Doe
Harry Trueman
the Here is the code i currently use to make the mainfolder and subfolder

Const Info = "\sharepoint\folder\"

Dim Folder As String
Dim Folder2 As String

Dim fso As Object


This creates the main folder based on the mainform fields
Folder = Environ("UserProfile") & Info & Me!Location
Folder2 = Folder & "\" & "Job No" & " " & [Job Number] & " " & "-" & " " & Me!Job


' Create FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
' Check whether folder exists
If fso.FolderExists(Folder) = False Then
' If not, create it
fso.CreateFolder Folder
End If

If fso.FolderExists(Folder2) = False Then
fso.CreateFolder (Folder2)
End If

' Open it
'shell "explorer.exe " & Folder, vbNormalFocus

 

[Minty]

Okay so you need to get the related subform data to create the subfolders in the JobNo folder, correct?
The subforms current recordset will return that information. So

Set rs = Me.YourSubformControlName.Form.Recordsetclone

Should put that into a recordset for you to loop around and create the folders. (Aircode but you get the idea)

 

[Lochwood]

Okay so you need to get the related subform data to create the subfolders in the JobNo folder, correct?
The subforms current recordset will return that information. So

Set rs = Me.YourSubformControlName.Form.Recordsetclone

Should put that into a recordset for you to loop around and create the folders. (Aircode but you get the idea)

Worked a treat thanks

 

[Minty]

Good news. Good luck with the rest of your project.