Solved Button in a form that creates a folder in the PC from input taken from the form

[martincabrera33]

Hello,
I am new to access, but at work I am trying to speed up a process.
We have an Access DB that has a few forms, in one of them we create new records and for each record we later need to create a folder to contain some documents.
I want to add a button to that form that will create the folder.
The folder has a set input such as "C:\Users\'UserName'\Desktop\etc..." and then some variables that depend on the selected record of the form, for instance the form has a business sector, a date, and a number. I need to create something like:
"C:\Users\'UserName?\Desktop\Business Sector\date(Year)\Number...

I have found many resources but can't manage to understand if I first need to do a query or just with the button and a macro this will work.
On the macro side, I can't get it to look for the info I need, for instance I was trying the following:

Private Sub Crear_Abrir_Carpeta_Click()


MkDir "C:\Users\'UserName'\Desktop\"
Err.Clear
MkDir "C:\Users\'UserName'\Desktop\" & "!Forms!FormName!FieldName"
Err.Clear
MkDir strFolderPath
...

But it is creating me a folder with the actual name of C:\Users\'UserName'\Desktop\!Forms!FormName!FieldName

Not sure how on a Macro to pull the name of the field I need from the record that is currently selected.

Thanks

 

[arnelgp]

Private Sub Crear_Abrir_Carpeta_Click()
    ' create folder based on:
    ' business sector, a date, and a number
    '
    ' all fields must not be blank
    Dim strFolder As String
    If IsNull(Me!businessSectorTextbox) Then
        MsgBox "Please type the business sector", vbInformation
        Me!businessSectorTextbox.SetFocus
        Exit Sub
    End If
    If IsNull(Me!DateTextbox) Then
        MsgBox "Please type the date", vbInformation
        Me!DateTextbox.SetFocus
        Exit Sub
    End If
    If IsNull(Me!aNumberTextbox) Then
        MsgBox "Please type a Number", vbInformation
        Me!aNumberTextbox.SetFocus
        Exit Sub
    End If
    ' all required fields are ok
    strFolder = Environ("UserProfile") & "\Desktop\" & _
        Me!businessSectorTextbox & "\" & _
        format(Me!DateTextbox, "yyyy_mmm_dd") & "\" & _
        Me!aNumberTextbox
    ' create the folder
    Call ForceMKDir(strFolder)
End Sub

Public Function ForceMKDir(ByVal strPath As String)
    Dim strNewPath As String
    Dim var As Variant
    Dim i As Integer
    '* remove trailing "\"
    If Right(strPath, 1) = "\" Then
        strPath = Left(strPath, Len(strPath) - 1)
    End If
    var = Split(strPath, "\")
    On Error Resume Next
    For i = 0 To UBound(var)
        strNewPath = strNewPath & var(i)
        VBA.MkDir strNewPath
        strNewPath = strNewPath & "\"
    Next
End Function

 

[martincabrera33]

Private Sub Crear_Abrir_Carpeta_Click()
    ' create folder based on:
    ' business sector, a date, and a number
    '
    ' all fields must not be blank
    Dim strFolder As String
    If IsNull(Me!businessSectorTextbox) Then
        MsgBox "Please type the business sector", vbInformation
        Me!businessSectorTextbox.SetFocus
        Exit Sub
    End If
    If IsNull(Me!DateTextbox) Then
        MsgBox "Please type the date", vbInformation
        Me!DateTextbox.SetFocus
        Exit Sub
    End If
    If IsNull(Me!aNumberTextbox) Then
        MsgBox "Please type a Number", vbInformation
        Me!aNumberTextbox.SetFocus
        Exit Sub
    End If
    ' all required fields are ok
    strFolder = Environ("UserProfile") & "\Desktop\" & _
        Me!businessSectorTextbox & "\" & _
        format(Me!DateTextbox, "yyyy_mmm_dd") & "\" & _
        Me!aNumberTextbox
    ' create the folder
    Call ForceMKDir(strFolder)
End Sub

Public Function ForceMKDir(ByVal strPath As String)
    Dim strNewPath As String
    Dim var As Variant
    Dim i As Integer
    '* remove trailing "\"
    If Right(strPath, 1) = "\" Then
        strPath = Left(strPath, Len(strPath) - 1)
    End If
    var = Split(strPath, "\")
    On Error Resume Next
    For i = 0 To UBound(var)
        strNewPath = strNewPath & var(i)
        VBA.MkDir strNewPath
        strNewPath = strNewPath & "\"
    Next
End Function

Hello Arnelgp,
I will try this right now. Wow, seems that this solves the whole issue and it is more complex and complete than I expected, I find that I'll have a long way to learn Access.
Thank you, I'll let you know as soon as I make it work with your code!

 

[arnelgp]

yes, you can try. you can also try to paste the Public Function to a Module so it can be called from anywhere your db.
all the textbox name on the code are but samples, you need to supply the correct name.

 

[martincabrera33]

can try. you can also try to paste the Public Fun

Yes, I am having a bit of an issue with the names, they are with spaces in between.

For instance the Date field is called:
Arrival Date of PO to OM

it gives me an error Compile Error: Expected: list separator or )

How do I get around that?
Thank you very much

 

[martincabrera33]

Yes, I am having a bit of an issue with the names, they are with spaces in between.

For instance the Date field is called:
Arrival Date of PO to OM

it gives me an error Compile Error: Expected: list separator or )

How do I get around that?
Thank you very much

I was able to solve it by using:

Me.Arrival_Date_of_PO_to_OM.Value

Thank you!!!

 

[arnelgp]

or enclosed it in Square parenthesis:

Me![Arrival Date of PO to OM]

or better put Different textbox name than the field name.
on design view click on the textbox
on it Property Sheet->Other->Name: txtArrivalDate

then you use:

Me!txtArrivalDate

 

[martincabrera33]

or enclosed it in Square parenthesis:

Me![Arrival Date of PO to OM]

or better put Different textbox name than the field name.
on design view click on the textbox
on it Property Sheet->Other->Name: txtArrivalDate

then you use:

Me!txtArrivalDate

Ok, I will use the square brackets it is a better option.
Regarding changing the name is not a good option now because this DB was developed a long time ago and there are many references to those fields thought the code.

Thank you very much!

 

[arnelgp]

on the future forms, it is best to use different control name than field name.
example, you have a fieldname [cost] and you add a textbox to the form and did not change its name (textbox name is cost, also).
when you do some formula, like =sum([cost]), access will get confused and display #Name.
then you will need to change the textbox to something, like txtCost.
and change the formula to =sum([txtCost])