Setting up a folder structure via Access

Question

Hi, 

How do you go about - if you even can do this - set up a button so that you when you click upon it (in my database it would be to set up a new case) that it can automatically create a folder on my documents area?

I've been told this can be done?

Any help appreciated.

Answer 1

You can call the MkDir command to create a folder.  If you want this to be in your Documents folder on your local drive this will have a path along the following lines:

C:\Users\Floyd\Documents

where Floyd is your user name.  Let's assume you are in a form bound to a Cases table with a column of text data type named CaseNumber, then to create a folder with the value of CaseNumber as its name you'd execute code along the following lines:

    On Error GoTo Err_Handler

    Const FOLDER_EXISTS = 75

    Const MY_DOCUMENTS_PATH = "C:\Users\Floyd\Documents"

    If Not IsNull(Me.CaseNumber) Then

        MkDir MY_DOCUMENTS_PATH & "" & Me.CaseNumber

        ' further code, e.g. to store a document in the folder would go here

    End If

Exit_Here:

    Exit Sub

Err_Handler:

    Select Case Err.Number

        Case FOLDER_EXISTS

        Resume Next

        Case Else

        MsgBox Err.Description

        Resume Exit_Here

    End Select

That's a simple example.  It could be more complex of course.  The following code is from one of my online demos and is used to store PDF files of invoices in folders, each of which has the customer's name as its name:

Private Sub cmdCreateInvoices_MultiFiles_Click()

    On Error GoTo Err_Handler

    Const FOLDER_EXISTS = 75

    Const MESSAGE_TEXT_1 = "No folder set for storing PDF files."

    Const MESSAGE_TEXT_2 = "No invoice(s) selected."

    Dim strFullPath As String

    Dim varFolder As Variant

    Dim varFolder_1 As Variant

    Dim varItem As Variant

    ' build path to save PDF file

    varFolder = DLookup("Folderpath", "pdfFolder")

    If IsNull(varFolder) Then

        MsgBox MESSAGE_TEXT_1, vbExclamation, "Invalid Operation"

    Else

        With Me.lstInvoices

            If .ItemsSelected.Count > 0 Then

                For Each varItem In .ItemsSelected

                    Me.txtInvoiceList = .ItemData(varItem)

                    ' create folder if does not exist

                    varFolder_1 = varFolder & "" & .Column(1, varItem)

                    MkDir varFolder

                    strFullPath = varFolder_1 & "" & .Column(1, varItem) & " " & .ItemData(varItem) & ".pdf"

                    DoCmd.OutputTo acOutputReport, "rptInvoiceMultiple", acFormatPDF, strFullPath, True

                Next varItem

            Else

                MsgBox MESSAGE_TEXT_2, vbExclamation, "Invalid Operation"

            End If

        End With

    End If

Exit_Here:

    Exit Sub

Err_Handler:

    Select Case Err.Number

        Case FOLDER_EXISTS

        Resume Next

        Case Else

        MsgBox Err.Description

        Resume Exit_Here

    End Select

End Sub

You'll find the file as InvoicePDF.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.