Create a Sub-Folder based on the parent folder and record id (1 Viewer)

Danick · May 6, 2016

I would like Access 2003 to create a folder in a common directory called “A_References” with the folder name based on 2 fields of the current record the user is working on. The two fields are: Categories and Id_Action

When the user clicks a button, I would like Access to create a folder called <Id_Action> that will go into the C:\A_References\<Categories> folder.

But the "Categories" folder will change depending on what record the user is on.

Is there a sample VBA that can do something like this?

Thanks

pbaldy · May 7, 2016

Well, the basic tools are the Dir() function to test whether the folder exists, and MkDir() to make a new one. You can concatenate your fixed path with your variables for use in both:

strPath = "C:\A_References\" & Me.txtCategory

where txtCategory is the name of the textbox containing the category.

Edit: oh, and the Len() function to test the result of the Dir() function.

yupstrips01 · May 9, 2016

I then select the 'Folder' record type and click OK button to create sub container 'my child folder'. ... But it's showing the 'my child folder' as same level as 'my parent folder' ... I've just figured out how to create a subfolder based on a container.

Danick · May 9, 2016

pbaldy said:
Well, the basic tools are the Dir() function to test whether the folder exists, and MkDir() to make a new one. You can concatenate your fixed path with your variables for use in both:

strPath = "C:\A_References\" & Me.txtCategory

where txtCategory is the name of the textbox containing the category.

Edit: oh, and the Len() function to test the result of the Dir() function.

OK I've got part of it working up to the first level. But can't figure out how to make the subfolder.

Code:

Private Sub btnCreateFolderIssue_Click()
Dim strPath As String

'This is what I want but not working
    'strPath = "C:\A_References\" & Me.Categories & "\" & "Issue" & Me.IdAction & " - " & Me.Subject 

'This works but only for the first level, how can I create the subfolder with the IdAction
     strPath = "C:\A_References\" & Me.Categories
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
        

    End If

    Shell "EXPLORER.EXE " & strPath, vbNormalFocus
End Sub

Danick · May 9, 2016

OK - never mind.
After a little more testing, I figured it out. This is working on Access 2003

Code:

Private Sub btnCreateFolderIssue_Click()
Dim strPath As String

  
     strPath = "C:\A_References\" & Me.Categories
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
    
    
     strPath = "C:\A_References\" & Me.Categories & "\" & "Issue" & " " & Me.Id_Action & " - " & Me.Subject
    
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
    MkDir strPath
    End If

    Shell "EXPLORER.EXE " & strPath, vbNormalFocus
End Sub

pbaldy · May 9, 2016

Glad you got it working.

Danick · May 10, 2016

Sorry one more thing.
Now that creating and opening the folder is working, the next thing to modify the GetOpenFile code by Ken Getz found here:
http://access.mvps.org/access/api/api0001.htm

This works great in that the command button opens a dialog for the user to pick the file to open. I used the guideline in the first part of the code to call the dialog.

However, this dialog has the default window starting in the My Documents folder. Is there a way to modify this guideline to use the StrPath (if it exists) or go back to using the default window if it doesn't?

pbaldy · May 10, 2016

Note the optional parameter varDirectory. You can pass your path to his function.

Danick · May 10, 2016

pbaldy said:
Note the optional parameter varDirectory. You can pass your path to his function.

Maybe getting a little over my head. This is what I've got with the varDirectory, but I get a compile error. Method or data member not found. It highlights the Me.Categories. And if get rid of the "& Me. Categories" then it at least opens to the A_References folder. But I can't get it open to the Categories folder..

Code:

Dim strFilter As String
Dim strInputFileName As String
Dim FileNameFromPath As String

'This is to try and make the default directory based on category and subject
Dim strPath As String
Dim varDirectory  As String

strPath = "C:\A_References\" & Me.Categories
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
strPath

varDirectory = strPath

End if

'Delete above if not working

strFilter = ahtAddFilterItem(strFilter, "All Files (*.*)", "*.*")
strInputFileName = ahtCommonFileOpenSave(InitialDir:=strPath, _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please select an input file...", _
                Flags:=ahtOFN_HIDEREADONLY)
                

FileNameFromPath = right(strInputFileName, Len(strInputFileName) - InStrRev(strInputFileName, "\"))

If Nz(strInputFileName, "") = "" Then

'Cancel Pressed so handle it accordingly
'Me.[LongName] = strInputFileName
Exit Sub

Else
Me.InputfileName = strInputFileName
Me.LongName = FileNameFromPath

End If

End Sub

Danick · May 10, 2016

Finally got it working. I was missing the Parent form. :banghead:

Code:

strPath = "C:\A_References\" & Me.Parent!Categories

speakers_86 · May 10, 2016

Bear in mind using APIs can cause issues if you try and deploy on Access 64 bit. Usually not an issue, but it was for me recently. So now, I try and avoid APIs when possible or code for both 32 and 64 bits.

Danick · May 10, 2016

speakers_86 said:
Bear in mind using APIs can cause issues if you try and deploy on Access 64 bit. Usually not an issue, but it was for me recently. So now, I try and avoid APIs when possible or code for both 32 and 64 bits.

Thanks for letting me know. Unfortunately I'm not really a programmer, so I'm just trying to keep an old Database updated with new features as the needs come up. Besides, my company just updated all the computers from XP to Win 7 just a few months ago. So if history can predict the future, I'm thinking I'll be good with Access 2003 for another 10 years!!!

Cheers...

pbaldy · May 10, 2016

Danick said:
Finally got it working. I was missing the Parent form. :banghead:

Code:

strPath = "C:\A_References\" & Me.Parent!Categories

Glad you got it working. Been jacked up today.

Danick · May 12, 2016

I thought I was done, but need a little help with VBA in trying to open an existing folder if it exists and use a new naming standard if it doesn't. This is because I don't want to go into the tables to try an re-name everything.

What I'm trying to do is, check if the first folder exists and open it if it does, or create a new folder if it doesn't. Seems simple, but this code just keeps creating a new folder if the original name exists. Here's my code. Anything obvious I'm missing?

Code:

Private Sub btnCreateFolderIssue_Click()
Dim strPath As String

  
   
     strPath = Application.CurrentProject.Path & "\A_References\" & Me.Categories
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
    
    ' This works and adds the Subject Title to the Folder name.  But has caused windows validation issues when subject contains punctuation.  And do not want to use validation code for Subject


    strPath = Application.CurrentProject.Path & "\A_References\" & Me.Categories & "\" & "Issue" & " "  & Me.Id_Action & " - " & Me.Subject

   If Len(Dir(strPath, vbDirectory)) = 0 Then
   
[COLOR="Red"]' Folder exists, so just open it otherwise, create a folder with new naming standard (this part is not working) [/COLOR]

    Else
    strPath = Application.CurrentProject.Path & "\A_References\" & Me.Categories & "\" & "Issue" & " " & Me.Id_Action
    If Len(Dir(strPath, vbDirectory)) = 0 Then
    MkDir strPath
     
    End If
    End If
Shell "EXPLORER.EXE " & strPath, vbNormalFocus
    
End Sub

Danick · May 12, 2016

Never mind - I figured it out. Seems so simple if you walk away for a bit...

Code:

Private Sub btnCreateFolderIssue_Click()
Dim strPath As String

     strPath = Application.CurrentProject.Path & "\A_References\" & Me.Categories
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
        MkDir strPath
    End If
    
    strPath = Application.CurrentProject.Path & "\A_References\" & Me.Categories & "\" & "Issue" & " " & Me.Id_Action & " - " & Me.Subject
    If Len(Dir(strPath, vbDirectory)) <> 0 Then
    Shell "EXPLORER.EXE " & strPath, vbNormalFocus
    Else
   
    strPath = Application.CurrentProject.Path & "\A_References\" & Me.Categories & "\" & "Issue" & " " & Me.Id_Action
    
    
    If Len(Dir(strPath, vbDirectory)) = 0 Then
    MkDir strPath
    End If

    Shell "EXPLORER.EXE " & strPath, vbNormalFocus
    End If
     
End Sub

speakers_86 · May 12, 2016

So you're saying that the else part is running when it should not be? If so, there is only one if statement, so it must be returning false when it should be returning true. Debug the strPath part right before that if statement and try to navigate to it in Windows. It probably wont work which means the value in strPath is not correct. Find and correct that error and see if it works.

pbaldy · May 12, 2016

Danick said:
Never mind - I figured it out. Seems so simple if you walk away for a bit...

Glad you got it sorted.

Create a Sub-Folder based on the parent folder and record id (1 Viewer)

Danick

Registered User.

pbaldy

Wino Moderator

yupstrips01

Registered User.

Danick

Registered User.

Danick

Registered User.

pbaldy

Wino Moderator

Danick

Registered User.

pbaldy

Wino Moderator

Danick

Registered User.

Danick

Registered User.

speakers_86

Registered User.

Danick

Registered User.

pbaldy

Wino Moderator

Danick

Registered User.

Danick

Registered User.

speakers_86

Registered User.

pbaldy

Wino Moderator

Similar threads

Users who are viewing this thread