Make folders from query

[BBBryan]

Hi,
Can some one help me or direct me to an example.
I am trying to make some folders from a Query list to my C drive.
I found this code for making folders.
I have a button on a form with this code. It works with making a folder but only gives me the first item in the query out of many rows. It is missing the rest of the items.
Also I would like it to be able to make Sub Folders too from this query.
Any help would be appreciated.....

Here is the code I found.
Private Sub MakePreservationTagFolder_DblClick(Cancel As Integer)
Dim strFolder As String

strFolder = "C:\Users\bryan\Documents\" & "Tag - " & DLookup("[Tag]", "Tags Qy")

Dim fso
Set fso = CreateObject("Scripting.FileSystemObject")

'Check whether the folder exists
If Not fso.FolderExists(strFolder) Then
FileSystem.MkDir (strFolder)
MsgBox "Folder Created"

Else
MsgBox "Folder Already Created"

End If
End Sub



Thanks BBryan

 

[namliam]

Private Sub MakePreservationTagFolder_DblClick(Cancel As Integer)
    Dim strFolder As String
    Dim rs as dao.recordset
    set rs = currentdb.openrecordset("tags qy") 
    do while not rs.eof

        strFolder = "C:\Users\bryan\Documents\" & rs!Tag

        Dim fso
        Set fso = CreateObject("Scripting.FileSystemObject")

        'Check whether the folder exists
        If Not fso.FolderExists(strFolder) Then
            FileSystem.MkDir (strFolder)
            MsgBox "Folder Created"
        Else
            MsgBox "Folder Already Created"
        End If
        rs.movenext
     loop 
     rs.close
     set rs = nothing
End Sub

Something like that

General notes:
1) Please use code tags when posting code (see my signature for details)
2) Naming convention, using spaces in object names (query name in this case) is a bad habit you need to break ASAP. Atleast you are using QY to identify a query, however "common practice" is to prefix your objects and use
tbl
qry
frm
rpt
mcr
mdl

for the different types of objects.

 

[BBBryan]

Thanks NamLiam for your help,
I put this code in I think it is stuck in a loop.
The "folder created" msgbox comes up but you have to click it until the entire query list is done. I had to click it 133 times....

Also If I want to add a sub folder using the next column in my query [Month]
I tried to add a sub folder by adding this but doesn't work
strFolder = "C:\Users\bryan\Documents\" & rs!Tag\" & rs!Month
It just highlights it in yellow as an error
Not sure how to do this.

And as for the using the Qy and everything else I will slowly have to change them I have using that all along.

Thanks BBryan

 

[namliam]

It isnt stuck in a loop, it tries to check every record in your query.... if the query has 10.000 records, it will show the popup 10.000 times
So if you dont want to see the message every time, simply remove it or make a counter to report on the end, instead of on every record... You wanted every record right?

strFolder = "C:\Users\bryan\Documents\" & rs!Tag\" & rs!Month

you are missing a quote and an ampersant

strFolder = "C:\Users\bryan\Documents\" & rs!Tag [COLOR="Red"]& "[/COLOR]\" & rs!Month

 

[BBBryan]

Thanks
I took out the folder created and now works good they all populated....
I tried to put the code in for the sub folders and I get an error
path not found and the FileSystem.MkDir (strFolder) is yellow highlighted
Thanks Bryan

 

[namliam]

Not sure if folders can be stacked to be created or not?

I.e. if the folder 2014 dont exist, you cant create c:\2014\01
where MkDir will I think try to create only the 01 folder instead of the entire tree.

So you would have to do it folder by folder, step by step

 

[BBBryan]

Ok Thanks
The big one was to create the folders which you have helped me do.......
I don't know enough too figure the sub folders out.....

One other thing I looked at using code tags example. how do you put the extras on the bar. I don't have the #, indents and a bunch of others that were in the example.
I only have 8 items - Remove text formatting, Bold, I ,U ,text colour ,insert link ,insert image and wrap...


Thanks BBryan

 

[namliam]

Sub folders could be as simple as

strFolder = "C:\Users\bryan\Documents\" & rs!Tag 
'check and create folder
strFolder = "C:\Users\bryan\Documents\" & rs!Tag & "\" & rs!Month
'check and create folder
strFolder = "C:\Users\bryan\Documents\" & rs!Tag & "\" & rs!Month & "\" rs!Day
'check and create folder

The "extra's" as you put it are in the "advanced" version of the post, you get there by pressing the Go Advanced or Post reply button. Also the advanced version is there if you make a new post.

 

[BBBryan]

thanks
advanced is better!

I put this in and I get an compile error Expected: end of statement on the last rs!

strFolder = "C:\Users\bryan\Documents\" & rs!Tag & "\" & rs!Description & "\" rs!Sub System

Also in my query I added a couple of the textboxes together to make a better title on the folder
And now that gives me an error Path not found.. It looks like it has something to do with addition i made in the query.
In Query I have i made a new one

Tagg: [Tag] & " [" & [Description] & "]--- [" & [SystemDescription] & " / " & [Sub System] & "]"

And then changed this too

strFolder = "C:\Users\bryan\Documents\" & rs!Tagg

Thanks BBryan

 

[BBBryan]

Sorry the code tag didn't work

thanks
advanced is better!

I put this in and I get an compile error Expected: end of statement on the last rs!

strFolder = "C:\Users\bryan\Documents\" & rs!Tag & "\" & rs!Description & "\" rs!Sub System

Also in my query I added a couple of the textboxes together to make a better title on the folder
And now that gives me an error Path not found.. It looks like it has something to do with addition i made in the query.
In Query I have i made a new one

Tagg: [Tag] & " [" & [Description] & "]--- [" & [SystemDescription] & " / " & [Sub System] & "]"

And then changed this too

strFolder = "C:\Users\bryan\Documents\" & rs!Tagg

Thanks BBryan

 

[namliam]

Dont use spaces in column names....
and/or same goes for VBA as for queries, wrap those special names in []
[Sub System]

Make sure the path you are trying to make is a valid path, i.e. dont have \ / or other reserverd chars in the path.
Also remember I dont think you can create a path you need to check it one (sub) folder at a time

 

[BBBryan]

I see I have a couple of bad habit....

Thanks for your help
It is appreciated!

BBryan

 

[namliam]

Not really that important a lot of the bad habits are actually learned online and/or my Microsoft samples....

But you do find things hurt you over the course of time if you stick with them, better not use them or start learning "better" ways of doing them.
Hope I am not being too harsch on you, it is only meant trying to help you, not to insult or embares or anything.

 

[BBBryan]

Thanks I am always willing to learn.......
Thanks BBryan